Record Indexing/Duplicate naming

[Runawaygeek]

Hi,


I have data that looks something like below,

ID | DATE | INFO
111 1/05/2016 Prog A
111 3/05/2016 Prog A
111 2/05/2016 Prog A
222 1/05/2016 Prog B
222 6/05/2016 Prog B
333 2/05/2016 Prog C

The ID is NOT Unique, it's a TV Programme ID

What i want is a way to sort the IDs into date order (which i have kind of done by just setting "Sort Asc" in the Columns within the query)
But then i want to add a 4th column that adds data, First Delivery, Update Delivery.

ID | DATE | INFO | Delivery_Type
111 1/05/2016 Prog A First_Delivery
111 3/05/2016 Prog A Update_Delivery
111 2/05/2016 Prog A Update_Delivery
222 1/05/2016 Prog B First_Delivery
222 6/05/2016 Prog B Update_Delivery
333 2/05/2016 Prog C First_Delivery

**sorry for my poor tables layout, i dont know how to make them on this forum!**

Any ideas how i go about this??

Thank you,

 

[Isskint]

If i understand your post correctly, you want to flag the chronologically first entry of ID as First_Delivery and subsequent entries of the same ID as Update_Delivery?

if so create the new field in the table first.
Now create an append query. Use the Dmin() function to check if the date is the minimum (earliest) and set the new field value accordingly.
Something like;
UPDATE TableName SET TableName.Delivery_Type = IIf(DMin("[Date]","[TableName]","[ID]='" & [ID] & "'")=[Date],"First_Delivery","Update_Delivery");

 

[Runawaygeek]

If i understand your post correctly, you want to flag the chronologically first entry of ID as First_Delivery and subsequent entries of the same ID as Update_Delivery?

if so create the new field in the table first.
Now create an append query. Use the Dmin() function to check if the date is the minimum (earliest) and set the new field value accordingly.
Something like;
UPDATE TableName SET TableName.Delivery_Type = IIf(DMin("[Date]","[TableName]","[ID]='" & [ID] & "'")=[Date],"First_Delivery","Update_Delivery");

Perfect, thank you for pointing me in the right direction.

 

[CJ_London]

there is no need to store the 4th column since it is a) a calculation and b) what if a record is deleted or changed? You'll have a big job to recalculate all the values

try this as a query (field names as you have them but note Date is a reserved word)

 SELECT P.ID, P.Date, P.Info, iif(P.Date=F.Date,"First","Update") & "_Delivery" AS DeliveryType
 FROM myTable P INNER JOIN (SELECT ID, min(Date) FROM myTable GROUP BY ID) F ON P.ID=F.ID
 ORDER By P.Date

 

[Runawaygeek]

Hi CJ

Thanks for the code, however, this is part of an end to end workflow, by this i mean the data gets erased at the start of every process.

I erase all tables, import a handful of Excel files, run them through bunch of queries to change and the data and add info, then pop them out into new Excel files for another team.

Data changes are not an issue as its fresh data each time.
I do keep a record of all delivered content to reference, which a query looks, this has an effect on the package type, but the area where the package type is built now exists in a query which appends to an empty table each run.

I have made a note of your idea, it may prove a better solution as i go down the line.

Thanks,