Create a union query

[AHMEDRASHED]

Hi

i try for long time To create a union query to make filter by (Customer name or Customer id )
Customer Name data : same source table ( tblcustomers )
want if i SEARCH BY Customer name To show recordes form (tblinvT& tblinvH ) ,

maybe error in relations to create it

1st screenshot query
2nd screenshot all relations
3rd screenshot the Form filter search by Customer

 

[ebs17]

maybe error in relations

YES!!

make filter by Customer name or Customer id

This information is in exactly one table (=> tblCustomers). There should only be one foreign key in tblInv.

Why is there the doubling T - H?

In most cases, the request for a UNION query indicates an imperfect database schema.

Technical: A UNION query is simple. Each subquery used by UNION must have the same number of fields in the SELECT part, and the data types of the fields must also match for appending. Definitions such as field names (aliases) take place in the first partial query.

 

[AHMEDRASHED]

T short name for Tickets , H for Hotels

YES!!

This information is in exactly one table (=> tblCustomers). There should only be one foreign key in tblInv.

Why is there the doubling T - H?

In most cases, the request for a UNION query indicates an imperfect database schema.

Thank you for your replay
T = Tickets & H =Hotel , its 2 different department

 

[ebs17]

T = Tickets & H =Hotel , its 2 different department

The same information belongs in the same table.
You can differentiate between departments in an additional field in this table. You can also see that additional departments can easily be added and managed.

 

[AHMEDRASHED]

The same information belongs in the same table.
You can differentiate between departments in an additional field in this table. You can also see that additional departments can easily be added and managed.

can you explain more or one example please

 

[mike60smart]

can you explain more or one example please

Can you explain your business process?

an example = A Customer on a specific Date will Order a number of Items.

 

[ebs17]

The tables in the blue frames in your image contain the same field names. Don't you see this obvious? It hurts my eyes.
=> Merge! Already in the table, not only query via UNION.

I don't know how to explain that extra. But I can add that a UNION query gives you additional difficulties beyond your problems with the creation itself. With the UNION query you lose the use of indexes and thus possible performance, i.e. when filtering. For some, that's a minor weakness. In a real database with lots of data, this is a disaster.

 

[Pat Hartman]

1. Every table needs a primary key.
2. You are confusing yourself with your PK-FK names. Unless you have a conflict, it is simpler on the brain to use the same name as the FK to match the PK it points to.
3. Cleaning up the relational diagram wouldn't hurt either.
4. You have an extra, duplicate column in the customer table.
5. If you want customer name to be unique, you need to create a separate, unique index. Making the PK the two fields - Autonumber and name, doesn't do what you want. The autonumber is already unique. Adding a second column to the index doesn't make it more unique. Leave the autonumber as the PK. That is what the joins will be on. Add a separate unique index for the name column.

 

[AHMEDRASHED]

Can you explain your business process?

an example = A Customer on a specific Date will Order a number of Items.

Yes Mr mike , A Customer on a specific Date will Order a number of Items (Tickets ) on on other specific Date will Order a number of Hotels . , i make it seprate in tables ticket & hotel cuz diffrent in some fields

 

[AHMEDRASHED]

The tables in the blue frames in your image contain the same field names. Don't you see this obvious? It hurts my eyes.
=> Merge! Already in the table, not only query via UNION.

I don't know how to explain that extra. But I can add that a UNION query gives you additional difficulties beyond your problems with the creation itself. With the UNION query you lose the use of indexes and thus possible performance, i.e. when filtering. For some, that's a minor weakness. In a real database with lots of data, this is a disaster.

The same information belongs in the same table.
You can differentiate between departments in an additional field in this table. You can also see that additional departments can easily be added and managed.

i will try to make it in one

 

[mike60smart]

Yes Mr mike , A Customer on a specific Date will Order a number of Items (Tickets ) on on other specific Date will Order a number of Hotels . , i make it seprate in tables ticket & hotel cuz diffrent in some fields

Hi
The attached is more in line with your process.

You currently have a Customer with an Invoice on a Specific Date for Either Tickets or Hotels.

Your Payments table should be linked to the Invoice Table.

Not sure where your Bill table fits into your process. Please explain.

 

[AHMEDRASHED]

Hi
The attached is more in line with your process.

You currently have a Customer with an Invoice on a Specific Date for Either Tickets or Hotels.

Your Payments table should be linked to the Invoice Table.

i will link it after

Not sure where your Bill table fits into your process. Please explain.

Bill TABLES names = tblBillT & tblBillH

 

[mike60smart]

If you Invoice the Customer what is the purpose of tblBill??
If the Customer does not pay the Invoice Amount Due then you would send a reminder.

 

[AHMEDRASHED]

If you Invoice the Customer what is the purpose of tblBill??
If the Customer does not pay the Invoice Amount Due then you would send a reminder.

tblBill = table of bills = equal = purcheses from our ( suplliers ) to show how much total bill unpaid for each suppliers

 

[mike60smart]

tblBill = table of bills = equal = purcheses from our ( suplliers ) to show how much total bill unpaid for each suppliers

OK thanks

 

[hango]

. Every table needs a primary key.
2. You are confusing yourself with your PK-FK names. Unless you have a conflict, it is simpler on the brain to use the same name as the FK to match the PK it points to.
3. Cleaning up the relational diagram wouldn't hurt either.
4. You have an extra, duplicate column in the customer table.
5. If you want customer name to be unique, you need to create a separate, unique index. Making the PK the two fields - Autonumber and name, doesn't do what you want. The autonumber is already unique. Adding a second column to the index doesn't make it more unique. Leave the autonumber as the PK. That is what the joins will be on. Add a separate unique index for the name column.

Valuable advice for database design: prioritize a clear primary key, simplify FK-PK naming, optimize the relational diagram, eliminate duplicates, and use separate unique indexes for desired uniqueness. Efficient data management matters!

 

[Pat Hartman]

@hango Thank you for confirming my "excellent" advice. And welcome to AWF.

 

[AHMEDRASHED]

Thank you Mr. Pat Hartman for it , I am learning about it now , and I have already fixed some of it .​

 

[apr pillai]

Check this Union Query Demo and the Rules goes with it in building the Query: https://www.msaccesstips.com/2008/02/union-query.html