Avatar of jmac001
jmac001 asked on

Multiple Concats

Is it possible to have multiple concatentations in SQL.  I have the following code that concats the CATEGORY in the temp table however I also need to see the CATEGORY in the primary table.

SELECT tmpQuery2.DivisionName, tmpQuery2.ProjectName, tmpQuery2.Type, 
tmpQuery2.tmpShipping.StoreNumber, tmpQuery2.tmpShipping.Shipment, 
Dconcat("tmpShipping.Category","tmpQuery2","[tmpShipping].[Shipment] = '" & 
[tmpShipping].[Shipment] & "' And [tmpShipping].[StoreNumber] = " & 
[tmpShipping].[StoreNumber]) AS Category, tmpQuery2.tmpShipping.Mode, 
tmpQuery2.tmpShipping.ActSailDate, tmpQuery2.tmpShipping.EstArrivalDate, 
tmpQuery2.tmpShipping.ActArrivalDate, tmpQuery2.tmpShipping.Signature, 
tmpQuery2.Shipping.StoreNumber, tmpQuery2.Shipping.Shipment, 
tmpQuery2.Shipping.Mode, tmpQuery2.Shipping.ActSailDate, 
tmpQuery2.Shipping.EstArrivalDate, tmpQuery2.Shipping.ActArrivalDate, 
tmpQuery2.QtyReceived, tmpQuery2.Shipping.Signature

FROM tmpQuery2

GROUP BY tmpQuery2.DivisionName, tmpQuery2.ProjectName, tmpQuery2.Type, 
tmpQuery2.tmpShipping.StoreNumber, tmpQuery2.tmpShipping.Shipment, 
Dconcat("tmpShipping.Category","tmpQuery2","[tmpShipping].[Shipment] = '" & 
[tmpShipping].[Shipment] & "' And [tmpShipping].[StoreNumber] = " & [tmpShipping].[StoreNumber]), 
tmpQuery2.tmpShipping.Mode, tmpQuery2.tmpShipping.ActSailDate, 
tmpQuery2.tmpShipping.EstArrivalDate, tmpQuery2.tmpShipping.ActArrivalDate, 
tmpQuery2.tmpShipping.Signature, tmpQuery2.Shipping.StoreNumber, 
tmpQuery2.Shipping.Shipment, tmpQuery2.Shipping.Mode, tmpQuery2.Shipping.ActSailDate, 
tmpQuery2.Shipping.EstArrivalDate, tmpQuery2.Shipping.ActArrivalDate, 
tmpQuery2.QtyReceived, tmpQuery2.Shipping.Signature

ORDER BY tmpQuery2.DivisionName, tmpQuery2.Type, 
tmpQuery2.tmpShipping.StoreNumber, tmpQuery2.tmpShipping.Shipment;

Open in new window



If I just add the field to the PRIMARY table it displays, however the TEMP CATEGORY data is gone.
Microsoft AccessSQL

Avatar of undefined
Last Comment
jmac001

8/22/2022 - Mon
Graham Mandeno

I would have thought there were (potentially) multiple categories for each record in your "primary" table.  Do you not have a many-to-many relationship (via a junction table) between Shipments and Categories?

Can you please post the SQL of tmpQuery2, and also give a description of your tables and how they are related.

--
Graham Mandeno [Access MVP 1996-2013]
ASKER
jmac001

Here is the SQL for tmpQuery2

SELECT Projects.DivisionName, Projects.ProjectName, IIf(IsNull([Shipping].[ID]),"New","Update") AS Type, tmpShipping.StoreNumber, tmpShipping.Shipment, tmpShipping.Category, tmpShipping.Mode, tmpShipping.ActSailDate, tmpShipping.EstArrivalDate, tmpShipping.ActArrivalDate, tmpShipping.Signature, Shipping.StoreNumber, Shipping.Shipment, Shipping.Category, Shipping.Mode, Shipping.ActSailDate, Shipping.EstArrivalDate, Shipping.ActArrivalDate, Shipping.QtyReceived, Shipping.Signature

FROM Projects INNER JOIN (tmpShipping LEFT JOIN Shipping ON tmpShipping.TrackerNo = Shipping.TrackerNo) ON Projects.StoreNumber = tmpShipping.StoreNumber

