Concatenating the same field from multiple records

D

DawnTreader

Hello All

another wierd query question. i have a union query where i get some
duplicated part numbers. there is a field that i would like to show the
actual data for, but i would like to show the quantities as a sum. let say i
have 2 lines where the part number is 321123 and a qty of 5 and the other
line is a qty of 3, for a total of 8. but i also have a field called Ref
which has 110 and 320 respectively in each of the 2 records returned for part
number 321123.

is there a way to get both Ref numbers and sum the qty at the same time? can
you concatenate across records in the same field?

i suspect that i am asking too much of access, but i am just curious. here
is my sql that produces the multiple record results:

SELECT
utblRebuildPartList.ProdID,
utblRebuildPartList.IMWPNID,
sum(utblRebuildPartList.PartBlockQTY) AS PBQTY,
utblRebuildPartList.Ref
FROM
utblRebuildPartList
GROUP BY ProdID, IMWPNID, Ref
UNION SELECT
tblProductList.ProductID AS ProdID,
tblCBAPartList.IMWPartNumberID AS IMWPNID,
tblCBAPartList.QTY AS PBQTY,
"CBA" AS Ref
FROM
((tblCBBANumbers RIGHT JOIN (tblProductList LEFT JOIN subdatatblCompressor
ON (tblProductList.ProductTypeID = subdatatblCompressor.ProductTypeID)
AND (tblProductList.ProductID = subdatatblCompressor.ProductID))
ON tblCBBANumbers.CBBAID = subdatatblCompressor.CBAEMID)
LEFT JOIN tblCBAPartList ON tblCBBANumbers.CBBAID = tblCBAPartList.CBAID)
LEFT JOIN utblRebuildPartList ON tblCBAPartList.IMWPartNumberID =
utblRebuildPartList.IMWPNID
WHERE
(((tblProductList.ProductID)=669) AND ((tblCBAPartList.IMWPartNumberID)
Is Not Null) AND ((utblRebuildPartList.IMWPNID) Is Null))
ORDER BY IMWPNID;

the reason i would like to be able to do this concatenating is so that i can
still know the areas of the product where those parts go, hence the Ref
field. it Refers to a number on a drawing.
 
K

Ken Snell \(MVP\)

Let's assume that your UNION query is named qryUNION.

This query should give you the results that you seek:

SELECT DISTINCT qryUNION.PRODID, qryUNION.Ref,
(SELECT Sum(T.PBQTY) AS TS
FROM qryUNION T
WHERE T.PRODID = qryUNION.PRODID) AS SumQty
FROM qryUNION;
 
D

DawnTreader

Hello Ken

thanks for your help, i will probably have to implement this at some point,
but for the moment i have another question.

here is my SQL so far:

INSERT INTO
utblRebuildPartListAll ( ProdID, Category, IMWPNID, Ref, PartBlockQTY,
PLID )
SELECT
subdatatblCompressor.ProductID AS ProdID,
"CBA" AS Category,
tblCBAPartList.IMWPartNumberID AS IMWPNID,
"CBA" AS Ref,
tblCBAPartList.QTY AS PartBlockQTY,
tblCBAPartList.CBAPartListID AS PLID
FROM
(tblCBBANumbers RIGHT JOIN subdatatblCompressor ON tblCBBANumbers.CBBAID
= subdatatblCompressor.CBAEMID)
LEFT JOIN tblCBAPartList ON tblCBBANumbers.CBBAID = tblCBAPartList.CBAID
WHERE NOT EXISTS
tblCBAPartList.IMWPartNumberID AND (subdatatblCompressor.ProductID=669);

how do i add the condition that the "tblCBAPartList.IMWPartNumberID AS
IMWPNID" cannot already exist.

for example if part number 321123 is already in the table how do i get this
query to not insert that number again from the tblCBAPartList?
 
D

DawnTreader

And one more thing i need to know...

if i was to take the SQL for the append query and use it in VBA code, would
it be better to execute the query, or can i use docmd.sql " my sql here" and
get the same results.

