Aaargghh!!!!!! Union Help Please!

[pofe333]

Hello all,

I'm using Access 2007 and am stuck with a Union Query. For a bit of background, I have two tables with cell phone billing information. The first table lists information about the users, such as name, number, department, etc... The second is a downloaded list of charges for the month by number, but it doesn't have the names. So, I've linked the number fields from both tables so I can easily run a query to match the user names contained in my first table with the wireless numbers and charges in the second.

That works fine, but when I download an invoice that has charges or credits for numbers that are no longer in my user table (maybe changed, terminated, etc.), they do not show up in a simple query since there is no relation in the first table. So I made an unmatched query and can get those records easily. That works fine too. My problem now is, when I create a Union query to get all of those records together, I can either get them all with no names, or get only the ones from my first query if I make the user names show up again. How can I get all of these records to show up in one query/report with user names for the ones that have one, and whatever else for the others??

Am I going about this wrong? Is there something more efficient than a Union query? Oh, and my user list can't include those other numbers; it is updated constantly to be current.

 

[boblarson]

You don't need a Union Query, you need a query that uses a LEFT OUTER JOIN. That which pulls all records for the first table and only the records for the records in the second table (call records).

So, what you do is link them by number and right click on the link between them and select JOIN PROPERTIES and then select the appropriate selection of the three.

 

[pofe333]

That was it! Thanks a million.

Follow up question which I'm sure I may eventually answer myself after researching. I have multiple rows in the second table that have no number or name associated with them (they are all irrelevant, but they're in the downloadable file). Without going in and manually deleting them from my table, could I add something to my query that does not display entries that are blank in the number column? Some sort of 'Except if Blank' expression? I don't have much experience with expressions/code.

 

[boblarson]

Use

Is Not Null

as the criteria in the number field criteria spot.

 

[pofe333]

Great, thanks again. You have been tremendously helpful.

 

[boblarson]

 

[pofe333]

Okay, another question related to this. To recap, I created 1 linked table with active user information, and 1 linked table with monthly invoice charges. The wireless number field in the active users table and monthly invoice table is joined. Some of the wireless numbers on the invoice though are not in the 1st table of active users because they may have been cancelled prior to running this query. I've found a spreadsheet I can download which will have only the user information for the numbers that have been disconnected. This leaves me with 3 tables: active users, deactivated users, and monthly charges.

Is there a way to make a query that matches up the charges for active lines with the first table, and then matches up the remaining inactive lines with the second table. My thoughts were that it is now time to make a union query, but I was wrong on that one last time and all it required was a join!

 

[boblarson]

Yes, you can use a Union Query now. Create the query for the Active persons and a separate query for the Inactive persons and then union them together.

 

[pofe333]

Glad to hear I'm on the right train of thought now This is all starting to seep in.