First Date Instance (1 Viewer)

[mark365]

I’m not sure on what the best function is to find out the following:

First Sale Date per Customer

I have 2 fields - Sale Date and Customer ID.

I want to add a new field named First Sale Date. Do I used the Max function? Not sure how to incorporate the per Customer ID.

I have made an attempt below:

FirstSaleDate: Max([SaleDate]

 

[cheekybuddha]

Are you trying to do this in a query?

The earliest date will actually be the MIN date.

Try:
FirstSaleDate: DMin("SaleDate", "TableName", "CustomerID = " & [CustomerID])

hth,

d

 

[cheekybuddha]

A better way to do this would be to use an Aggregate query:

SELECT
  CustomerID,
  MIN(SaleDate) AS FirstSaleDate
FROM TableName
GROUP BY
  CustonerID
;

but it depends on your specific situation and where you are using these results.

 

[mark365]

Are you trying to do this in a query?

The earliest date will actually be the MIN date.

Try:
FirstSaleDate: DMin("SaleDate", "TableName", "CustomerID = " & [CustomerID])

hth,

d

Thank you for your help. It’s much appreciated. I have tried this a few times and am getting #error under this field in query results.

In your sample above where it says table name this is where I put the name of the sales table correct?

would it be better to do this in the actual table rather than a query?

I tried adding the SQL but I am not that confident with.

 

[cheekybuddha]

Remember, I can't see your computer screen so I don't know what your table is called, the name of its fields and their datatypes, what is the purpose of your query (eg RecordSource for a form, etc).

If you provide these details I can help more specifically.

>> In your sample above where it says table name this is where I put the name of the sales table correct? <<
Yes

>> would it be better to do this in the actual table rather than a query? <<
I doubt it, but I'm not sure exactly what you mean.

 

[mark365]

Remember, I can't see your computer screen so I don't know what your table is called, the name of its fields and their datatypes, what is the purpose of your query (eg RecordSource for a form, etc).

If you provide these details I can help more specifically.

>> In your sample above where it says table name this is where I put the name of the sales table correct? <<
Yes

>> would it be better to do this in the actual table rather than a query? <<
I doubt it, but I'm not sure exactly what you mean.

thank you - the purpose of the query is to do a match on customer ID to see which customers have made a purchase so linking between sales table and customer table.

Then I need to find the first sale date per customer but want it in its own column.

The query will just be run and show the customers and their associated sales. The query won’t be used for any further development in access at this stage. Just export to excel. I hope this gives some better insight.

 

[cheekybuddha]

Thanks for the extra detail.

Please can you also post the SQL of the query before trying to add in the FirstSaleDate column.

 

[arnelgp]

create 2 queries.

first query is on Sales table, to get the CustomerID and its First sale date:

query1:

select [customerID], min([sales date) as FirstSaleDate from [sales table];

the second (query2), joining customer table to query1 above:

select [customer table].[customerid], [customer table].[customer name], [query1].[FirstsaleDate] From
[customer table] Left join [query1] on [customer table].[customerid] = [query1].[customerid];

 

[mark365]

Thanks for the extra detail.

Please can you also post the SQL of the query before trying to add in the FirstSaleDate column.

Thanks will paste in

Thanks for the extra detail.

Please can you also post the SQL of the query before trying to add in the FirstSaleDate column.

SELECT [CUSTOMERS].CUSTOMERID, [SALES].[SALEDATE]

FROM [CUSTOMERS] INNER JOIN [SALES] ON [CUSTOMERS].CUSTOMERID = [SALES].[CUSTOMER NUMBER];

 

[mark365]

create 2 queries.

first query is on Sales table, to get the CustomerID and its First sale date:

query1:

select [customerID], min([sales date) as FirstSaleDate from [sales table];

the second (query2), joining customer table to query1 above:

select [customer table].[customerid], [customer table].[customer name], [query1].[FirstsaleDate] From
[customer table] Left join [query1] on [customer table].[customerid] = [query1].[customerid];

thanks tried this but getting a prompt each time when running query to enter CustomerID

 

[cheekybuddha]

OK,

You can use a sub-query in this case:

SELECT
  c.CUSTOMERID,
  s.SALEDATE,
  (
    SELECT
      MIN(s2.SALEDATE)
    FROM SALES s2
    WHERE s2.[CUSTOMER NUMBER] = s.[CUSTOMER NUMBER]
    GROUP BY
      s2.[CUSTOMER NUMBER]
  ) AS FirstSaleDate
FROM CUSTOMERS c
INNER JOIN SALES s
        ON c.CUSTOMERID = s.[CUSTOMER NUMBER];

[EDITED: forgot GROUP BY clause ]

hth,

d

 

[cheekybuddha]

It might be more efficient to join the subquery outside the SELECT as arnelgp suggests:

SELECT
  c.CUSTOMERID,
  s.SALEDATE,
  s2.FirstSaleDate
FROM (
  CUSTOMERS c
  LEFT JOIN (
    SELECT
      s2.[CUSTOMER NUMBER]
      MIN(s2.SALEDATE) AS FirstSaleDate
    FROM SALES s2
    GROUP BY 
      s2.[CUSTOMER NUMBER]
  )
         ON c.CUSTOMERID = s2.[CUSTOMER NUMBER]
)
INNER JOIN SALES s
        ON c.CUSTOMERID = s.[CUSTOMER NUMBER];

 

[mark365]

OK,

You can use a sub-query in this case:

SELECT
  c.CUSTOMERID,
  s.SALEDATE,
  (
    SELECT
      MIN(s2.SALEDATE)
    FROM SALES s2
    WHERE s2.[CUSTOMER NUMBER] = s.[CUSTOMER NUMBER]
    GROUP BY
      s2.[CUSTOMER NUMBER]
  ) AS FirstSaleDate
FROM CUSTOMERS c
INNER JOIN SALES s
        ON c.CUSTOMERID = s.[CUSTOMER NUMBER];

[EDITED: forgot GROUP BY clause ]

hth,

d

thanks for your continued work on finding a solution for me. When I place both extracts of SQL code in, I get the same error message with both.“Syntax error (missing operator) in query expression ‘s.SALEDATE’ - I think it related to the 3 line. What can I try to fix this missing operator? Thanks again

 

[cheekybuddha]

OK, that's slightly confusing! Are you getting the exact same error with both queries?

There is a mistake in the second query. it should read:

SELECT
  c.CUSTOMERID,
  s.SALEDATE,
  s2.FirstSaleDate
FROM (
  CUSTOMERS c
  LEFT JOIN (
    SELECT
      s2.[CUSTOMER NUMBER],
      MIN(s2.SALEDATE) AS FirstSaleDate
    FROM SALES s2
    GROUP BY 
      s2.[CUSTOMER NUMBER]
  )
         ON c.CUSTOMERID = s2.[CUSTOMER NUMBER]
)
INNER JOIN SALES s
        ON c.CUSTOMERID = s.[CUSTOMER NUMBER];

(Missing comma in the SELECT slause of the sub-query)

However, Access requires the nesting of Joins in a specific way which I always get wrong and so there might be something more fundamwntal wrong with the syntax.

The first query looks like it should be OK.

You could try it like this:

SELECT
  c.CUSTOMERID,
  s.SALEDATE,
  (
    SELECT
      MIN(s2.SALEDATE)
    FROM SALES s2
    WHERE s2.[CUSTOMER NUMBER] = c.CUSTOMERID
    GROUP BY
      s2.[CUSTOMER NUMBER]
  ) AS FirstSaleDate
FROM CUSTOMERS c
INNER JOIN SALES s
        ON c.CUSTOMERID = s.[CUSTOMER NUMBER];

If that still fails, then check that this works:

SELECT
  c.CUSTOMERID,
  s.SALEDATE
FROM CUSTOMERS c
INNER JOIN SALES s
        ON c.CUSTOMERID = s.[CUSTOMER NUMBER];