Union Query - why didn't I use it before?

[PNGBill]

We wanted a list of All Tel, Mob and Fax numbers so we can identify numbers on our Telephone Account.

to achieve this I created:

1 Report
1 macro
9 Queries
1 temp Table

OK - it was a while ago

Just replaced this pile of clutter with:
1 Report which has 1 Union Query - Multistory - and inserted into the report.

Didn't keep a good track of Mb's saved but the Temp table had 13,000 records - only 2 fields.

Anyone want some 2nd hand temp tables and macros ?

 

[vbaInet]

If they're tailor made I will buy some, but actually no thanks, I use better means

 

[PNGBill]

just discovered another report that can be done easily with Union Query yet I went around the long way

 

[vbaInet]

You're getting quite excited about this union query thing. I will go easy on them because they're slow.

 

[PNGBill]

Thanks for the Heads Up on this.

Slower then 9 queries and a temp table?

These are monthly reports which are not a problem if a few seconds to run.
Actually, some of them are hardly used.

Current system takes 15 to 20 seconds.

 

[vbaInet]

Maybe not slower. Test it out and see how many seconds it saves.

 

[Galaxiom]

I expect you have a table with three fields for phone, fax and mobile numbers. This is a common but very, very limited way to do contact numbers.

Better to use a separate table with ContactID, NumberTypeID and PhoneNumber. (This can also be extended to separate fields for InternationalCode, AreaCode and PhoneNumber) A Comment field is also useful.

This puts all the numbers in one field (or set of easily concatenated fields) and allows any number of numbers to be related to a single contact. It also allows any number of NumberTypes and supports duplicates of the NumberType. Work, Home, Mobile, Mobile (again), Fax, whatever etc

 

[PNGBill]

Good point.

We have contact numbers for Customers in the Customers table and Employers of customers in the Employers table. ie contacts for both tables.

Some rainy day I should sort this out but should an employer have 50 employees who are all Customers of ours then there would be 50 additional records in the Contacts Table where as there is only one record in the Employers Table.

Customers have Bus Tel as a field and Employers tel is usually the HR dept of the Employer.

 

[Poppa Smurf]

Members beware! PNGBill is an UNION man. Soon he will be asking all members to join the UNION movement.

 

[PNGBill]

Members beware! PNGBill is an UNION man. Soon he will be asking all members to join the UNION movement.

After what they did to poor Kevin, not likely

 

[PNGBill]

ooopppss Now I know the difference between UNION and UNION ALL.

Wondered why the figures didn't look right.

UNION takes the liberty of stripping out duplicate records

This must be the system my bank uses

 

[Galaxiom]

UNION takes the liberty of stripping out duplicate records

This must be the system my bank uses

I wish mine would do that.
Every month my bank repeats a record called Interest Charged.

 

[PNGBill]

Yes. Clear Duplicate Record issue

 

[vbaInet]

You can tell these banks never apply DISTINCT or DISTINCTROW