Urgently need help...

scott · October 17th, 2003, 01:01 PM

Hello, all!

I need to be able to populate a field in a form that will return an incremented number, and that will "re-start" at 1 on the next day. It is NOT my primary key (have an autonum field for that).

For example: Let's say today (10/17/03) I have logged in 25 entries. I would like a field in my form to show 1, then 2, then 3, etc. for each successive record entered on that date. Tomorrow (10/18/03) I would like the same field to show 1, then 2, then 3, etc. for each successive record recorded on that date (and so on...). The Form Field would be something like "Invoice number" (I'll rename to make it fit my situation). The important thing is that it increment automatically when starting a new record, and that it re-start on each successive day. I have a "Date In" field already in the table and form that automatically populates with the current date.

How do I implement this? Unfortunately, I have a superior who is adamant that we be able to do this...:(. I am relatively competent in Access, but have no experience whatsoever with VBA. Can someone please help??

Thanks!

sal · October 17th, 2003, 01:59 PM

Maybe a variation of this one will help?

http://p2p.wrox.com/topic.asp?TOPIC_ID=5337

Let us know.

Sal

Jeff Mason · October 17th, 2003, 02:36 PM

That referenced topic dealt with a numbering of the rows after the fact. That is, after the rows had already been inserted in the table (or at least that's how I interpreted the issue).

Here, I think the OP wants to number the rows as they are being inserted. Perhaps something along the lines of:

Code:

INSERT INTO Invoices
    (InvoiceNumber,...)
VALUES (SELECT NZ(MAX(InvoiceNumber),0)+1 WHERE InvoiceDate=#invoicedate#),...)

The idea being to find the maximum value in the date in question and add 1 to get the next number. If the row is the first one on the given date, there will be no MAX value (it will be null), so the NZ protects against that...

(I'm no Access programmer, so I can't help with the forms display issue, but I do know a little about SQL so I might be able to help, query wise...)

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

stoneman · October 17th, 2003, 02:47 PM

Since there is a DateIn field that is automatically entered she could build a report where the RecordSource is a query having the fields he wants including the DateIn field. He could use an unbound form where the user can select a specific date or between two dates as parameter to DateIn field of this query.

Put an unbound textbox in the report detail section.
Set the RecordSource to =1
Set the RunSum set to OverGroup or OverAll depending on how he watts the data.

This will display the count for each day
Hope this helps.

sal · October 17th, 2003, 03:04 PM

Jeff, This will work

SELECT (SELECT COUNT(*) FROM yourtable T2 WHERE T1.Today=T2.Today AND T2.EntryDate<=T1.EntryDate) AS TheeCount, T1.EntryDate, T1.Today
FROM yourtable AS T1
WHERE ((((SELECT COUNT(*) FROM yourtable T2 WHERE T1.Today=T2.Today AND T2.EntryDate<=T1.EntryDate))<>0));

I checked it on my PC. Here is what you do.

Make sure that there are two date fields
Today (is a date field that only keeps the current date IE 10/17/2003
Dafault Value is Date()
EntriTime (date field formated to keep date and time)
Default Value is Now()

Sal

sal · October 17th, 2003, 03:18 PM

Actually, to ensure uniquenes use an autonumber field instead for the EntryTime field.
there is a possibility of two or more records being inserted in one second.

Sal

scott · October 17th, 2003, 05:23 PM

Jeff: I think this is more along the lines of what I need. Now to be really stupid... how/where should I insert the code you suggest?

My table is set up with the following fields:

ID (autonum; PK; used as FK in several other tables)
DateIn (default value: Date() )
TimeIn (default value: Time() )
.... there are several other fields, but not relevant to my original post...

What I would like to do is create a "new" field/form field that will show: (a) current date as in the DateIn column and (b) a sequential number (NOT the autonum in field [ID]) that will increment automatically with each new record added. When the date advances, the sequential portion of this new field should start numbering again at "1". It is unlikely I will need more than 3 digits in the sequential number.

What we are doing is tracking the number of "job requests" in each day. Some days are busy, others not... It is important to my boss that we can keep track "on the fly" of "what job number we are up to" during a particular day. This is not so much for reporting issues (I have a query to handle that issue already) but to make data entry easier for the persons using the DB in their daily tasks. Needless to say, no one in the department knows Access... so ease of use is essential.

What we do is enter the "job request number" on a requisition form. My boss does NOT want us to be entering "1,235" as a "job request number." We normally don't go over 50-100 jobs per day (though it has happened in the past that we will log 125-150 requests in one day!), thus I will need to have 3 digits in my sequential number. To keep all the information together, I would like this "automatically" generated sequential number to appear in the main data entry form that the users will use.

I will need the information to automatically populate when the record is begun, as well. (OnEnter?)

Anyway, thanks for your ideas: your help is GREATLY Appreciated!!

"Here, I think the OP wants to number the rows as they are being inserted. Perhaps something along the lines of:

Code:

INSERT INTO Invoices
    (InvoiceNumber,...)
VALUES (SELECT NZ(MAX(InvoiceNumber),0)+1 WHERE InvoiceDate=#invoicedate#),...)

The idea being to find the maximum value in the date in question and add 1 to get the next number. If the row is the first one on the given date, there will be no MAX value (it will be null), so the NZ protects against that...

(I'm no Access programmer, so I can't help with the forms display issue, but I do know a little about SQL so I might be able to help, query wise...)

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

[/quote]

sal · October 17th, 2003, 08:10 PM

Scott,
Please double check my propossed solution. Since you already have an auto number. I can guarantee that this will always work assigning the correct number to your record, as long as you never delete any records. Like This

ID DailyID DateIn TimeIn
105 1 11/16/2003 9:01:47 AM
106 2 11/16/2003 9:01:48 AM
107 3 11/16/2003 9:01:48 AM
108 4 11/16/2003 4:45:00 PM
109 1 11/17/2003 8:59:00 AM First one
110 2 11/17/2003 10:00:00 AM

You get the idea

By using this in a query, you will not have to worry about where to put what code. the query takes care of it all for you. Just do not delete any records from it, and the numbers will never change. If you delete a record, It should not matter much.

SELECT T1.ID, (SELECT COUNT(*) FROM T1 T2 WHERE T1.DateIn=T2.DateIn AND T2.ID<=T1.ID) AS DailyID, T1.TimeIn, T1.DateIn
FROM T1
WHERE ((((SELECT COUNT(*) FROM T1 T2 WHERE T1.DateIn=T2.DateIn AND T2.ID<=T1.ID))<>0));

By the way, substitute T1 with the name of your table ans voila, you get

ID DailyID TimeIn DateIn All Other fields here
__________________________________________________ __________
1 1 12/30/1899 19:52:03 10/17/2003
2 2 12/30/1899 19:52:05 10/17/2003
3 3 12/30/1899 19:52:08 10/17/2003
4 4 12/30/1899 19:52:09 10/17/2003
5 5 12/30/1899 19:52:09 10/17/2003
6 6 12/30/1899 19:52:09 10/17/2003
7 7 12/30/1899 19:52:09 10/17/2003
8 8 12/30/1899 19:52:10 10/17/2003
9 9 12/30/1899 19:52:10 10/17/2003
10 1 12/30/1899 19:52:32 10/18/2003
11 2 12/30/1899 19:52:37 10/18/2003
12 3 12/30/1899 19:52:40 10/18/2003
13 4 12/30/1899 19:52:41 10/18/2003
14 5 12/30/1899 19:52:41 10/18/2003
15 6 12/30/1899 19:52:42 10/18/2003
16 7 12/30/1899 19:52:43 10/18/2003

It works. Notice multiple entries within the same second, and no problem.

Let me know if you do not know where to put this in the query.

Sal

scott · October 17th, 2003, 09:20 PM

Sal: Thanks GREATLY for the reply!! It looks great and will work on implementing it tomorrow when I go back into the office. However, where should I put the query? And could you give a newbie a quick explain of T1, T2 etc....? I hate to be such a pain in the ***, but I've struggled with this one so long, my brain is fried! Also, will this "store" the information in a table for retrieval at a later time? My boss wants to be able to, say, pull up a listing (much like the one you have inserted!) on a daily, weekly, monthly, quarterly, etc... basis. So, for example, on 10/31/03, she may choose to run a report showing all entries (with the job log "sequencing" intact) for 10/01/03....

Thanks in advance from a non-IT person. You know what they say, a little knowledge is a dangerous thing! Unfortunately in this situation, I have no choice but to come up with a solution! ARRRGH!

Thanks again,
Scott
New York City

Quote:

quote:Originally posted by sal
Scott,
Please double check my propossed solution. Since you already have an auto number. I can guarantee that this will always work assigning the correct number to your record, as long as you never delete any records. Like This

ID DailyID DateIn TimeIn
105 1 11/16/2003 9:01:47 AM
106 2 11/16/2003 9:01:48 AM
107 3 11/16/2003 9:01:48 AM
108 4 11/16/2003 4:45:00 PM
109 1 11/17/2003 8:59:00 AM First one
110 2 11/17/2003 10:00:00 AM

You get the idea

By using this in a query, you will not have to worry about where to put what code. the query takes care of it all for you. Just do not delete any records from it, and the numbers will never change. If you delete a record, It should not matter much.

SELECT T1.ID, (SELECT COUNT(*) FROM T1 T2 WHERE T1.DateIn=T2.DateIn AND T2.ID<=T1.ID) AS DailyID, T1.TimeIn, T1.DateIn
FROM T1
WHERE ((((SELECT COUNT(*) FROM T1 T2 WHERE T1.DateIn=T2.DateIn AND T2.ID<=T1.ID))<>0));

By the way, substitute T1 with the name of your table ans voila, you get

ID DailyID TimeIn DateIn All Other fields here
__________________________________________________ __________
1 1 12/30/1899 19:52:03 10/17/2003
2 2 12/30/1899 19:52:05 10/17/2003
3 3 12/30/1899 19:52:08 10/17/2003
4 4 12/30/1899 19:52:09 10/17/2003
5 5 12/30/1899 19:52:09 10/17/2003
6 6 12/30/1899 19:52:09 10/17/2003
7 7 12/30/1899 19:52:09 10/17/2003
8 8 12/30/1899 19:52:10 10/17/2003
9 9 12/30/1899 19:52:10 10/17/2003
10 1 12/30/1899 19:52:32 10/18/2003
11 2 12/30/1899 19:52:37 10/18/2003
12 3 12/30/1899 19:52:40 10/18/2003
13 4 12/30/1899 19:52:41 10/18/2003
14 5 12/30/1899 19:52:41 10/18/2003
15 6 12/30/1899 19:52:42 10/18/2003
16 7 12/30/1899 19:52:43 10/18/2003

It works. Notice multiple entries within the same second, and no problem.

Let me know if you do not know where to put this in the query.

Sal

sal · October 17th, 2003, 10:44 PM

You are welcome,
Click on Queries,
Click on "New" on top
Click "OK" when the "New Query" dialog opens
Close the "Show Tables" dialog when it opens
On the top left of your screen you will see "SQL" in Bold letters, click on that and a screen will open and it will have the word "SELECT;" entered. Delete it and paste the code I gave you.
Replace every T1 for the name of your table in your database.
open your query and you should see the results.
after that you can open your query in design view as normal and you can add the remaining fields that you wish to show.

that's it.
time to go to bed, too bad you are working on Saturday.

Sal