By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,480 Members | 2,192 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,480 IT Pros & Developers. It's quick & easy.

How to update a destination table with data from a temp table?

P: 1
Okay guys,
I'm struggling with understanding how to properly update a table with values from a temp table I created from an APPEND query which provides totals information off of an applicaion database.

I'm using Access 2007 and Windows XP Pro.

I have the append queries working and they ouput data elements witb DATE being common.

So, it looks like this:

DATE Data1 Data2 Data3 Etc......
11/01/08 100
11/02/08 75
11/03/08 125
11/04/08 76
11/05/08 120
11/06/08 221
11/07/08 123
11/01/08 3
11/02/08 7
11/03/08 4
11/04/08 6
11/05/08 12
11/06/08 21
11/07/08 13
11/01/08 10
11/02/08 7
11/03/08 15
11/04/08 7
11/05/08 10
11/06/08 21
11/07/08 13

Now what I'm trying to do, is update a SUMMARY from the TEMP table with the totals for each day across.. Like this:

DATE Data1 Data2 Data3
11/01/08 100 3 10
11/02/08 75 7 7

I'm just conceptually not understanding how to get just the date and data element I want. I created a join on the DATE from both tables.

Thanks,
Jim
Nov 14 '08 #1
Share this Question
Share on Google+
1 Reply


nico5038
Expert 2.5K+
P: 3,072
To get this data I would add an autonumber to the temp table.
Next use the DCOUNT() function to count the number of values Less/Equal to the autonumber.
This would give:
11/01/08 1 100
11/01/08 2 3
11/01/08 3 10

Now with this additional column you can create a crosstable query using the DCount column as header.

Getting the idea ?

Nic;o)
Nov 16 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.