GROUP BY Projects.DivisionName, Projects.ProjectName, IIf(IsNull([Shipping].[ID]),"New","Update"), tmpShipping.StoreNumber, tmpShipping.Shipment, tmpShipping.Category, tmpShipping.Mode, tmpShipping.ActSailDate, tmpShipping.EstArrivalDate, tmpShipping.ActArrivalDate, tmpShipping.Signature, Shipping.StoreNumber, Shipping.Shipment, Shipping.Category, Shipping.Mode, Shipping.ActSailDate, Shipping.EstArrivalDate, Shipping.ActArrivalDate, Shipping.QtyReceived, Shipping.Signature, tmpShipping.ID, Shipping.ID

HAVING (((Shipping.Category)<>[tmpShipping]![Category])) OR (((Shipping.Shipment)<>[tmpShipping]![Shipment])) OR (((Shipping.Mode)<>[tmpShipping]![Mode])) OR (((Shipping.ActSailDate)<>[tmpShipping]![ActSailDate])) OR (((Shipping.EstArrivalDate)<>[tmpShipping]![EstArrivalDate])) OR (((Shipping.ActArrivalDate)<>[tmpShipping]![ActArrivalDate])) OR (((Shipping.Signature)<>[tmpShipping]![Signature])) OR (((Shipping.ID) Is Null));

Open in new window


There is no separate table for the categories would that be advisable?
Graham Mandeno

Do you mean that you are listing several categories in the one field?  That is very bad design because it makes it very difficult to select shipments in a particular category.  If one record can belong to several categories, you should have a one-to-many relationship with the record-categories in a separate table.

I don't understand what you are doing with DConcat.  I assume it is the much-published function which concatenates a field from multiple records in a secondary table which are related to the current record in the primary table.  If you do not have this structure, then what are you trying to achieve?

Finally, you have posted two GROUP BY (totals) queries which have no aggregate functions (Sum, Count, etc) in them.  There is no point in using GROUP BY if you are not aggregating data, because there are only distinct records with nothing to group.

Please give more information about your table structures and exactly what you are trying to achieve.

Best wishes,
Graham
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
ASKER
jmac001

Hi Graham,

The data lists 1 category per record. I am attaching an excel of the query results. The tmp2Query is a detail query of the data and the initial query that I posted is a summary of the data.  

The DCONCAT is concating the Category field from the tmpShipping table to achieve the summaty report that I was looking for, however when I was creating I notices that the category field from the primary table for not there when I inputted the field into the query in the results there was no data for the tmpShipping but data for the Shipping Category field.  Adding results in the excel.



My ultimate goal is to produce a report that shows what has been added as type "new" or those records  with type "update" which fields have been updated.
EE-2013.09.06.xlsx
Graham Mandeno

From my first answer:
<<...give a description of your tables and how they are related.>>

From my second answer:
<<Please give more information about your table structures and exactly what you are trying to achieve.>>

You have still not done this, which makes it very difficult to answer you question!

However, it seems that tmpShipping is a copy of Shipping which had had new records added and existing records modified.  The query tmp2Query is a selection of the records which are new or modified.  Is this correct?

For a start, you can greatly simplify tmp2Query by not making it a GROUP BY query:
SELECT Projects.DivisionName, Projects.ProjectName, IIf(IsNull([Shipping].[ID]),"New","Update") AS Type, 
    tmpShipping.StoreNumber, tmpShipping.Shipment, tmpShipping.Category, 
	tmpShipping.Mode, tmpShipping.ActSailDate, tmpShipping.EstArrivalDate, 
	tmpShipping.ActArrivalDate, tmpShipping.Signature, 
    Shipping.StoreNumber, Shipping.Shipment, Shipping.Category, 
	Shipping.Mode, Shipping.ActSailDate, Shipping.EstArrivalDate, 
	Shipping.ActArrivalDate, Shipping.QtyReceived, Shipping.Signature
FROM Projects INNER JOIN (tmpShipping 
        LEFT JOIN Shipping ON tmpShipping.TrackerNo = Shipping.TrackerNo) 
    ON Projects.StoreNumber = tmpShipping.StoreNumber
