If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
update query
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 |
#2
|
|||
|
|||
update query
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. How might I update a table with the contents of a crosstab where there are 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. |
#3
|
|||
|
|||
update query
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 |
Thread Tools | |
Display Modes | |
|
|