query two or more tables

[giandeo]

Hello everybody. I am in a mess. I am unable to retrieve data from three tables. Please advise.

I have 3 tables in access. None of them has a primary key.

Table 1
Passenger surname
Passenger other name
flight number

Table 2
Passenger surname
Passenger Other name
Offence
tablename


Table 3
Passenger surname
Passenger Other name
Offence
tablename

I understand that there should be a primary key for each table, but there is no such key in any table.

Table 1 is dynamic, meaning that there could be a number of flight landing in a day

Table 2 and 3 are static

I wish to pass a query so that I could generate a list containing the surname and other information of passengers matching either table 1 or table 2

The expected result may appear like this

Surname | Oname | Offence | Tablename
Tom | J | x | Table1
Tom | J | y | Table2
Tom | w | r | Table1
Arnaud | J | y | Table2
Brown | d | s | Table1
Brown | d | y | Table2

Please help me out.......

[shweta123]

Hi,

Can you please more clear about your doubt?
Because here as you have specified Table2 and Table3 are containing same fields. Have you taken it with any purpose?

Table 1
Passenger surname
Passenger other name
flight number

Table 2
Passenger surname
Passenger Other name
Offence
tablename


Table 3
Passenger surname
Passenger Other name
Offence
tablename

I understand that there should be a primary key for each table, but there is no such key in any table.

Table 1 is dynamic, meaning that there could be a number of flight landing in a day

Table 2 and 3 are static

I wish to pass a query so that I could generate a list containing the surname and other information of passengers matching either table 1 or table 2

The expected result may appear like this

Surname | Oname | Offence | Tablename
Tom | J | x | Table1
Tom | J | y | Table2
Tom | w | r | Table1
Arnaud | J | y | Table2
Brown | d | s | Table1
Brown | d | y | Table2

[giandeo]

Hi
Yes.
Table 2 and table 3 contains the same fields but different data from different sources. In fact, they are watchlists through which data from table1 will be filtered.I have included a source (tablename) to differentiate between the tables.

If the surname of a passenger from table1 matches the surname in table2 and also matches the surname from table3 then, I wish to get two records with same surname.

If the surname of a passenger from table1 matches the surname in table2 or table3 ONLY then, I wish to get one records with same surname.

Frankly speaking, I am trying to help someone developing an application to track risky travellers. Suggestions from anyone would be most welcome.

I am using ASP and MS Access to do this little trick.

I have given it a try. Here are the codes:

set adorst = server.createobject("ADODB.recordset")
 
adorst.Open "SELECT table1.sname, table1.oname, table2.offence, table3.offence, table2.source, table3.source from table1, table2, table3 where table1.sname=table2.sname or table1.sname=table3.sname order by table1.sname", adocon
 
do while not adorst.eof
      response.write adorst("sname")
      response.write adorst("oname")
      response.write adorst("offence")
      response.write adorst("source")
 
                  response.write("</br>")
adorst.movenext
loop
 
%>

Kindly note that the code does not work!!! The system is stuck and no response. I need to reboot the pc again.

Please advise .....

[jhardman]

Hi
Yes.
Table 2 and table 3 contains the same fields but different data from different sources. In fact, they are watchlists through which data from table1 will be filtered.I have included a source (tablename) to differentiate between the tables.

If the surname of a passenger from table1 matches the surname in table2 and also matches the surname from table3 then, I wish to get two records with same surname.

If the surname of a passenger from table1 matches the surname in table2 or table3 ONLY then, I wish to get one records with same surname.

Frankly speaking, I am trying to help someone developing an application to track risky travellers. Suggestions from anyone would be most welcome.

I am using ASP and MS Access to do this little trick.

I have given it a try. Here are the codes:

Kindly note that the code does not work!!! The system is stuck and no response. I need to reboot the pc again.

Please advise .....

I don't think you can do that in one query unless you got really creative with your sql - maybe you could post in a db forum. Adding a primary key wouldn't necessarily help with this step (although I would highly recommend that you add one before you get much further).

Jared

[JamieHowarth0]

Hi guys,

Two words - UNION ALL. Allows you to join the resultsets of two tables with matching schemas together to create one resultset.

SELECT * FROM Table1 INNER JOIN
(SELECT * FROM Table2
UNION ALL
SELECT * FROM Table3) AS JoinedData
ON Table1.Surname = JoinedData.Surname
 

In this example, we do the UNION ALL on the two identical tables (data that comes from different sources). Then, we join on Table1 (flight details) to our newly created resultset on an individual's surname (and optionally firstname, but I left this bit out).

Hope this helps.

medicineworker

[giandeo]

Hi guys,

Two words - UNION ALL. Allows you to join the resultsets of two tables with matching schemas together to create one resultset.

SELECT * FROM Table1 INNER JOIN
(SELECT * FROM Table2
UNION ALL
SELECT * FROM Table3) AS JoinedData
ON Table1.Surname = JoinedData.Surname
 

In this example, we do the UNION ALL on the two identical tables (data that comes from different sources). Then, we join on Table1 (flight details) to our newly created resultset on an individual's surname (and optionally firstname, but I left this bit out).

Hope this helps.

medicineworker

Yes. The solution works fine. Thank you so much ......

[JamieHowarth0]

You're welcome :-)

medicineworker