Help desperatleyneeded

[Anon]

Hi,

(Foreword notice: If the below example of SQL query does not make sense, please see the end of the document for an example of how I am attempting to present the data - Thanks 🙂).

I am using MS-ACCESS to query a databases over several tables. The tables that I am querying are:

PRODUCT - PROD_SRS_ID (Primary Key)

REFERENCE - PROD_SRS_ID & REFERENCE_ID (Compound Primary Key)

DEFINITION - PROD_SRS_ID & DEFINITION_ID (Compound Primary Key)

Relationship - For each product of a unique identifier, there exists multiple Definitions and References.

Product has a one to many relationship with both REFERENCE and DEFINITION table.

Ok?

What I am trying to do:
I am attempting to generate a query that I can use in a report that uses fields from these tables.

Originally I set about creating a query to relate the PRODUCT table with the DEFINITION table, so that I could be presented with (in the datasheet view) all of the PRODUCTS, regardless of whether or not they had any matching DEFINITIONS. If they did they would be presented also.
Eg

PRODUCT.PROD_SRS_ID PRODUCT.PROD_NAME DEFINITION.PROD_DEF_DESC

1 BOOBLE AAAAAA
1 BOOBLE BBBBBB
1 BOOBLE CCCCCC
2 GEORGE XXXYXX
2 GEORGE ******
3 JENNA LIKJUG

I did this with a LEFT JOIN. See below...

SELECT DISTINCTROW PRODUCT.PROD_SRS_ID, DEFINITION.PROD_SRS_ID, DEFINITION.PROD_DEF_DESC

FROM PRODUCT LEFT JOIN DEFINITION ON PRODUCT.PROD_SRS_ID = DEFINITION.PROD_SRS_ID;

Now, my problem is creating a query that allows me to also do the same with the results from the REFERENCE table aswell as the DEFINITION table. The problem that I am presented with is as follows, (multiple occurances of unwanted data (DEFINITION details) ...

(TABLE AND FIELD NAMES ABBREVIATED TO FIT ON PAGE)

PRODUCT._ID PRODUCT.NAME DEFINITION.DEF_DESC REFERENCE.REF_DESC

1 BOOBLE AAAAAA CRAIG <---
1 BOOBLE AAAAAA JAMES <---

1 BOOBLE BBBBBB
1 BOOBLE CCCCCC

2 GEORGE XXXYXX TOM <----
2 GEORGE XXXYXX FRANK <----

2 GEORGE ******
3 JENNA LIKJUG

As can be seen by the above table,
There are 2 instances of the first line that are identical except for the last value (REFERENCE.REF_DESC).

What I would like to achive is something like this...

1 BOOBLE AAAAAA CRAIG <---
1 JAMES <---

As can be seen on the second line the duplicate DEFINITION details have been removed. They are only presented once.

Please can someone offer some urgently needed assistance on how to remove this duplicate instance, so that when a report is created it will appear to have a PRODUCT as the top grouping level and the DEFINITION AND REFERENCE as the second.

Thus, looking like..

PRODUCT : 1

REFERENCES:
BOOBLE
GEORGE
JENNA
DEFINITIONS:

CRAIG
JAMES
TOM
FRANK

PRODUCT : 2

REFERENCES : etc, etc.

DEFINITIONS: etc, etc.

I really hope that this example made sence. Thanks all the same.

Craig.

[[deleted]]

The thing to note here is that you are not doing a join between PRODUCTS and DEFINITIONSand between PRODUCTS and REFERENCE.

if you used LEFT JOIN twice, then you are joining he PRODUCTS table with the DEFINITIONS table, and using the output of that join as the left table in the second JOIN with REFERENCES.

So, your first JOIN between PRODUCT and REFERENCE returns

1 BOOBLE AAAAAA

This data will be used to as the LEFT table in the second LEFT JOIN.

If there are two matches between this data and the DEFINITIONS table, you will get two records:

1 BOOBLE AAAAAA CRAIG <---
1 BOOBLE AAAAAA JAMES <---

You cannot remove duplicates here, because there simply are no duplicates.

You query was built to return exactly this, unique combinations of data from three tables.

[Anon]

Thanks Vincent,

I don't suppose you know how to query the database for the output that I am after?

Kind regards,

Craig.

[[deleted]]

Hmmm...

the trick is that you want to display the data in blocks, but a single query will always return just a set of records.

You can do two things.
a) use loops.
Do one query to fetch all product data, and loop through that.
For each product, run a second query to get the references and print them, then run another query to get the definitions and print them.
This will take a sh*tload of queries.

b) run one large LEFT JOIN query like the one you described, and loop throug it.
Tricky bit: all records contain all information about which product, which reference and which definition.
You'll have to somehow filter that data and process if into arrays that you can print.
This is 'easy' for the products, just look at the product, compare it to what the last product was, and if the new product is different, you print a new 'product' line.
But for the other items it's much more complex....

It's up to you: run many simple queries or suffer a few days of headaches trying to un-join data.

[Anon]

Thankyou very much.