query and propogate table - very hard 1

[klameer]

I have a database storing details about invoiced air tickets with the following structure (which is very simplified). It is an access database and I can only use the access SQL editor.

Table
TairTrans – a transaction file showing details of an invoice. The pk is the ticket number (tktno). An invoice (invno) could hold multiple tickets, each of different prices (tktamt)

Troute – also a transaction file showing the route (sector1 and sector2) taken and the flight details(flightno). This file contains details of only the first ticket in the TairTrans invoice(tktno).

This is a sample of the of the tables

TAirTrans

tktno invno tktamt

1000 1 100
1001 1 100
1002 1 50
1003 1 50
1004 1 25
2200 2 750
2201 2 750
2202 2 500
2203 2 500
3000 2 1,000
4010 3 200
4011 4 200

Troute

tktno sector1 sector2 flightno
1000 CMB SIN 100
2200 CMB MAA 200
3000 CMB BKK 301
4010 CMB SIN 777


I want to derive a table filling up the tickets with no details in the Troute table with the details corresponding to the particular invoice. I don’t even mind linking to multiple queries. What should come out should look like this…

tktno invno tktamt sector1 sector2 flightno

1000 1 100 CMB SIN 100
1001 1 100 CMB SIN 100
1002 1 50 CMB SIN 100
1003 1 50 CMB SIN 100
1004 1 25 CMB SIN 100
2200 2 750 CMB MAA 200
2201 2 750 CMB MAA 200
2202 2 500 CMB MAA 200
2203 2 500 CMB MAA 200
3000 2 1,000 CMB MAA 200
4010 3 200 CMB BKK 301
4011 4 200 CMB SIN 777

Can this be done?

 

[FancyPrairie]

Create a query that includes both tables and join them on the tktno field. Then, if you want to create a table based on the results of this query, select (via the menubar) QUERY|MAKE-TABLE QUERY... That should do it for you.

 

[klameer]

I have tried this, this query gives me the results of only the first ticket ... this dosent work as I need all tickets per invoice.

 

[FancyPrairie]

If the value of tktno exists in table Troute only when it also exists in TAirTrans, (that is, Troute can't have a tktno that hasn't already been assigned int TAirTrans) then you need to do a Left-Join on the tables rather then the default of Inner-Join. If you don't know how do do this, right click on the join line and select the appropriate check box (Include all records in TAirTrans and only those records from Troute where the join fields are equal).