i have never done a recordset insert through VBA code before and i am not
sure if it would do one resulting record or if it would automattically find
the first qualifying record insert it and then move to the next. i have
inserted one record at the push of a button, and that is easy, but i am not
sure if a coded append works the same way
for multiple records as it does for one record.

any tips or tricks you can suggest?
 
D

DawnTreader

I have been playing with the SQL and trying to figure it out, but i dont
think this is it:

SELECT
subdatatblCompressor.ProductID AS AProdID,
"CBA" AS ACategory,
tblCBAPartList.IMWPartNumberID AS AIMWPNID,
"CBA" AS ARef,
tblCBAPartList.QTY AS APartBlockQTY,
tblCBAPartList.CBAPartListID AS APLID
FROM
(tblCBBANumbers RIGHT JOIN subdatatblCompressor ON tblCBBANumbers.CBBAID
= subdatatblCompressor.CBAEMID) LEFT JOIN tblCBAPartList ON
tblCBBANumbers.CBBAID = tblCBAPartList.CBAID
WHERE NOT EXISTS
(SELECT utblRebuildPartListAll.IMWPNID
FROM utblRebuildPartListAll
WHERE utblRebuildPartListAll.IMWPNID=tblCBAPartList.IMWPartNumberID AND
utblRebuildPartListAll.ProdID=669)
AND ((subdatatblCompressor.ProductID)=669);

