Concatenation of a query Need Help

[enu1978]

I'm having trouble with cancatenation with two tables. Ok I have two tables 1 table is called backorders the other is called replacement table. When i join the tables by Item No and I query the tables my output is what you see below it duplicates because REPL_ITEM can have two items in the field for the same Order NO/Item No. I want to concatenate so that all repl_item is on one row for order no/item no. Now the item no is primary key it's just in the query below i chose item 40191700 as the specific item but it can be any item number. Anyways how do i cantenate this into one row. I've been stuck on this for a week, everything i've done doesn't make sense. If i can get any assistance i will really appreciate it. thanks

 

[John Big Booty]

Firstly welcome to the forum.

Either of these links should point you in the correct direction.

 

[enu1978]

Thank you i will look through the links and if i have questions i hope you're around to answer me. thanks agaiin for welcoming me.

 

[enu1978]

ok John i hope youre still there so I was looking at the blue claw website and i found a code that i think might work for me it's the first code relating to the patient ID. I modeled my code exactly to theirs but i'm missing something. I'm very new to this coding thing so i'm hoping someone can answer my questions.
1. The patient code written is what goes into the module, I got that but what is my actual SQL Query suppose to look like?

2. In the code they are inserting the contatenation in a new table i understand that. It doesn't work, is the new insertion of the results into a new table suppose to happen when i run the query?. I did create a blank table to insert the information in but again nothing happened.

Again any insight would really be great FROM ANYONE at this Point. thanks

 

[John Big Booty]

Any chance of posting a copy of your DB (pre '07 format)?

 

[enu1978]

Thanks John for responding, Now let me explain my database. The two tables are REPLACEMENT and BACKORDERS. The data in the databases are just a sample of data taking from the actual larger tables. The field ITEM_NO number criteria has been specified to that 40192700 number. In the actual larger database/query the ITEM_NO can be any of 1000's of number. The issue is when you run the Query called Backorder query you'll see that a customer's Order doubles because they are two Replacement item numbers for an Item number. I need to cancatenate the field REPL. So that it shows one record per order so REPL needs to be concatenated but i just can't get it to work. I had an code that worked a little in which REPL fields were cancatenated but it just repeated the item numbers over and over again. Any help is greatly appreciated.

 

[John Big Booty]

A couple of quick observations straight up.

Avoid using spaces and other special characters in object and control names, confine yourself to alpha numeric characters and the underscore (_).

Also consider implementing a naming protocol along the lines of; TBL_TableName, QRY_QueryName, FRM_FormName, RPT_ReportName.

 

[John Big Booty]

My real work has got a little busy so I'll come back to this when I get a chance

 

[John Big Booty]

Sorry should have mentioned I stumbled across this article that has a demo DB that my help in my absence.

 

[John Big Booty]

I've been struggling to get this to work on your DB, but here is a working example of BlueClaw's DB

 

[enu1978]

OK John,
So i tried a new method or can you say new coding because i found the other coding just to darn confusing. The new coding is simple enough and it did get my field to cocatenate but now the new field repeats over and over again . For example instead of just 40219000, 12458796 it does 40219000, 12458796,40219000, 12458796,40219000, 12458796. So can you please look at Qry_Backorder and look at the Module coding and see if you can help me figure out why the results repeats. Thanks

I've attached the database again

 

[John Big Booty]

Blue Claw's solution is reasonably straight forward. I'm just running into trouble bashing this portion of his code;

[COLOR="SeaGreen"]'  select list of records for this patient[/COLOR]

Set rst = db.OpenRecordset("SELECT Allergy " & _
" FROM L_alrgy INNER JOIN M_Patient_alrgy " & _
" ON L_alrgy.Allergy_ID = M_Patient_alrgy.Allergy_ID " & _
" WHERE Patient_ID=" & Patient_ID)

as I'm not strong on raw SQL

 

[enu1978]

Blue Claw's solution is reasonably straight forward. I'm just running into trouble bashing this portion of his code;

[COLOR=SeaGreen]'  select list of records for this patient[/COLOR]

Set rst = db.OpenRecordset("SELECT Allergy " & _
" FROM L_alrgy INNER JOIN M_Patient_alrgy " & _
" ON L_alrgy.Allergy_ID = M_Patient_alrgy.Allergy_ID " & _
" WHERE Patient_ID=" & Patient_ID)

as I'm not strong on raw SQL

you know i've been working with that selection also and i keep getting an runtime error data type mismatch. this is getting so frustrated, were you able to figure out anything John or ANYBODY!!! THANKS

 

[enu1978]

you know i've been working with that selection also and i keep getting an runtime error data type mismatch. this is getting so frustrated, were you able to figure out anything John or ANYBODY!!! THANKS

i figured it out. thanks

 

[John Big Booty]

i figured it out. thanks

Good work