Count and recount per date and per user ...

[valkyry]

hope this is the correct posting.

I need something to count (two digits 00-99) per date and by per user.

So, if I enter a quote I need that quote for today to be 00 and the next one to be 01 up until 99.

BUT if another user enters quotes on the same day, the count should restart with 00 for the new user.

I need this in a query as well as in a form.

So far I was able to get help with:
field named txtCountUserDate = "01"
but it does not increment nor does it restart the count with a new user.

 

[barny2006]

each time you do a quote, you need to username with the max option. this will pull the highest user quote number. then when the same user does another quote, you do the same, and increment the max number by 1. when not found, that means a new user, who has not done a quote, and you start with user+00. the quotes should have it's own table.
sort of like this:
select max(quote_id) from quote_table where quote_id like
(user & "*")

 

[valkyry]

ok, I'm not sure how to incorporate that.

The QuoteNumber needs to be:
yymmdd##@@

1. "yymmdd" is driven from the EntryDate in the Quotes table - Format([EntryDate),"yymmdd"
2. COUNT (##) is what I need to automatically start at 00 for each new user on the same day and also restart for the same user on the next day.
3. UserID (@@) is driven from the FirstName and LastName from the Employee table. Left([FirstName],1) & [Left([LastName])

I do have a QuoteID that is set to AutoNumber and auto increment but this does not restart and is continues. So I can't use this for the "QuoteNumber".

They need the QuoteNumber to be the logic mentioned above.

Example:
060724 01 AE
yymmdd count user initials

 

[lespaul]

you need a way to store each user's max id! Another table that stores the intials, the last date incremented and the increment number. When a person gets a quote run a query to see if the last date incremented is today. If it is, then increment the number if it's not, start over and update the date and number.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[valkyry]

Leslie,
sorry, i'm not getting it.
how do you mean store each user's max id?

and about the last date increment and increment number?
increment what number?

Should I create a table of just numbers 00-99?
the userID, the user's initials, from the firstname and lastname fields with the Left([string],1)

 

[lespaul]

Joe Blow places his first order today (7/25/06) - his number needs to be 01 for TODAY, right?

Jane Doe places her last order today, her number needs to be incremented?

Say you have a table:
User LastDate LastNumber
JoeBlow 7/24/06 89
JaneDoe 7/25/06 71

When JoeBlow does his first order for TODAY (7/25/06) you need to query the table above:

SELECT * FROM Table WHERE User = "JoeBlow"

if LastDate <> Today
then Update LastDate = Today
Update LastNumber = 01

if LastDate = Today
then increment LastNumber for use in quote.

You don't even have to use initials, use the EMployeeID - you just need some way to identify the last day a specific user requested a quote AND the last quote number for that date.

Make sense now?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[valkyry]

where is the LastNumber coming from?

 

[valkyry]

barney2006,
how does this occur?
"each time you do a quote, you need to username with the max option. this will pull the highest user quote number:"

I don't get it??

 

[lespaul]

LastNumber the field name I made upfor a field in a table that you create to track each users last quote number for each day. Each day you update that number back to 1. Each time they get a quote you retrieve that number:

SELECT LastNumber From UserQuoteNumberTable WHERE Employee = WhoeverIsCreatingQuote

Increment LastNumber (+1) and use in your Quote Number.

The QuoteNumber needs to be:
yymmdd##@@

1. "yymmdd" is driven from the EntryDate in the Quotes table - Format([EntryDate),"yymmdd"
2. COUNT (##) is what I need to automatically start at 00 for each new user on the same day and also restart for the same user on the next day.
3. UserID (@@) is driven from the FirstName and LastName from the Employee table. Left([FirstName],1) & [Left([LastName])

Somewhere in the QuoteCreation process you are going to have take all these pieces of information and combine them to the format you need and run an update query:

UPDATE QuoteTable Set QuoteID = FormattedQuoteIDFromInformationAbove

Barney and I have approached this in differing ways. You need to decide if you want to go the way Barney has described or the way I have described. Either way you will get to where you want to be.


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[valkyry]

I have these.

It doesn't increment and resolve Left(EmployeeID.Column(3), 1)


my result is 06072601E instead of 06072601AE and the next quote still says 06072601E instead of 06072602AE.


Private Sub EmployeeID_AfterUpdate()
If IsNull(QuoteNumber) Then
QuoteNumber = Format([EntryDate], "yymmdd") & Format(Val(Nz(DMax("mid(QuoteNumber,7,8)", "CustomQuote", "Right(QuoteNumber,2) = '" & Left(EmployeeID.Column(3), 1) & Left(EmployeeID.Column(4), 1) & "' and Left(QuoteNumber,7) = Format([EntryDate],'yymmdd')"), 0)) + 1, "00") & Left(EmployeeID.Column(3), 1) & Left(EmployeeID.Column(4), 1)
End If
End Sub




This, in addition to the above problem, won't change:

Private Sub EmployeeID_Change()
If IsNull(QuoteNumber) Then
QuoteNumber = Format([EntryDate], "yymmdd") & Format(Val(Nz(DMax("mid(QuoteNumber,7,8)", "CustomQuote", "Right(QuoteNumber,2) = '" & Left(EmployeeID.Column(3), 1) & Left(EmployeeID.Column(4), 1) & "' and Left(QuoteNumber,7) = Format([EntryDate],'yymmdd')"), 0)) + 1, "00") & Left(EmployeeID.Column(3), 1) & Left(EmployeeID.Column(4), 1)
End If
End Sub

 

[barny2006]

here it is:
quote table:

john_06072700
john_06072701
john_06072702
john_06072703
jane_00000000
jane_06072701
paul_00000000
max_00000000
when you do: select max(quote_num) where quote_num like
("john" & "%") it will retrieve john_06072703
this the latest number for john. you add the date and up the number from 03 to 04 and insert it in this table.
and so on. if you need to insert other info in another table, you do the same way.