i know that this sql is giving me 21 records to many. i have checked it
against a manual count. :(

any suggestions?
 
K

Ken Snell \(MVP\)

What you will need is a separate, saved query that returns all records from
tblCBAPartList EXCEPT where tblCBAPartList.IMWPartNumberID = 321123
(assuming that this field is a number data type):

SELECT * FROM tblCBAPartlList
WHERE IMWPartNumberID <> 321123;


Assuming that the above query is named qryCBAPartList_Not321123, then your
insert query would look like this:

INSERT INTO
utblRebuildPartListAll ( ProdID, Category, IMWPNID, Ref, PartBlockQTY,
PLID )
SELECT
subdatatblCompressor.ProductID AS ProdID,
"CBA" AS Category,
tblCBAPartList.IMWPartNumberID AS IMWPNID,
"CBA" AS Ref,
tblCBAPartList.QTY AS PartBlockQTY,
tblCBAPartList.CBAPartListID AS PLID
FROM
(tblCBBANumbers RIGHT JOIN subdatatblCompressor ON tblCBBANumbers.CBBAID
= subdatatblCompressor.CBAEMID)
LEFT JOIN qryCBAPartList_Not321123 ON tblCBBANumbers.CBBAID =
qryCBAPartList_Not321123.CBAID
WHERE
subdatatblCompressor.ProductID<>669;


--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/
 
K

Ken Snell \(MVP\)

Sorry, I just realized that I didn't give you the right answer for this.

The insert query would be this (I've left in your final WHERE condition
statement relating to subdatatblCompressor.ProductID=669 although I don't
know why you need or want it:

INSERT INTO
utblRebuildPartListAll ( ProdID, Category, IMWPNID, Ref, PartBlockQTY,
PLID )
SELECT
subdatatblCompressor.ProductID AS ProdID,
"CBA" AS Category,
tblCBAPartList.IMWPartNumberID AS IMWPNID,
"CBA" AS Ref,
tblCBAPartList.QTY AS PartBlockQTY,
tblCBAPartList.CBAPartListID AS PLID
FROM
((tblCBBANumbers RIGHT JOIN subdatatblCompressor ON tblCBBANumbers.CBBAID
= subdatatblCompressor.CBAEMID)
LEFT JOIN tblCBAPartList ON tblCBBANumbers.CBBAID = tblCBAPartList.CBAID)
LEFT JOIN utblRebuildPartListAll ON tblCBAPartList.IMWPartNumberID =
utblRebuildPartListAll.IMWPNID
WHERE NOT EXISTS
utblRebuildPartListAll.IMWPNID IS NULL AND
(subdatatblCompressor.ProductID=669);


--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/
 
K

Ken Snell \(MVP\)

It's better to use the Execute method of CurrentDb for executing queries
because you can have it fail on an error in VBA code:

Dim dbs As DAO.Database
Set dbs = CurrentDb
dbs.Execute "YourSQL", dbFailOnError
If Err.Number <> 0 Then
MsgBox "Error " & Err.Number & " occurred: " & _
Err.Description
Err.Clear
End If
dbs.Close
Set dbs = Nothing
 
D

DawnTreader

hello Ken

i had the utblRebuildPartListAll.IMWPNID IS NULL because i was trying to get
just the parts that didnt already exist in the table. the
subdatatblCompressor.ProductID=669 is to get the one unit that i am testing
the query with.

when i am done the subdatatblCompressor.ProductID=669 will use a form field
in place of the 669.

so based on your suggestions, my sql will look like this:

INSERT INTO
utblRebuildPartListAll ( ProdID, Category, IMWPNID, Ref,
PartBlockQTY,PLID )
SELECT
subdatatblCompressor.ProductID AS ProdID,
"CBA" AS Category,
tblCBAPartList.IMWPartNumberID AS IMWPNID,
"CBA" AS Ref,
tblCBAPartList.QTY AS PartBlockQTY,
tblCBAPartList.CBAPartListID AS PLID
FROM
((tblCBBANumbers RIGHT JOIN subdatatblCompressor ON
tblCBBANumbers.CBBAID= subdatatblCompressor.CBAEMID)
LEFT JOIN tblCBAPartList ON tblCBBANumbers.CBBAID = tblCBAPartList.CBAID)
LEFT JOIN utblRebuildPartListAll ON tblCBAPartList.IMWPartNumberID =
utblRebuildPartListAll.IMWPNID
WHERE NOT EXISTS
utblRebuildPartListAll.IMWPNID IS NULL AND
(subdatatblCompressor.ProductID=forms!zzMAINFORM.form!subProductID);

now i dont know, do i need "utblRebuildPartListAll.IMWPNID IS NULL" ? or
should i just put "utblRebuildPartListAll.IMWPNID" because we are already
telling it to look for those that do not exist?

understand the only field that will be the same is the IMWPNID. all the
other fields for a record in this situation will be different to some degree,
the IMWPNID is the part number identifier. the category, ref, qty and PLID
will all be different between the 3 data sets that i am pulling in, but the
tblCBAPartList are known duplicates if the other 2 sets have the part number
listed already.

i am going to implement this and see what happens in my test enviroment. i
will post back later.

thanks for your help! :)
 
K

Ken Snell \(MVP\)

No, do not use WHERE NOT EXISTS..... copy/paste error on my part. Here is
corrected query:

INSERT INTO
utblRebuildPartListAll ( ProdID, Category, IMWPNID, Ref, PartBlockQTY,
PLID )
SELECT
subdatatblCompressor.ProductID AS ProdID,
"CBA" AS Category,
tblCBAPartList.IMWPartNumberID AS IMWPNID,
"CBA" AS Ref,
tblCBAPartList.QTY AS PartBlockQTY,
tblCBAPartList.CBAPartListID AS PLID
FROM
((tblCBBANumbers RIGHT JOIN subdatatblCompressor ON tblCBBANumbers.CBBAID
= subdatatblCompressor.CBAEMID)
LEFT JOIN tblCBAPartList ON tblCBBANumbers.CBBAID = tblCBAPartList.CBAID)
LEFT JOIN utblRebuildPartListAll ON tblCBAPartList.IMWPartNumberID =
utblRebuildPartListAll.IMWPNID
WHERE
utblRebuildPartListAll.IMWPNID IS NULL AND
(subdatatblCompressor.ProductID=669);

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/
 
D

DawnTreader

Hello Ken

yeah, i spent some time playing with it and figured it out.

now i have another question, can i do this:

INSERT INTO
utblRebuildPartListAllIntervalsGrouped (ProdID, Category, IMWPNID, Ref,
PartBlockQTY, PLID, PartDesc, ListPrice, Interval)
SELECT
utblRebuildPartListAllIntervalsAdded.ProdID,
utblRebuildPartListAllIntervalsAdded.Category,
utblRebuildPartListAllIntervalsAdded.IMWPNID,
utblRebuildPartListAllIntervalsAdded.Ref,
utblRebuildPartListAllIntervalsAdded.PartBlockQTY,
utblRebuildPartListAllIntervalsAdded.PLID,
utblRebuildPartListAllIntervalsAdded.PartDesc,
utblRebuildPartListAllIntervalsAdded.ListPrice,
"1 1000 Hr Maintenance" AS Interval
FROM
utblRebuildPartListAllIntervalsAdded
WHERE
(((utblRebuildPartListAllIntervalsAdded.rOneThouHrs)=True))
UNION SELECT
utblRebuildPartListAllIntervalsAdded.ProdID,
utblRebuildPartListAllIntervalsAdded.Category,
utblRebuildPartListAllIntervalsAdded.IMWPNID,
utblRebuildPartListAllIntervalsAdded.Ref,
utblRebuildPartListAllIntervalsAdded.PartBlockQTY,
utblRebuildPartListAllIntervalsAdded.PLID,
utblRebuildPartListAllIntervalsAdded.PartDesc,
utblRebuildPartListAllIntervalsAdded.ListPrice,
"2 5000 Hr Maintenance" AS Interval
FROM
utblRebuildPartListAllIntervalsAdded
WHERE
(((utblRebuildPartListAllIntervalsAdded.rFiveThouHrs)=True));

i mean can i insert with a union data set? there are actually 6 "groupings"
that i want to be inserting all at the same time, if necessary i will do each
group seperately, but i thought maybe i could get away with it in a union.
 
K

Ken Snell \(MVP\)

Yes, you can use a UNION query as the data set for an append query. You may
want to save the UNION query as a separate query so that the append query is
a little less complicated in structure, but it should work.
 
D

DawnTreader

Hello again

so if i use your code, what is the most efficient way of doing 10 different
sql operations?

Dim dbs As DAO.Database
dim utblSQLPartInsert as String

Set dbs = CurrentDb

utblSQLPartInsert = "myfirststepsqlhere"

dbs.Execute utblSQLPartInsert, dbFailOnError

If Err.Number <> 0 Then
MsgBox "Error " & Err.Number & " occurred: " & _
Err.Description
Err.Clear
End If

utblSQLPartInsert = "mynextstepsqlhere"

dbs.Execute utblSQLPartInsert, dbFailOnError

If Err.Number <> 0 Then
MsgBox "Error " & Err.Number & " occurred: " & _
Err.Description
Err.Clear
End If

.... etc for 8 more steps

dbs.Close

Set dbs = Nothing

each of the sql steps is different so i would need to set the variable each
time, but i am thinking there is a more efficient way that you could point me
to.

i am basically building each step i need to get the results i have been
working on for over 1.5 months. it is a complicated set of steps and as i
have posted in other threads i cant get it to work in one set of queries
stacked because i hit the "cannot open more databases" and "system resources
exceeded" errors. i get within 4 steps of being finished and "boooinng" my
stack falls apart. so i figured if i build each step into a temp table and
then make my report based on 2 of the steps i will get my report finished.
finally.

currently i simply created a button that has each sql step in proper order
to do the function that i need, but if i can make the code more efficient
then i figured it would be worth it. it is working, but i would rather make
it more efficient now while i have
the time.

any and all help appreciated. :)
 
