Multiple Concats

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]

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));

Select allOpen in new window

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

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

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

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);

Select allOpen 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

Select allOpen 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

Select allOpen 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);

Select allOpen in new window

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

Best wishes,
Graham

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

Will try and let you know how it goes