Query which shows data from various subforms...Help pls 1

[schredder]

Hi all

Have a Form called "Offers" in which - depending on selecting the product category first in a combo box - the following subforms may pop up. The subforms are called "Offers_IP", "Offers_FM" and "Offers_MTA" and contain many different fields according to product categories (IP,FM,MTA). The seven fields in the main form "Offers" are the same for all the three sub offers, so thats why they are in the main form. The tables behind the forms look obviously the same. I related the table by "Offers_ID" which in the table "Offers" is the primary key.
Now i would like to design a query which shows all the offers with all the fields for the different offers entered and not only the fields of the table "Offers".
Hope this is explaining enough.
Any help is gr8ly appreciated.
Chris

 

[BarkingFrog]

This statement will give you everything in all tables based on Offers_ID. I couldn't quite follow what you were saying but I think this is what you wanted.

SELECT a.*, b.*, c.*, d.* FROM Offers a, Offers_IP b, Offers_FM c, Offers_MTA d WHERE a.Offers_ID = b.Offers_ID and a.Offers_ID = c.Offers_ID and a.Offers_ID = d.Offers_ID

 

[schredder]

thx for reply...i tried like below and dont get any records:

SELECT Offers.OffersID, [Offers FM].Fund, [Offers IP].Modul, [Offers MTA].[Art MT Advice]
FROM Offers, [Offers FM], [Offers IP], [Offers MTA]
WHERE
Offers.OffersID=[Offers FM].OffersID
AND
Offers.OffersID=[Offers IP].OffersID
AND
Offers.OffersID=[Offers MTA].OffersID

Thanks
Chris

 

[BarkingFrog]

I do not know how much technical knowledge you have concerning databases. So if I say something that you already know, please do not take it as an insult.

Your query looks correct and should be working. If it isn't then the data in your tables must not be consistent across all tables. In other words, you must have the same OffersID in all the tables in order for the statement to work. If any of your tables has a missing corresponding OffersID to the one located in the Offers table then you cannot pull the info because your link is broken between the Offers table and whichever table is missing the corresponding OffersID. Does this make sense?

Example: Let's say you have an OffersID of 1 in the Offers table. All tables have an OffersID of 1 as well except for Offers IP. If Offers IP is missing the OffersID of 1 then your query cannot work because it cannot make the link between the Offers table and the Offers IP table.

So, please make sure that your data is consistent across all tables.

Hope this helps.

 

[schredder]

hrm, i see what u mean. think this doesnt lead to the result i would like to have. for a final effort may be it makes sense that i post all the fields of 3 of the mentioned tables so that i can show better what the query should look like at the end:
Table "Offers" Table "Offers IP" Table "Offers FM"
Field OffersID Field OffersID Field OffersID
Field pdc Field Modul Field Fund
Field SendBy Field Ccy Field Mfee
Field Date Field Amt Field Ifee
Field Chances Field Fix
So the query finally should "List/Merge" all the entries like:

OffersID/pdc/SendBy/Date/Chances/Modul/Ccy/Amt/Fund/Mfee/Ifee/Fix
1/IP/email/apr/80%/1/bb/usd/1000/Null/Null/Null/Null/Null
2/FM/email/apr/60%/Null/Null/Null/Null/2/spid/2%/1%/500

So the query actually should create a new table like shown above where Null indicates the field is left blank.
I'm not really into SQL but shouldn't this work with "UNION SELECT" or so ?
Thanks in advance
Chris

 

[BarkingFrog]

OK, I see where the problem is. You do not have consistent data across all tables. You will need to use outer joins on your query statements.

Try the following:

SELECT Offers.*, [Offers FM].*, [Offers IP].*, [Offers MTA].*
FROM ((Offers LEFT JOIN [Offers FM] ON Offers.OffersID = [Offers FM].OffersID) LEFT JOIN [Offers IP] ON Offers.OffersID = [Offers IP].OffersID) LEFT JOIN [Offers MTA] ON Offers.OffersID = [Offers MTA].OffersID

You should get everything in all tables by using this query.

 

[schredder]

hammer. thx for the patience. chris