A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

update query



 
 
Thread Tools Display Modes
  #1  
Old December 8th, 2009, 08:07 PM posted to microsoft.public.access.queries
Ian Burton[_2_]
external usenet poster
 
Posts: 4
Default 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  
Old December 8th, 2009, 09:06 PM posted to microsoft.public.access.queries
Dale Fye
external usenet poster
 
Posts: 2,651
Default 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  
Old December 8th, 2009, 11:19 PM posted to microsoft.public.access.queries
Ian Burton[_2_]
external usenet poster
 
Posts: 4
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 07:24 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.