Query Question 2

[georgesOne]

Hi,

I have a formulation db with two tables

Table1
ID Compound Property etc
1 A Yellow
2 B Blue
3 C White etc

and

Table2
ID Compound1 Compound2 etc
1 A B
2 B
3 C A etc

How do I get the following table

ID Compound1 Property1 Compound2 Property2 etc
1 A Yellow B Blue
2 B Blue
3 C White A Yellow etc

My actual table2 has six Compound(i) fields and and must contain at least Compound1 (but does not need to have Compound2, etc).

I have tried join and union queries, but somehow I cannot get the result.

Thanks for any tip, georges

 

[FoxProProgrammer]

Georges,

I don't understand where Property2 comes from in the final output. The following Query gets you everything except Property2.

SELECT Table1.ID, Table2.Compound1, Table1.Property, Table2.Compound2, Table2.etc
FROM Table1, Table2
WHERE (((Table1.ID)=[Table2].[ID]));

Please explain what Property2 is and I'll try to come up with the rest of the query.

Thanks,

dz
dzaccess@yahoo.com

 

[georgesOne]

dz,

thanks for your help.
Property1 and Property2 should be the same as Property in table1.
I have tried your suggestion, but the result would be

ID Compound1 Property Compound2 Property
1 A Yellow B YELLOW
2 B Blue BLUE
3 C White A WHITE

What I want is

ID Compound1 Property Compound2 Property
1 A Yellow B BLUE
2 B Blue
3 C White A YELLOW

That is actually the problem. I have one property in table1 for each compound. In table2 I have some of this compounds together. In the new table I want to show the compounds and their respective colors.
May be my table layout does not allow this???

Any thoughts? Thanks, Georges

 

[georgesOne]

dz,

actually I got it working but I would prefer not to use the DLookUp:

