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.
jmac001Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

GrahamMandenoCommented:
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]
0
jmac001Author Commented:
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?
0
GrahamMandenoCommented:
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
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

jmac001Author Commented:
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
0
GrahamMandenoCommented:
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
0
jmac001Author Commented:
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
0
GrahamMandenoCommented:
If TrackerNo is a text field then you must enclose its value in quotes when you call DConcat, so this:

DConcat("Category","Shipping","[TrackingNo]=" & [Shipping].[TrackingNo])

becomes this:

DConcat("Category","Shipping","[TrackingNo]='" & [Shipping].[TrackingNo] & "'")

You will need to do this in all three instances of DConcat in the query SQL.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jmac001Author Commented:
Will try and let you know how it goes
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.