Help dealing with duplicate query results

[Sephiroth0327]

Hi All,

Ok, my database is very simple. I am dealing with three tables:

• Projects (contains a list of project numbers)
• dbo_contract_project (Table which contains basic contract information along with associated project number)
• ACT_Contract_Private (Table which contains additional contract information, most notably the BarCode field)

My query should show all records in the Projects table along with corresponding BarCode field associated with those projects (The contract info I want is in the ACT_Contract_Private table. The dbo_contract_project table is only there because it links the two tables together).

The problem is that there may be multiple contracts associated with a project. Let's say my Projects table contains project "123456" and that project number is associated with 3 contracts. My resulting query displays three rows for 123456 when I really want one row with all three BarCode values in the BarCode field?

Here's what I am getting

Project # BarCode
123456 ABC 1234
123456 DEF 1234
123456 XYZ 1234

When what I want to see is

Project # BarCode
123456 ABC 1234; DEF 1234; XYZ 1234

Here is the SQL of my query

SELECT Projects.ProjectNum, ACT_Contract_Private.BarCodeList AS BarCode

FROM (Projects LEFT JOIN dbo_contract_project ON Projects.ProjectNum = dbo_contract_project.project_key) LEFT JOIN ACT_Contract_Private ON dbo_contract_project.contract_id = ACT_Contract_Private.ContractID;

Any ideas?

 

[pbaldy]

This type of thing?

http://www.mvps.org/access/modules/mdl0004.htm

 

[Sephiroth0327]

pbaldy,

Thanks for the response. I have reviewed the code on the link you provided and I am a bit confused. I see that I should be using the function fConcatChild and I see the short SQL statement, but I have no idea what to do with the code that is in the box below the SQL statement. I assume I need to enter this code somewhere so I can define the function, but I am not familiar with defining functions.

Can you point me in the right direction?

Also, regarding the SQL statement itself, what value should I be entering her for my situation (in the example, this reference is titled [OrderID] but would correspond to a ProjectNum value in my situation). I have a 1:M relationship where the ProjectNum field is the primary key in the Projects table but could potentially be listed multiple times in the ACT_Contract_Private table. I want the BarcodeList field concatenated when multiple barcodes are returned.

 

[pbaldy]

You would copy/paste the function (everything from "Function fConcatChild..." to "End Function") into a standard module. That makes it accessible from anywhere in the application.

 

[Sephiroth0327]

Ok, I copied/pasted the code into a new module. When I run the query, it is not running as I would expect it. I imagine that I must have updated the SQL statement incorrectly. Here is my SQL - I'm at a loss for what I am missing here. I'd appreciate any help anyone can provide. Thanks!

SELECT Projects.*, ACT_Contract_Private.BarCodeList, ACT_Contract_Private.ContractType, fConcatChild("Projects","Projects.[Project Number]","ACT_Contract_Private.BarCodeList","Text",[[B]Not sure what to put in here[/B]]) AS ["SubFormValuesFROM Projects"]

FROM (Projects LEFT JOIN dbo_contract_project ON Projects.[Project Number] = dbo_contract_project.project_key) LEFT JOIN ACT_Contract_Private ON dbo_contract_project.contract_id = ACT_Contract_Private.ContractID

WHERE (((ACT_Contract_Private.ContractType)="SCO" Or (ACT_Contract_Private.ContractType)="SCD" Or (ACT_Contract_Private.ContractType)="CSA" Or (ACT_Contract_Private.ContractType)="SUL" Or (ACT_Contract_Private.ContractType)="SLA" Or (ACT_Contract_Private.ContractType) Is Null))

ORDER BY Projects.ID;

 

[pbaldy]

Try

fConcatChild("Projects","[Project Number]","BarCodeList","Text", [Project Number]) AS SubFormValuesFROMProjects

 

[Sephiroth0327]

I have updated the query, as you indicated above, and it is running without any errors, but the "SubFormValuesFROMProjects" field is blank for all records. The resulting data, actually, is exactly the same as I was getting when I ran the query without the fConcatChild function, with the exception of the additional field.

