Multiple Entries in Query Results.....

[ukgthor]

I am joining two separate tables that I have downloaded from our host system into a query and linking them via a common denominator (Product Code). Every Product Code has three unique entries, one for Brand A, one for Brand B and one for Group (Brand A & B) however the Group entry is a Null Field.

When I join these tables in the query (using Product Code) and enter a condition of Is Null (to give me only Group results), I get three times the number of results from every Product Code.

Anyone have any ideas why this would be ?

All help appreciated and let me know if you need any more details.

EDIT:
Total Records in initial Table is 256,734.

 

[dkinley]

Hello,

I would hazard to guess that because you use a Null and then attempting to create a join on a Null that you should have more than 3 times as many returns from the query.

A Null is a non-specification of value thus the first pass of the query will probably group all of Table 2's Null records to Table 1's first record. The second pass would group all of Table 2's Null records to Table 1's second record and so on and so forth.

You could do a search on 'dealing with Null fields' which return alot of information you can sift through. The first bit is not to allow Null fields into a dataset.

Is there anyway you can normalise the data a bit more? Perhaps updating a '1' for Product A, '2' for Product B and a '3' for Product A & B. This may alleviate the symptoms you are experiencing.

Another way to potentially reduce the return (unsure about guarenteeing integrity) is to create a subquery for one to create a dataset for the query you are attempting. I've had success in the past approaching this way.

Not alot, just some ideas.

-dK

 

[ukgthor]

dK,

Thanks for the prompt response. I have tried to run the same query this time linking on Brand A and it returns the correct number of fields, so the Null field is my issue.

Therefore my next question is how can I replace all of the Null Fields with the text "Group" ?

Thanks again.

 

[dkinley]

Use an Update Query.

If you create a new query and 'show' one of the tables of interest, populate the QBE with the field you want to update ([Product Code]).

Depending on the version of Access the location to change the query type is different; however, you have to change the query type from Select to Update. A new row, 'Update To', will show in the QBE.

Insert "Group" (with quotations) in the Update To row in the same column as the field. In the criteria use Is Null.

I normally add some other fields and test the criteria before altering the query from a Select to an Update to ensure I am changing the fields I want.

Good luck!

-dK

 

[vbaInet]

dK,
Therefore my next question is how can I replace all of the Null Fields with the text "Group" ?

Nz([FieldName],"Group")

or

IIF(IsNull([FieldName]), "Group", [FieldName])

 

[dkinley]

You could probably use the Nz method but I wouldn't suggest it because you would require two subqueries to feed into the query you want. I am not that trusting of it enough to attempt to link two datasets together.

-dK

 

[ukgthor]

dkinley / vbaInet,

Thanks for your responses, however after coming back in fresh this morning, I am still getting duplicate rows in my queries, even after I have replaced the Null fields.

I've attached a sample copy of my two tables in an Excel format. Each table has 37,611 records and as soon as I link these tables via Product Code in a query, it gives me 112,833 results.

Any further help would be appreciated.

EDIT: The Excel filesize was too big to upload, therefore I have now modified both tables to have 9,999 records each and now I get 29,997 results.

 

[vbaInet]

For your duplicate values, edit your sql and include the DISTINCTROW keyword after the SELECT keyword. That is:

SELECT DISTINCTROW .......

 

[ukgthor]

vbaInet,

Thanks again for your input. As you can guess I am not an advanced user of MS Access and as a consequence I do not have any SQL included within my query, is there any other way I can use this command without creating an additional SQL statement.

Apologies if I have misunderstood your response.

 

[SOS]

vbaInet,

Thanks again for your input. As you can guess I am not an advanced user of MS Access and as a consequence I do not have any SQL included within my query, is there any other way I can use this command without creating an additional SQL statement.

Apologies if I have misunderstood your response.

ukgthor:

A query IS SQL. But you are viewing it in the QBE (Query by Example) grid. Go up to VIEW on the menu and select SQL View and you'll see what they were talking about.

 

[ukgthor]

Okay, I have opened the SQL view and entered the DISTINCTROW field as requested and I still have 37,611 results returned.

SELECT DISTINCTROW [Table1].[Product Code], [Table1].TotalUni, [Table2].TotalVal, [Table1].Brand
FROM [Table1] INNER JOIN [Table2] ON [Table1].[Product Code] = [Table2].[Product Code]
WHERE ((([Table1].Brand)="Group"));

 

[wilpeter]

I just copied your SQL into a new query. Uploaded both your XL tables and imported them to Access 2002. The query produces 9999 records as predicted. That is, 3 times the expected records. If you add another field, Table2.Brand and set the criteria to "Group" it works.

 

[ukgthor]

I'm actually using Access 2007, so I'm unsure if this makes a difference. I have run the same query (see attached screen shots) and still get 37,611 results.

 

[wilpeter]

The version doesn't have a bearing, it's that you are not stating in the query that you only want the records from Table2 that match "group". Put "Brand" field from Table2 also in the query with that criteria.

 

[ukgthor]

wilpeter,

I have followed your advice and tried the following:

1. I removed Table1 Brand and Replace with Table2 Brand with the "Group" criteria and I still get 37,611 records (see image 3/4)

2. I then included Table2 Brand alongside Table1 Brand (both with the "Group" criteria and I get 12,537 records (see images 5/6) which is also incorrect.

 

[SOS]

You need to link to BRAND as well as Product Code.

 

[wilpeter]

I'm curious, SOS, why or even how you intended that to happen? With the sampling sent yesterday, the result was that one out of every three records was "Group" and it seems that the results obtained do filter out two thirds. My question would be: what number is the expected number of records that match?

 

[SOS]

The number should be 3333 since there is a Group for every product code and each table is essentially the same. Now if it were not the case then we could see a difference and then also it might be that we were wanting to have an outer join so that we get all matches from Table 1 and only those that matched in Table 2, and in that case we would still get 3333 records because we would be after all in Table 1 that matched "Group" but and those that matched in table 2 but not that there would have to be a record in Table 2.

But in this case the determination was just that we wanted to link the two tables together and find out how many records it returned. And because we weren't linking on a primary key, we had to link on all applicable fields (the shared ones which would be between the product code and the brand). If we wanted NUMBERS which matched then we would also link the TotalUni and TotalVal fields and that would yield 3330 records.

 

[ukgthor]

SOS / Wilpeter,

I have now joined Product Code and Brand, and as you can see from the attachments I now get a total of 12,537 results (when I should expect 9,999).

I posted both tables in a previous entry on this thread, can someone else import these into a database and tell me if you get the same issue.

Thanks in advance.

 

[vbaInet]

I think this could be a matter of changing the DISTINCTROW keyword to just DISTINCT. They behave differently.