update query

[Ian Burton]

Hi,
I thnk I am missing something obvious, but I an stuck, and seem to need help
getting some fields in a table updated.
Table 1: R_BSA_M
This has fields for JanHires, JanRevenue, FebHires, FebRevenue etc for 12
months, and a Year Field YearIM.
I have a crosstab query that contains some aggregate data whch I need to get
into this table.
Each record is distinctive for four possible field values that also exist in
the table: category, salesRep, referrertype, bsacategory, and so I need to
update the existing record with the correct record from the crosstab query.
Of course, if I create a query with the table above, and also the crosstab,
and create relationships between the four criteria, then the query is not
updateable, and so I cannot simply update the values in the query.

How might I update a table with the contents of a crosstab where there are
many criteria like this?
Any clues or ideas would be gratefully received.
thanks,
Ian Burton

 

[Dale Fye]

First and foremost.

Reconstruct table R_BSA_M. Any time you have a field name that contains
"data", your structure is WRONG. In your case, the "data" is the name of the
month. Your data structure for this table should be something like:

ID- Autonumber
HireYear-numeric (you really should not use "YEAR" as a field name as it is
a reserved word, so you should preceed or follow year with a descriptive word
that identifies what the year or the table is for)
HireMonth-numeric
Hires-numeric
Revenue-numeric

If you need to get this into columns like JanHire, JanRevenue, FebHire, ...
you can do so via a crosstab query.
many criteria like this?

I'm not sure why you would want to update a table with values from a
crosstab that does an aggregation, since you can do the aggregation any time
you want via a query. Furthermore, in order to keep this "table" updated,
you will have to run the crosstab and subsequent query each time a value is
changed, added or deleted, to make keep the "table" up-to-date. Seems like a
major waste of time.

 

[Ian Burton]

Dale, thanks for your input. I agree that it is not normalised, but the
reason it is in this structure is because it is a report table. I.e. it is
a table that is used as the basis of a report, and indeed it is recalculated
every time the report is run.
It is a form because this offers greater flexibility in terms of filtering
and ordering the data, and because the data for the report comes from three
quite different sources, whch can not be combined into one single report
query. There are some other reasons as well.
In this particular case, can you help me try to get an update query working
for this situation, or give me some other tips?
regards,
Ian