D

David W. Fenton

It's better to use the Execute method of CurrentDb for executing
queries because you can have it fail on an error in VBA code:

Dim dbs As DAO.Database
Set dbs = CurrentDb
dbs.Execute "YourSQL", dbFailOnError
If Err.Number <> 0 Then
MsgBox "Error " & Err.Number & " occurred: " & _
Err.Description
Err.Clear
End If
dbs.Close
Set dbs = Nothing
Click to expand...

Uh, you should never close a database you didn't open, and since you
initialized your database variable with CurrentDB, which you didn't
open, you shouldn't close it. It actually causes no error to attempt
to close it (as it can with a variable initialized with
DBEngine(0)(0)), but I think it's important to never write a line of
code that you know won't actually do anything. Always including it
could also lead you to use it with DBEngine(0)(0) sometime,
potentially with disastrous results.

The only time I close a database is if I opened it with
DBEngine.OpenDatabase(), which means it's *not* the db currently
open in the Access UI.
 
D

DawnTreader

Hello David

thanks for the tip.

David W. Fenton said:
Uh, you should never close a database you didn't open, and since you
initialized your database variable with CurrentDB, which you didn't
open, you shouldn't close it. It actually causes no error to attempt
to close it (as it can with a variable initialized with
DBEngine(0)(0)), but I think it's important to never write a line of
code that you know won't actually do anything. Always including it
could also lead you to use it with DBEngine(0)(0) sometime,
potentially with disastrous results.

