Assigning Alias to First record of an ID

[Runawaygeek]

Hi all,

I have some data where there is a Product_ID, linked to this is the first appearance of this ID, Ship_Date. Furthermore, i have ordered the list by Ship_Date and autoNumbered the rows 1 > etc..

I need a way of finding the first appearance of that Product_ID and giving it an alias like "First Sale" and then for all the rest of the appearances to have the alias "Subsequent Sale"

Can someone please point me in the right direction on how to go about this?

Thanks,

 

[sneuberg]

You could using the DCount function to determine if the record is the first or not and then the IIF function is display first or subsequent record. If you give me the table name and field name that determines the order, I'll write the expression for you.

Note that in some cases an autonumber is not a reliable field to determine when a records has been added. A date would be better.

 

[Runawaygeek]

You could using the DCount function to determine if the record is the first or not and then the IIF function is display first or subsequent record. If you give me the table name and field name that determines the order, I'll write the expression for you.

Note that in some cases an autonumber is not a reliable field to determine when a records has been added. A date would be better.

Thanks, So, my Fields are:

Mat_ID
Publish_DateTime
RowNum(although it sounds like you wont want to use that, the table is cleared and repopulated each time before being accessed for information, so all records would be new each time this query is run)

TableName is "Dplay_Ordering_Step_2"

Thank you for your help,

 

[sneuberg]

I'm not clear on your names so I just picked Publish_DateTime. In that case the expression would be something like:

FirstOrNot: IIF(DCount("*","[qryFirstSale]","[Publish_DateTime] < #" & [Publish_DateTime] & "#")=0, "First Sale", "Subsequent Sale")

This is demonstrated in the attached database. Hopefully you can modify this expression to fit your situation.

 

[Runawaygeek]

Hi,

thank you for that, I changed the table (DOMAIN) name, but i get the same result for all.

My Table is called "Dplay_Ordering_Step_2"

I have attached images so you can see my data better.

- Date list

- Table Heading

I changed your code to this:

FirstOrNot: IIf(DCount("*","[Dplay_Ordering_Step_2]","[Publish_DateTime] < #" & [Publish_DateTime] & "#")=0,"First Package","Subsequent Package")

I only ever return the false value, "Subsequent Package", where as from the image, you can see that the ID for Sweden should return "First Package" (True)

Thanks,

 

[sneuberg]

I think this is because your dates are in dd/mm/yyyy format and not in American format. I'm looking for a fix.

 

[Runawaygeek]

I can convert them to US, apply this and then change back if required?
What format do they need to be? mm/dd/yyyy HH:mi:ss??

 

[sneuberg]

Allen Brown's site has the following function:

Public Function SQLDate(varDate As Variant) As String
    'Purpose:    Return a delimited string in the date format used natively by JET SQL.
    'Argument:   A date/time value.
    'Note:       Returns just the date format if the argument has no time component,
    '                or a date/time format if it does.
    'Author:     Allen Browne. allen@allenbrowne.com, June 2006.
    If IsDate(varDate) Then
        If DateValue(varDate) = varDate Then
            SQLDate = Format$(varDate, "\#mm\/dd\/yyyy\#")
        Else
            SQLDate = Format$(varDate, "\#mm\/dd\/yyyy hh\:nn\:ss\#")
        End If
    End If
End Function

Please put this in a modules and then change the expression to:

FirstOrNot: IIf(DCount("*","[Dplay_Ordering_Step_2]","[Publish_DateTime] < " & SQLDate([Publish_DateTime]))=0,"First Sale","Subsequent Sale")

The attached database has this change. Please let me know if that fixes this.

 

[Runawaygeek]

Hi,

So that kind of works, but, it returns the first date in the whole list, not the first date based on the Mat_ID.

I have Re-attached the Database, adding in the Mat_ID col. and an "Expected" column for return.

Thank you for helping me with this, i am very grateful and learning a lot.

View attachment FirstandSubsequent.accdb

 

[sneuberg]

Then Mat_ID gets added to the criteria like:

FirstOrNot: IIf(DCount("*","[Dplay_Ordering_Step_2]","[Mat_ID] = '" & [Mat_ID] & "' And [Publish_DateTime] < " & SQLDate([Publish_DateTime]))=0,"First Sale","Subsequent Sale")

The attached database has this change.

 

[Runawaygeek]

Thank you so much, that works a treat!

The criteria, what does it actually tell access to do?

"[Mat_ID] = '" & [Mat_ID] & "' And [Publish_DateTime] < " & SQLDate([Publish_DateTime]))=0,

I cant get my head around the =0 or how adding the Publish_DateTime to the Mat_Id is working?

Thanks again,

 

[sneuberg]

I think it's important that you understand this so you know how to make these yourself. First consider the DCount function. The first argument in this of "*" just means to count all records. If you put a field in there it won't count the records where that field is Null. The second argument is normally the table or query which has the records. The third argument specifies the criteria. For each record where this expression is true the count is incremented. So let's consider just the part:

[Publish_DateTime] < " & SQLDate([Publish_DateTime])

The part that is concatenated in or SQLDate([Publish_DateTime]) reflects whatever record is being retrieved at that point. So if they are being retrieved in ascending order at the first record this will be the earliest date. Since this is the earliest there can't be any records earlier so this expression is false and the counts is zero. Since the boolean expression for the IIF is whether or not the DCount returns 0, that expression is true so "First Sale" is return. When the second record is retrieve SQLDate([Publish_DateTime]) has the value for the second record and there is one record (the first) with a date less than it has so the count is one. For the third record the count will be two.

Adding the "[Mat_ID] = '" & [Mat_ID] part just restrict the DCount to consider the previous records where the Mat_ID is equal to the one being retrieved.

 

[Runawaygeek]

Thank you for that, its good to know how that works!
Have a great day.