Need Current date w/data daily, maintaining prev dates 1

[kentwoodjean]

Trying to make a query from a talble so that I can get a current inventory count for each day while keeping the previous dates in place. Below is the SQL of where I am at so far. The tblLastUpdated contains several dates having to do with when the contract was received, when it was last updated, but nothing that day by day gives me the ending inventory number. I cannot adjust the primary tables as they are downloaded from our main frame and will populate the numbers in my query. Can I add a field to the query to reflect current day, and tomorrow show that date and number as well as today’s date and number? In other words, everyday will reflect a new date or the current date with the current inventory count while all the past days and their count will remain as well.

SELECT Count(tblLastUpdated.[Contract #]) AS [CountOfContract #], RespCdeLst.RespMain
FROM tblLastUpdated INNER JOIN RespCdeLst ON tblLastUpdated.[Resp Cd] = RespCdeLst.RespCde
WHERE (((tblLastUpdated.[Closed Dt]) Is Null))
GROUP BY RespCdeLst.RespMain
HAVING (((RespCdeLst.RespMain)="professional"));

 

[lupins46]

You can only report on data that is held in tables or derivable from the data in tables. There is no concept of a query 'remembering' previous answers unless you create a table to hold those answers.

If you need to hold on to previous results then you need to use an append query to add each days results to a table. You can then report of that table for your day-by-day values.

 

[kentwoodjean]

Well, it was worth a try and you don't know if you don't ask. I will go ahead to set up an append query which then leads me to one other question. I would like to set it up so the append query runs the first time the query is opened each day. since I do not do well in writing my own macros and conditions or modules, can you tell me which items I would select when I select Macro, and New to create it. I have never done something like this.

 

[GingerR]

Create a new macro. Select ACTION = OpenQuery. Then down below, choose your query name. Close the macro and run it (You may wish to have a test query appending to a test table). See how it asks you if you are sure you want to do this? In order to get rid of that pop-up message, go back into the macro, and in the line ABOVE your OpenQuery action, pick SET WARNINGS. Down below, make sure it says NO. Then you have to make sure to set warnings back on, so in the line right after your OpenQuery line, choose SET WARNINGS again, but down below pick YES. Save the query and run it again. All done.

If you want this query to run when you first open the database, you can name it Autoexec and it will do that. However, note that it will run whenever ANYONE opens the database. If this is a back-end db, that's fine, since it will only be YOU opening it. So you can open it once and the Autoexec macro runs. Every other time you open it during the day, hold down the shift key before double-clicking on the db, and it will NOT launch any macro called 'autoexec.' Another option is to schedule (in Windows Scheduler) to open the db at like 4:00 am; again, when it opens, the Autoexec macro will launch, and the data will be there before you even get to work!

And...you'll have to figure out how it only runs "THE FIRST TIME" each day. How about making another table called Admin, with a date/time field ('RefreshDate') in it? And a query which updates that field to the current day's date? Put that query in your macro as well, after the first one. Then, in your macro, make a new first (blank) row above all other rows. In the Condition statement on the first line, put something like

Dlookup("RefreshDate","Admin")=Date()

. In the ACTION column of the first line, put StopMacro. This way, if the append query has already run today, it won't run again.

I know this is a lot of info, but should be enough for you to figure out what you want to do. You can also do this in a code module (some day, convert your macro to code using the menu bar to see what it looks like).

Hope this helps--g

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[kentwoodjean]

Wonderful help, and it is all working really well. This will be very useful in a lot of situations. It gets a bit confusing when you look at the many choices you have in the Macro window.

For my own knowledge, can you tell me this. I would assume you can have only one auto exect. therefore, if you were to have say 4 different queries that you wanted to open and run when data base first opened, can this all be put on the same autoexec macro?

 

[GingerR]

Yes. You can put whatever you want in the same macro. Take a look at a text book for more explanation.

Good luck!

g

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244