Creating query from 2 tables using date criteria

[pofe333]

There has to be an easy answer staring me right in the face, but I can't see it. I've got two tables: one for charges, one for credits. Both tables are updated on the 15th of the month with new charges or credits with a listed mobile number. I need to run a report each month that includes the total amount per number (charges minus credits) for the dates I enter.

Both tables have a date column, wireless number column, and amount column. Both have a BETWEEN DATE criteria on date and work when run individually. But, if I create a new query and join on wireless number, the query ignores the date criteria. If I join on date, the query applies the credits to all wireless numbers. How can I join these so that if I enter 03/14/09-03/16/09 it returns the charges and credits per line only for that period from both tables? What am I missing? Thanks in advance for any assistance.

 

[Rabbie]

Have you considered using a Union query to get the information you want from your tables. Access help has some information about these.

 

[pofe333]

Have you considered using a Union query to get the information you want from your tables. Access help has some information about these.

I've been messing around with a Union Query for the last 30 minutes or so. The problem is my two queries don't contain all of the same columns. And I still seem to be running into Access either matching the date or the credit amounts incorrectly.

*To clarify, my queries pull the following:

Query 1 - Charges
Date, Account Number, Wireless Number, User Name, Department, Charges

Query 2 - Credits
Date, Wireless Number, Credits

Query 1 is pulled from a spreadsheet I download every month, Query 2 pulls from a table I have to manually add data to. I would like to keep the first query with all of the fields it has because I also need to run a monthly report with the detailed charges per department.

 

[pofe333]

I finally got the union query working last night! I figured I would post this question here though instead of creating a new post somewhere else. My goal is to create separate reports that I can send to the managers of about 15 departments. I'd rather not create 15 reports, so I figured using a form w/ a combo box would work. There is a Cost Center field in my query output, but when I created a combo box it didn't group the similar departments because there are multiple instances of them within my source table. For example, my source table would have the following records:

Date | Account | Department | Name | Number | Charges
2/25/2009 | 123456789 | IT Department | Jack | 111-111-1111 | $34.99
2/25/2009 | 123456789 | IT Department | Joe | 111-111-1111 | $54.99
3/25/2009 | 123456789 | IT Department | Jack | 111-111-1111 | $34.99
3/25/2009 | 123456789 | IT Department | Joe | 111-111-1111 | $54.99

Because IT Department is listed for several records, my combo box doesn't seem to be grouping it as one department if I point the source to the Department field in my query (or table). It lists each one individually as it corresponds to a record. Has anyone done this? Am I forgetting to do something or is this inherent?