Alright I don't know if this can be done but I've been given this task by higher ups so I ahve to at elast try. The database I'm using has different tables going abck once a month for pending sales. These are each essentaily a snapshot in time of how far along the sales are and how many of them are in the pipeline. I need to do some basic analysis on this data. Is there a way to build a query that finds something about one table (say sums the revenue for january) but using some form of input can be switched to using another table? (like than summing the revenue for March.) I'll be checking this thread pretty often until I either find a solution or become convinced this won't work right so don't hesitate to ask any clarifying questions.
Using a single Query for multiple Tables (1 Viewer)
- Thread starter amiscus
- Start date
- Local time
- Today, 08:56
- Joined
- Feb 28, 2001
- Messages
- 27,223
In theory, you don't need separate tables if the sales tables all look the same except for dates. If the tables have a date field in them and that field was the basis for why they were in one table vs. another, you can isolate the months anytime using a query with a Group By function on the DatePart function of that defining date. Or a User-Defined Data format of something like yyyy-mmm and group by that field in a query.
Merge those tables into a single table that contains everything you need for long-term analysis. Use queries to extract anything you need for shorter time periods. Forms, reports, and various other database functionality all work perfectly well using a query-based recordset instead of a table-based recordset.
If that is not allowed, look up union queries as a way to merge multiple tables into a single data stream.
Merge those tables into a single table that contains everything you need for long-term analysis. Use queries to extract anything you need for shorter time periods. Forms, reports, and various other database functionality all work perfectly well using a query-based recordset instead of a table-based recordset.
If that is not allowed, look up union queries as a way to merge multiple tables into a single data stream.
I was just exploring this possibility but my only question is that many of these sales sit around pending for months so they would apear in the list many times. Is there an easy way to combine these tables and add a new autonumber as the primary key. The original Primary keys (the sale ID number) will duplicate otherwise.
Nevermind, I figured out how to add the autonumber. I feel kinda dumb in retrospect cause its easy. (In case anyone finds this through search just go into design view and add a field and select autonumber.) II'm going to play around with this solution for awhile and hopefully it works for everything I need. thanks for the help Doc.
