Query to display data from 3 transaction MS access tables

Question

User1343581070 posted

Hi,

     I have 2 Master Files viz. Customer Master and Machine Master

     I also have 3 transaction files viz. Despatch_Details , Installation_Details and Service_Report_Details

     All the 3 files have common fields machine_srno and customer_id and they can be linked to the Machine Master and Customer Master by  

     these fields

     My report format is like : Despatch Date , Customer Name , Machine Srno, Install Date , Service Report Date , Name of Engineer

     Now I want to display data from any or all of these 3 files which contain data . (ie display data only from files which have data) I dont want

     to have any relations set between tables

 

Thanks & regards

 

Sanjish

Answer 1

User269602965 posted

Use the MS Access Query builder.  Add the tables, join the keys, drop the desired columns from each table in the order you like, apply any filters or in the case of dynamic queries, parameters, save the query, and then you can call from .NET with OLEDB, passing parameters if required.  the tables do not need referential integrity relationships from the table designer to make the query with key joins.

Answer 2

User1343581070 posted

The 3 tansaction files are related to each other through machine_srno but i require both left and right joins on 2 tables. How is that possible ?

Regards

 

Sanjish

Answer 3

User-1199946673 posted

You write 'files' where you mean 'tables'. You show us the report format, but you don't say form which table the fields come from.

If you want help, please specify exatly the table structure and which query you want to perform?