The only time I close a database is if I opened it with
DBEngine.OpenDatabase(), which means it's *not* the db currently
open in the Access UI.
Click to expand...
 
K

Ken Snell \(MVP\)

What's the "difference" for the SQL statement or actions being done for each
of the 10 times?
 
D

DawnTreader

Hello Again Ken

I know it has been a while, but i am looking at a situation that i thought
this conversation had covered, but i cant seem to find the answer in our talk.

all that to say that i need to take this:

COID WOID
54621 04858
54621 04859
55106 10000
55106 10388
55384 10007
55384 10289
55384 16470
60000 10011
60000 10013
60000 10059

and turn it into this:

COID WOIDs
54621 4858, 4859
55106 10000, 10388
55384 10007, 10289, 16470
60000 10011, 10013, 10059

my sql at the moment:

SELECT
dbo_CUST_ORDER_LINE.CUST_ORDER_ID AS COID,
dbo_DEMAND_SUPPLY_LINK.SUPPLY_BASE_ID AS WOID
FROM
((dbo_CUST_ORDER_LINE
LEFT JOIN dbo_CUST_LINE_BINARY
ON (dbo_CUST_ORDER_LINE.LINE_NO = dbo_CUST_LINE_BINARY.CUST_ORDER_LINE_NO)
AND (dbo_CUST_ORDER_LINE.CUST_ORDER_ID =
dbo_CUST_LINE_BINARY.CUST_ORDER_ID))
LEFT JOIN dbo_DEMAND_SUPPLY_LINK
ON (dbo_CUST_ORDER_LINE.LINE_NO = dbo_DEMAND_SUPPLY_LINK.DEMAND_SEQ_NO)
AND (dbo_CUST_ORDER_LINE.CUST_ORDER_ID =
dbo_DEMAND_SUPPLY_LINK.DEMAND_BASE_ID))
LEFT JOIN dbo_WORK_ORDER
ON (dbo_DEMAND_SUPPLY_LINK.SUPPLY_SUB_ID = dbo_WORK_ORDER.SUB_ID)
AND (dbo_DEMAND_SUPPLY_LINK.SUPPLY_SPLIT_ID = dbo_WORK_ORDER.SPLIT_ID)
AND (dbo_DEMAND_SUPPLY_LINK.SUPPLY_LOT_ID = dbo_WORK_ORDER.LOT_ID)
AND (dbo_DEMAND_SUPPLY_LINK.SUPPLY_BASE_ID = dbo_WORK_ORDER.BASE_ID)
WHERE
(((dbo_DEMAND_SUPPLY_LINK.SUPPLY_BASE_ID) Is Not Null))
GROUP BY
dbo_CUST_ORDER_LINE.CUST_ORDER_ID,
dbo_DEMAND_SUPPLY_LINK.SUPPLY_BASE_ID;

any and all help appreciated. :)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How to use WHERE NOT EXISTS 2
Question about Queries with Subqueries 1
combining queries into 1 8
3 queries into 1 8
How to fix the one exception to the rule. 0
Changing value to 0 if condition exists in another field of record 6
Change value of field to 0 if specific condition exists on other f 6
records for the year 3

Top