For each project on the Projects table, I want to see all corresponding values for the BarCodeList field on the ACT_Contract_Private table which are associated with the project in a concatenated string.

Not sure if this is affecting the query, but I am not comparing the Projects table directly to the ACT_Contract_Private table since I cannot do a direct join. I am first doing a LEFT JOIN to another table which has another LEFT JOIN to the ACT_Contract_Private table.

I really appreciate all of your help so far. Let me know if see anything else that I might be missing here. For your reference, I am posting my updated SQL:

[B][SIZE=3]//My SELECT statement containing the fConcatChild function[/SIZE][/B]
 
SELECT Projects.*, ACT_Contract_Private.BarCodeList,
ACT_Contract_Private.ContractType, fConcatChild("Projects","[Project Number]"
,"BarCodeList","text", [Project Number]) AS SubFormValuesFROMProjects
 
[B][SIZE=3]//As I referenced above, I am working with three tables [/SIZE][/B]
[B][SIZE=3]and two [/SIZE][/B][B][SIZE=3]LEFT JOINS.  This is because I cannot directly link [/SIZE][/B]
[B][SIZE=3]the Projects table [/SIZE][/B][B][SIZE=3]with the ACT_Contract_Private table[/SIZE][/B]
 
FROM (Projects LEFT JOIN dbo_contract_project 
ON Projects.[Project Number] = dbo_contract_project.project_key) 
LEFT JOIN ACT_Contract_Private 
ON dbo_contract_project.contract_id = ACT_Contract_Private.ContractID
 
[B][SIZE=3]//This is just a filter that I applied to ensure that [/SIZE][/B]
[B][SIZE=3]the results [/SIZE][/B][B][SIZE=3]only correspond to specific contract types[/SIZE][/B]
 
WHERE (((ACT_Contract_Private.ContractType)="SCO" 
Or (ACT_Contract_Private.ContractType)="SCD" 
Or (ACT_Contract_Private.ContractType)="CSA" 
Or (ACT_Contract_Private.ContractType)="SUL" 
Or (ACT_Contract_Private.ContractType)="SLA" 
Or (ACT_Contract_Private.ContractType) Is Null))
ORDER BY Projects.ID;

 

[pbaldy]

Can you post the db?

 

[Sephiroth0327]

Here you go. Thanks again.

 

[pbaldy]

I realize this is sample data, but I'm confused. What do you want to see for project 111111? I guess what confuses me is that a project can be connected to multiple contract_id's. Your query repeats the project numbers because of the multiple contract_id's, but from your description I though you wanted one instance of each project. It would help if you would list what the function should return based on the sample data.

 

[Sephiroth0327]

Here is what I want to see:

Project BarcodeList
111111 ABC 1111; ABC 2222; ABC 3333
222222 ABC 1111
333333 ABC 4444, ABC 5555; ABC 6666
444444 Null
555555 Null
666666 ABC 1111; ABC 2222
777777 Null
888888 Null
999999 Null
000000 ABC 2222

When the existing query is run, I get three rows for 111111 each with a separate barcode. I only want one row per project # with the barcode values shown in a string separated by semi-colons.

Clear as mud?

 

[pbaldy]

Okay, first create a new query that will serve as a base:

SELECT dbo_contract_project.project_key, ACT_Contract_Private.BarCodeList
FROM dbo_contract_project INNER JOIN ACT_Contract_Private ON dbo_contract_project.contract_id = ACT_Contract_Private.ContractID

For chuckles, I called it "qryPaul". Then run this query:

SELECT Projects.[Project Number], fConcatChild("qryPaul","Project_key","BarCodeList","String",[project number]) AS Barcodes
FROM Projects

That returns the above, plus "ABC 7777" which I don't think you accounted for. Either that or I'm all goofed up again.

 

[Sephiroth0327]

You, sir, are a genius. This is perfect. Thanks!!

 

[pbaldy]

Well, the genius tag is definitely misplaced, but I'm glad it worked for you.