Create a union query (1 Viewer)

AHMEDRASHED

Member
Local time
Today, 04:04
Joined
Feb 2, 2020
Messages
59
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


1689852681823.png
1689853523676.png


1689853188957.png
 
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.
 
Last edited:
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
 

Attachments

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.
 
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
 
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.
 
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.
 
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
 
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
 
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.
 

Attachments

  • RI.jpg
    RI.jpg
    147.9 KB · Views: 82
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.
 
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.
1689863382195.png
tblBill = table of bills = equal = purcheses from our ( suplliers ) to show how much total bill unpaid for each suppliers
 
. 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! 🗄️💡📊
 

Users who are viewing this thread

Back
Top Bottom