SELECT
Table2.Compound1, DLookUp("Property","Table1", "[Compound] = " & """" & Table2.Compound1 & """") AS Expr1,
Table2.Compound2, DLookUp("Property","Table1", "[Compound] = " & """" & Table2.Compound2 & """") AS Expr2, .......

Any idea?

Thanks, Georges

 

[FoxProProgrammer]

Georges,

I understand where Property2 comes from now. It's funny because the only way that I got it to work was to use the DLookUp function as well. I'll think about it some more tomorrow if you haven't already gotten it to work another way.

dz
dzaccess@yahoo.com

 

[lespaul]

to get this query:

SELECT T2.Compound1, T1A.Property, T2.Compound2, T1B.Property
FROM T2
INNER JOIN T1 ON t2.Compound1 = T1A.Compound
INNER JOIN T1 ON t2.Compound2 = T1B.Compound

to fix this problem, normalize your database: 'The Fundamentals of Relational Database Design'

If you have more than just two compounds, then I would create a query to normalize your structure and redo this query based on the normalized data.

HTH

Leslie

 

[lespaul]

minor change:

SELECT T2.Compound1, T1A.Property, T2.Compound2, T1B.Property
FROM Table2 AS T2
INNER JOIN Table1 AS T1A ON t2.Compound1 = T1A.Compound
INNER JOIN Table1 As T1B ON t2.Compound2 = T1B.Compound

that should do it, change Table2 to your table name and Table1 to the other.



Leslie

 

[PHV]

Leslie, I think we have to use LEFT JOIN for Compound2, ..., Compound6.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[lespaul]

I don't think so. For each instance of Compound(1, 2, 3, 4), with this solution he would need to join into the descriptor table for each one. HOwever, in re-reading the post, it looks like there are multiple Compound fields. So I would revert to my original post that first the data needs to be normalized:

SELECT ID, Compound1 As Compound FROM tblName WHERE Compound1 <> ''
UNION
SELECT ID, Compound2 FROM tblName WHERE Compound2 <> ''

UNION
SELECT ID, Compound3 FROM tblName WHERE Compound3 <> ''

continue unioning the selects together until you get to CompoundN. Now you will have a query that returns a dataset:

ID Compound
1 A
1 B
2 B
3 C
3 A

Now you can take the normalized query and use it as the source for this query:

SELECT ID, Compound, Property FROM qryName INNER JOIN Table1 ON qryName.Compound = Table1.Compound

This will give you:

ID        Compound        Property
1            A            Yellow
1            B            Blue
2            B            Blue
3            C            White
3            A            Yellow

now you can use this as the source for a report and group the items in the desired way.

HTH

Leslie

Leslie

 

[PHV]

So, my guess:
SELECT A.ID, A.Compound1, B1.Property As Property1, A.Compound2, B2.Property As Property2, ..., A.Compound6, B6.Property As Property6
FROM Table2 A
INNER JOIN Table1 B1 ON A.Compound1=B1.Compound
LEFT JOIN Table1 B2 ON A.Compound2=B2.Compound
...
LEFT JOIN Table1 B6 ON A.Compound6=B6.Compound
;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[georgesOne]

Thanks, guys,

I will check it out tonight.
PHV - one thing I do not understand: what is tables B1, B2...B6? Is the syntax in your INNER JOIN and LEFT JOINS correct?

Thanks, Georges

 

[lespaul]

tables B1, B2, .. B6 are alias for the descriptor table.

because your structure is:

ID Field1 field2 field3

you need to join into the descriptor table for each field1, field2, field3 etc.

SELECT A.ID, A.Compound1, B1.Property As Property1, A.Compound2, B2.Property As Property2, ..., A.Compound6, B6.Property As Property6
FROM Table2 A
INNER JOIN Table1 B1 ON A.Compound1=B1.Compound
LEFT JOIN Table1 B2 ON A.Compound2=B2.Compound
...
LEFT JOIN Table1 B6 ON A.Compound6=B6.Compound

So the query is saying:

Select the ID field from 'A' (an alias for Table2 - so you don't have to type Table2 over and over, you can use 'A'), select Compound1 From 'A', Compound1 is joined to the descriptor table (Table1) through the INNER JOIN, so the descriptor for the value found in Compound1 is in the Table1 with the alias B1, so you want the matching Property B1.Property; to get the descriptor of Field2, you need to join Field2 to the SAME descriptor table, but it needs a DIFFERENT name, 'B2'. So when you get Compound2 you want the descriptor that matches it. PHV has changed the subsequent joins to LEFT which means:

'Give me all the records from the table on the left side of the join and only those that match from the table on the right side of the join'

He did that because not every record has multiple compounds; since all the records have at least 1 compound, the join into Field1 can be an inner join, since compound2 may be blank you need the left join to eliminate it.

Hope that makes some sense and explains the alias and multiple joins.





Leslie

 

[georgesOne]

Hi LesPaul,

my query now looks like this (with the original table and field names).

SELECT tbl_AZPolymers.MonoName1, B1.Molweight AS Molweight1, tbl_AZPolymers.MonoName2, B2.Molweight AS Molweight2, tbl_AZPolymers.MonoName3, B3.Molweight AS Molweight3, tbl_AZPolymers.MonoName4, B5.Molweight AS Molweight5, tbl_AZPolymers.MonoName5, B5.Molweight AS Molweight5, tbl_AZPolymers.MonoName6, B6.Molweight AS Molweight6
FROM tbl_AZPolymers
INNER JOIN tbl_Monomer AS B1 ON tbl_AZPolymers.MonoName1 = B1.[Monomer ID]
LEFT JOIN tbl_Monomer AS B2 ON tbl_AZPolymers.MonoName2=B2.[Monomer ID]
LEFT JOIN tbl_Monomer AS B3 ON tbl_AZPolymers.MonoName3=B3.[Monomer ID]
LEFT JOIN tbl_Monomer AS B4 ON tbl_AZPolymers.MonoName4=B4.[Monomer ID]
LEFT JOIN tbl_Monomer AS B5 ON tbl_AZPolymers.MonoName5=B5.[Monomer ID]
LEFT JOIN tbl_Monomer AS B6 ON tbl_AZPolymers.MonoName6=B6.[Monomer ID]

I get an syntax error message including the bold part.
However the query

SELECT tbl_AZPolymers.MonoName1, B1.Molweight AS Molweight1, tbl_AZPolymers.MonoName2
FROM tbl_AZPolymers INNER JOIN tbl_Monomer AS B1 ON tbl_AZPolymers.MonoName1 = B1.[Monomer ID];

runs ok. As soon as I add the LEFT JOIN I get an error. Any syntax error in my joins?

Sorry to bother you - but after three hours I am done...

Regards, Georges

 

[PHV]

You may try this:
FROM (((((tbl_AZPolymers AS A
INNER JOIN tbl_Monomer AS B1 ON A.MonoName1 = B1.[Monomer ID])
LEFT JOIN tbl_Monomer AS B2 ON A.MonoName2=B2.[Monomer ID])
LEFT JOIN tbl_Monomer AS B3 ON A.MonoName3=B3.[Monomer ID])
LEFT JOIN tbl_Monomer AS B4 ON A.MonoName4=B4.[Monomer ID])
LEFT JOIN tbl_Monomer AS B5 ON A.MonoName5=B5.[Monomer ID])
LEFT JOIN tbl_Monomer AS B6 ON A.MonoName6=B6.[Monomer ID]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[georgesOne]

Hello,

one final question though:

I have a quite complex form where many fields are filled by coded DLookups - code is similar as in my third post. It works, but sometimes it is somewhat slow.
Can I expect reasonable speed enhancements when I would rewrite the code using joins in a query?
Looks like it should, right?

Thanks, Georges