Combine 2 Tables as Recordsource

[DeoM]

Hi,
I have a sort of a work order system where i have 2 tables: on-going work order table and completed work order. In a single form, can i make a search which will show all on-going work and completed work for a certain customer? This means that my form will sort of combine the 2 tables. Is there a way to create a query to have the 2 tables combined?
Thanks,
Jun M

 

[Zion7]

Yes!

For starters, you may want to create a relationship between the 2 tables (assuming, they have a common field?).

Either way, you can do this prior (assure referential integrity), or during design time from the Query pane.

Create the Query, give it a name, Save it, then use it as the recordsource for your form....

 

[DeoM]

Thanks Zion7. Did that. Table1 (on-going work orders) has 3 records and Table2 (completed work orders) has 1 record. The query should show 4 records but it only shows the 3 records of Table1 and not the record of Table2.

 

[Zion7]

No, the total should only be 3.
Table 2 should be repeated 3 times, (for every record in Table 1).

BUT, I believe you want a UNION QUERY????

Union queries can not be created from the query pane, only from the SQL window.

Are you familiar with the syntax & SQL window?

 

[DeoM]

Please Zion7 help me with the SQL syntax for a UNION QUERY. I am not familiar with Union Queries. Will appreciate it very much.

 

[Zion7]

First, all the fields that you choose, from all tables, must be the same datatype.
Basically, it's one table. All columns must have the same data types as usual.

SELECT txtDate, txtCustomer, txtCost FROM tblProjects UNION ALL SELECT txtDateOrdered, txtClient, txtPrice FROM tblOrders;

...3 Tables

SELECT txtDate, txtCustomer, txtCost FROM tblProjects UNION ALL SELECT txtDateOrdered, txtClient, txtPrice FROM tblOrders UNION ALL SELECT txtDateSent,txtCourier, txtInvoiced FROM tblDelivery;

must be same data type, wherever the columns line up...

 

[DeoM]

Thanks a lot Zion7! Worked perfectly. Its a big table actually so i chose SQL specific in the query window and modified the SQL. Its a long SQL but i copied and paste and modified. Took me few minutes to write the correct SQL; like over a thousand char long. Thats awesome!