union query to limit dates

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have many tables going back 20 years. Each table has in common saleperson and date of sale, and various other information. I wrote a union query to find all sales of a particular saleperson for the length of service to our company. Is it possible to write a union query in order to find all sales between certain dates? Such as all months of April or say February 15 to March 1st? thanks for any suggestions.
 
Dear Sue:

Why not create a "Master UNION" of all your data, then query that in
whatever fashion desirable. It would be as though all the stuff were
already in one bit table.

Better yet, use the union to create an actual table with all these
years of sales, index that as needed, and start using that table for
all the query work.

Since it's more convenient to use it this way, why even have 20 years
worth of tables? Start using the one table once you have created it.
This should be easier and quicker anyway.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
I thought in order to do an append query, that all tables must have the same columns? Assuming I am able to combine all the tables, how do I write a query to specific months or periods of time (such as 3/15-4/15)? Thanks again for your suggestions and help.
 
I thought in order to do an append query, that all tables must have the same columns?
Click to expand...

That's true for a UNION query as well. You could create a target table
with instances of all the fields in your current tables and run
multiple Appends; if the yearly tables are not identical, you might
append some fields from one table and other fields from a different
table.
Assuming I am able to combine all the tables, how do I write a query to specific months or periods of time (such as 3/15-4/15)? Thanks again for your suggestions and help.
Click to expand...

You can put in calculated fields (again, in each SELECT of the UNION
query or in a query on the appended single table) extracting just the
month and day into a date/time field with an arbitrary year, for
example this current year: e.g. if the UNION query or the table has a
field SaleDate, you could use

SaleMonthDay: DateSerial(Year(Date()), Month([SaleDate]),
Day([SaleDate])

You can then use a criterion like

Between #3/15# AND #4/15#

Access will assume the current year, matching the calculated field.
 
Dear Sue:

You have already stated, in your original post, "I wrote a union query
to find all sales of a particular saleperson for the length of service
to our company." In order to create this union query, you must have
found a common set of columns to the set of tables. That's a
requirement for creating such a union.

If you have different columns in the various tables, you probably
should consider including all the available information from all these
tables. When a certain column has no equivalent in one of these
"source" tables, you could leave it null.

In designing a destination table for a set of incongruent source
tables, you can consider how each column from those tables should
contribute to the destination table. For a missing column, use NULL
in the union query in most cases, or perhaps zero or an empty string
in some cases.

I strongly recommend having a Source column that identifies from which
of these tables each row originates. That can be useful in tracking
down problems and in explaining some of the differences in these
tables. In the union query, this Source column will be a constant
string or numeric value within each SELECT statement, but different
for eacy SELECT statement that makes up the union.

Once you have decided on how each source table contributes to the
unified table, you can then create this table so it can accomodate all
the various string lengths. Constructing a spreadsheet on paper or
perhaps in Excel that describes how each source table variously
contributes to the finished product would be a good part of the design
process, and would document this later if things become unclear or
modifications are required.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Back
Top