subreports -v- criteria query

[060159]

I have 8 databases, all different, which record sales for each client. I have created a master database which contains links to all the main tables in those databases.
I want to be able to get a report at the end of each day of all sales across all the client databases. I have set up queries which are ID and date driven, and these tell the database to present sales for that day across all 8 databases. I have then set up a report (consisting of 8 subreports) to give me an rtf which then prints it all out. Phew! Problem is because the underlying queries are date driven, I am being asked for the date 8 times! Any suggestions as to how I can perform this task more efficiently?

 

[kubanvz]

You could create a new table that contains one record that contains the desired date. Then modify your queries' date criteria to be equal to the date field on your new date table, instead of using paramenters. That way, you should only have to input the date once. You would not be prompted for a new date automatically, however. You would need to remember to change it on the table, or write a macro or module to prompt you (or the user) to do so through a form or something like that.

 

[060159]

thanks for this but I really want to completely automate it. The idea is that if I can get this to work, a macro will kick start and automate the whole process so that I dont have to do anything! Keep those suggestions coming-really preciate it guys

 

[BNPMike]

You could use Now() or Date() as criteria to pick up only today's data. If it is not always today then I would use a form where you can change the defaulted date if necessary. Then you can run your stuff using a query connected to the form control.

 

[lespaul]

So if you are trying to completely automate it do you always want to run today's date? yesterday's date? If so you can use Today or Now to get today's date. and DateDiff to use yesterday's.

HTH

leslie

 

[060159]

bnpmike-love your idea-stepxstep please! and yes, it is always going to be on todays date