WHERE (Shipping.Category<>[tmpShipping]![Category]) 
    OR (Shipping.Shipment<>[tmpShipping]![Shipment]) 
	OR (Shipping.Mode<>[tmpShipping]![Mode]) 
	OR (Shipping.ActSailDate<>[tmpShipping]![ActSailDate]) 
	OR (Shipping.EstArrivalDate<>[tmpShipping]![EstArrivalDate]) 
	OR (Shipping.ActArrivalDate<>[tmpShipping]![ActArrivalDate]) 
	OR (Shipping.Signature<>[tmpShipping]![Signature]) 
	OR (Shipping.ID Is Null);

Open in new window

Next, I see that you are joining Shipping and tmpShipping on the field TrackerNo.  Is this a unique key in both tables?  If not then you will have trouble because records with one category will not match records with another, and vice-versa.

For example, say you have the following two records in tmpShipping, and the exact same two records in Shipping:
TrackerNo    Category
=========    ========
   1234         C1
   1234         C2

Open in new window

These records are the same in both tables, so you should not expect them to turn up in your "differences" query, but they will, because the C1 in one table does not match the C2 in the other.  So you will get:
TrackerNo    tmpShipping.Category    Shipping.Category
=========    ====================    =================
   1234             C1                      C2
   1234             C2                      C1

Open in new window

Now, I understand you want to select the records where anything including the list of categories has changed.  To do this, I think you would be best to compare the concatenated lists, not the individual categories.

For this, modify tmp2Query as follows:
SELECT DISTINCT
    Projects.DivisionName, Projects.ProjectName, IIf(IsNull([Shipping].[ID]),"New","Update") AS Type, 
    tmpShipping.StoreNumber, tmpShipping.Shipment, 
	DConcat("Category","tmpShipping","[TrackingNo]=" & [tmpShipping].[TrackingNo]) AS tmpShippingCategory, 
	tmpShipping.Mode, tmpShipping.ActSailDate, tmpShipping.EstArrivalDate, 
	tmpShipping.ActArrivalDate, tmpShipping.Signature, 
    Shipping.StoreNumber, Shipping.Shipment, 
	DConcat("Category","Shipping","[TrackingNo]=" & [Shipping].[TrackingNo]) AS ShippingCategory, 
	Shipping.Mode, Shipping.ActSailDate, Shipping.EstArrivalDate, 
	Shipping.ActArrivalDate, Shipping.QtyReceived, Shipping.Signature
FROM Projects INNER JOIN (tmpShipping 
        LEFT JOIN Shipping ON tmpShipping.TrackerNo = Shipping.TrackerNo) 
    ON Projects.StoreNumber = tmpShipping.StoreNumber
WHERE (DConcat("Category","tmpShipping","[TrackingNo]=" & [tmpShipping].[TrackingNo])
     <>DConcat("Category","Shipping","[TrackingNo]=" & [Shipping].[TrackingNo])) 
    OR (Shipping.Shipment<>[tmpShipping]![Shipment]) 
	OR (Shipping.Mode<>[tmpShipping]![Mode]) 
	OR (Shipping.ActSailDate<>[tmpShipping]![ActSailDate]) 
	OR (Shipping.EstArrivalDate<>[tmpShipping]![EstArrivalDate]) 
	OR (Shipping.ActArrivalDate<>[tmpShipping]![ActArrivalDate]) 
	OR (Shipping.Signature<>[tmpShipping]![Signature]) 
	OR (Shipping.ID Is Null);

Open in new window


I hope I have "read between the lines" correctly :-)

Best wishes,
Graham
ASKER
jmac001

Graham,

I am attaching a copy of the relationship table I hope that this helps.  The tables are joined by the TrackerNo which is a unique number that combines the project number and line item number.  



Tried the modified tmpQuery2 but it causes a run-time error 3075 - syntax error (missing operator) in query expression [TrackerNo]='.  Highlighted error in the attached VBA code for the DConcat.


Let me know if you need any other info.
Relationships-for-Parts-Tracker-.pdf
modConcat.pdf
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
Graham Mandeno

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
jmac001

Will try and let you know how it goes