Populate table with count values from queries

[dsc2bjn]

I have created a database which contains individual records. I have created
3 queries to get a record count for three populated fields within the table
(1. Total record count for each Office; 2. Number of records which have been
completed; 3) number of records remaining open).

I wish to store those count values in a new table in order to keep a history
of progress.

I created an update query and told it to update the coresponding values in
the "history" table with the value from each query; however, I get a message
saying the data must come from an editable query.

Any suggestions would be greatly appreciated.

 

[John W. Vinson]

I created an update query and told it to update the coresponding values in
the "history" table with the value from each query; however, I get a message
saying the data must come from an editable query.

If you're keeping a history, would it not be better to add a new record for
each history point? An Append query need not be based on an updateable query.

John W. Vinson [MVP]

 

[dsc2bjn]

I have tried to respond three times to this email and MS keeps prompting me
for my account information even though they show me as signed in. Wil

 

[John W. Vinson]

I have tried to respond three times to this email and MS keeps prompting me
for my account information even though they show me as signed in. Wil

It's not email - it's a public newsgroup.
You don't need to use the webpage, and you don't need an account.

Point your newsreader (Outlook Express comes installed with Windows Explorer)
to msnews.microsoft.com and subscribe to microsoft.public.access.

John W. Vinson [MVP]

 

[dsc2bjn]

I have been going through the browswer for years without problems...of course
I was using my computer at work...so who knows...works from home...not the
real issue.

Since I have three separate queries (Total, Open, Closed) and each provides
a count for the same set of data. I need to have all the counts in the
history table associated with the same Office, FY, and for the same Reporting
period.

Office1, 2006, 1stQTR, 28, 15, 13

Wouldn't an append query would try to save them in different records?

Office1, 2006, 1stQTR, 28
Office1, 2006, 1stQTR, 15
Office1, 2006, 1stQTR, 13

 

[John W. Vinson]

Since I have three separate queries (Total, Open, Closed) and each provides
a count for the same set of data. I need to have all the counts in the
history table associated with the same Office, FY, and for the same Reporting
period.

Office1, 2006, 1stQTR, 28, 15, 13

Join the three queries on Office and Year, and make an append query out of the
joined query.

John W. Vinson [MVP]

 

[dsc2bjn]

Joining the three was proving to be a problem, hence the need to post the
question.

After your last reply, I went back and tried joining them again using both
the OFFICE and YEAR. It turns out that I needed to use both fields and LEFT
JOIN both from the Total Query to the OPEN and CLOSED query.

Thanks!!!