Restart number field with 1 for new records for every new date

I have a table in access 2003 where I append records to it using an Append Query, in that table I have a auto number field and other fields such as Test_Date, chart etc. then I have a number field in that same table called Label_Seq , I want that whenever I append records to that table that the value in 'Label_Seq' should start by 1 and increment accordingly for every new value in 'Test_Date'

For example, if I add 5 records where the test date was 9/8/13, 'Label_Seq' should be 1 2 3 4 5, and if I add after that 10 records where the test date was 9/9/13, 'Label_Seq'  should be 1 2 3 4 5 6 7 8 9 10, and so on.

So I am looking for the value I should use on 'Label_Seq' when I run the append query to get this accomplished.

Tnx
JohnTallAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

IrogSintaCommented:
For Label_Seq try this:
Nz(DMax("[Label_Seq]","TableName","[TestDate] = #" &[Enter date parameter here] & "#"),0) +1

Open in new window

0
JohnTallAuthor Commented:
And I would put that in the append query for the Label_Seq field, correct ?
0
Jeffrey CoachmanMIS LiasonCommented:
<No Points waqnted as IrogSinta  has posted the most solid way to do this>

There may be a slim chance that it might be difficult to find the highest value (DMax) when values are added so rapidly with bulk Updates done via SQL (Timing issues as far as when Access sees the new value(s) as being "Added/Updated")
This may only be a factor if you are adding all the records in one shot. (Click one button and it adds in 5 records)
If you are adding the records one at a time, you should be fine

It may also become an issue if two people are trying to run this code at the same time...

..so just double-check the numbering when done...

My guess is that you will be fine.

;-)

JeffCoachman
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

IrogSintaCommented:
As always, Jeff's posts are invaluable.

Ron
0
IrogSintaCommented:
And I would put that in the append query for the Label_Seq field, correct ?
Yes you would.
0
JohnTallAuthor Commented:
I used the code that IrogSinta provided in the 'Field' section of the append query, and in the 'Append to' section I have selected the 'Label_Seq' field, but the problem is that when I ran the query it set everything to '1'
0
IrogSintaCommented:
Yes, this method of using an append query to an access table is only good when adding 1 record at a time.  The only way I'm aware of this being possible for simultaneously appending multiple records using SQL is if your table is a linked table to Oracle or SQL_Server where you would have a Trigger that automatically puts those numbers in.  

In Access, you would need to use some VB code to go through a loop and append the records using either an Execute SQL method or a Recordset AddNew method.
0
JohnTallAuthor Commented:
Tnx, would you have some sample code to provide and I will adjust it to fit my purpose ?
0
IrogSintaCommented:
Here are two ways, the first using a Recordset, the second using a SQL insert statement.  You can also choose to get the next sequence number every time within the loop or use an incrementing counter inside the loop.  Here I show both methods:

    Dim rst As Recordset
    Dim i As Integer
    Dim numRec As Integer
    Dim dt As Date
    
    dt = #9/18/2013#
    numRec = 5
    
    'add records to bb table
    Set rst = CurrentDb.OpenRecordset("TableName")
        For i = 0 To numRec
            rst.AddNew
            rst!Label_Seq = Nz(DMax("[Label_Seq]", "TableName", "[TestDate] = #" & dt & "#"), 0) + 1
            rst!TestDate = dt
            rst.Update
        Next
    rst.CLOSE
    Set rst = Nothing

Open in new window

    Dim i As Integer
    Dim SeqNum As Integer
    Dim numRec As Integer
    Dim dt As Date
    
    dt = #9/18/2013#
    numRec = 5
    
    SeqNum = Nz(DMax("[Label_Seq]", "TableName", "[TestDate] = #" & dt & "#"), 0) + 1
    For i = 1 To Me.txtQty
        SeqNum = SeqNum + 1
        DoCmd.RunSQL "Insert Into TableName (Label_Seq, TestDate) Values (" & SeqNum & ",#" & dt & "#)"
    Next

Open in new window

0
JohnTallAuthor Commented:
Tnx for the 2 samples, it shows the code for the target table that I am adding records to, but how do I add the source query to take the data from ?
0
IrogSintaCommented:
You would have to add another recordset in both samples to do this.  The recordset could either be a table, a query, a SQL Select string, or a RecordsetClone of a form's recordsource.

I can't say which would work better for you since I don't know how your design so I'm going to give you samples based on a Query source.  Of course, you would need to change your Append query to a Select query.  Here are the revised samples:
    Dim db As Database
    Dim rsSrc As Recordset
    Dim rsTgt As Recordset

    'add records to table
    Set db = CurrentDb
    Set rsSrc = db.OpenRecordset("SourceQueryName")
    Set rsTgt = db.OpenRecordset("TargetTableName")
        
    Do While Not rsSrc.EOF
        rsTgt.AddNew
            rsTgt!Label_Seq = Nz(DMax("[Label_Seq]", "TableName", "[TestDate] = #" & rsSrc!TestDate & "#"), 0) + 1
            rsTgt!TestDate = rsSrc!TestDate
            rsTgt!AnotherField = rsSrc!AnotherField
            rsTgt.Update
        rsTgt.Update
        rsSrc.MoveNext
    Loop
    rsSrc.Close
    rsTgt.Close
    Set rsSrc = Nothing
    Set rsTgt = Nothing

Open in new window

    Dim db As Database
    Dim rst As Recordset

    'add records to table
    Set db = CurrentDb
    Set rst = db.OpenRecordset("SourceQueryName")
    
    SeqNum = Nz(DMax("[Label_Seq]", "TargetTableName", "[TestDate] = #" & rst!TestDate & "#"), 0) + 1
    Do While Not rst.EOF
        DoCmd.RunSQL "Insert Into TargetTableName (Label_Seq, TestDate, AnotherField) Values (" & SeqNum & ",#" & rst!TestDt & "#,'" & rst!AnotherField & "')"
        SeqNum = SeqNum + 1
        rst.MoveNext
    Loop
    rst.Close
    Set rst = Nothing

Open in new window

Incidentally, if the SQL of your query is simple you can just get rid of your query and replace line 6  above like so:
Set rst = db.OpenRecordset("SELECT * FROM Table1 WHERE [TestDate]>DATE-7")
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JohnTallAuthor Commented:
Tnx, I will give it a try next week
0
JohnTallAuthor Commented:
Tried it, seems to be working, but the only problem is that in the rsSrc I refer in the query to a date which is in a textbox control on a form, and the form is open at the time, but it errors out with

Runtime error 3061. Too few parameters. Expected 1

so I need to embed the date when I set the rsSrc
0
IrogSintaCommented:
Just use the name of the control in the form then:
rsTgt!TestDate = Me.NameOfControlWithTestDate
0
JohnTallAuthor Commented:
it is not the target table, it is the source query rsSrc I need to pass on a date that is in the open form

so I used the following:

Set rsSrc = CurrentDb.OpenRecordset("select * from MyQueryName WHERE [Appt Date] = #" & Me.txtAptDate & "#")

but it errors out with:

3131

Syntex error in FROM clause
0
IrogSintaCommented:
I don't see anything wrong with your SELECT statement.  MyQueryName is the actual name of your query, right?  Try setting a break point and seeing what the value of txtAptDate is at that point.  If that doesn't help, try posting your whole code and specify where it actually shows the error.
0
JohnTallAuthor Commented:
The actual name of the query is

qry-6030-Charts_over_17_Doctor_Group_Appts

Did that, and here's the debug window:

?me.txtAptDate
9/16/2013

So the date is there, but it errors out with 3131

Syntex error in FROM clause
0
IrogSintaCommented:
Post the SQL of qry-6030-Charts_over_17_Doctor_Group_Appts
0
JohnTallAuthor Commented:
SELECT dbo_jk_Patients.[Chart Number], dbo_View_Appointment_all.[Appt Date]
FROM Provider_Types INNER JOIN ((dbo_View_Appointment_all INNER JOIN dbo_jk_Patients ON dbo_View_Appointment_all.Chart = dbo_jk_Patients.[Chart Number]) INNER JOIN dbo_jk_provider ON dbo_View_Appointment_all.Provider = dbo_jk_provider.Code) ON Provider_Types.Provider_Code = dbo_jk_provider.Code
WHERE (((dbo_jk_Patients.[Chart Number])<>"") AND ((Provider_Types.Provider_Type)="Doctor") AND ((DateDiff("yyyy",[Birth Date],[Appt Date]))>17)) OR (((dbo_jk_Patients.[Chart Number])<>"") AND ((DateDiff("yyyy",[Birth Date],[Appt Date]))>17) AND ((dbo_View_Appointment_all.Reason)="group")) OR (((dbo_jk_Patients.[Chart Number])<>"") AND ((DateDiff("yyyy",[Birth Date],[Appt Date]))>17) AND ((dbo_View_Appointment_all.Reason)="assessmen"))
GROUP BY dbo_jk_Patients.[Chart Number], dbo_View_Appointment_all.[Appt Date], dbo_jk_Patients.[Last Name] & ", " & dbo_jk_Patients.[First Name]
ORDER BY dbo_jk_Patients.[Last Name] & ", " & dbo_jk_Patients.[First Name];
0
IrogSintaCommented:
Does this query return any records without requiring any input?  If it does, then without seeing the database, I'm at a loss.  If possible, create a copy of your database to upload with just the objects in question and add a few fake records in the tables.
0
JohnTallAuthor Commented:
It does return records without requiring any input, maybe there are some single quotes missing here ? copying the whole DB with some fake records will be very consuming
0
JohnTallAuthor Commented:
What's even more interesting here is that when I hardcode the date criteria in the query it runs perfect, so that hints me that there are some single or double quotes missing here.
0
IrogSintaCommented:
Can  you show me the SQL with the date hardcoded?
0
JohnTallAuthor Commented:
SELECT dbo_jk_Patients.[Chart Number], dbo_View_Appointment_all.[Appt Date]
FROM Provider_Types INNER JOIN ((dbo_View_Appointment_all INNER JOIN dbo_jk_Patients ON dbo_View_Appointment_all.Chart = dbo_jk_Patients.[Chart Number]) INNER JOIN dbo_jk_provider ON dbo_View_Appointment_all.Provider = dbo_jk_provider.Code) ON Provider_Types.Provider_Code = dbo_jk_provider.Code
WHERE (((dbo_jk_Patients.[Chart Number])<>"") AND ((Provider_Types.Provider_Type)="Doctor") AND ((DateDiff("yyyy",[Birth Date],[Appt Date]))>17) AND ((dbo_View_Appointment_all.[Appt Date])=#9/16/2013#)) OR (((dbo_jk_Patients.[Chart Number])<>"") AND ((DateDiff("yyyy",[Birth Date],[Appt Date]))>17) AND ((dbo_View_Appointment_all.Reason)="group") AND ((dbo_View_Appointment_all.[Appt Date])=#9/16/2013#)) OR (((dbo_jk_Patients.[Chart Number])<>"") AND ((DateDiff("yyyy",[Birth Date],[Appt Date]))>17) AND ((dbo_View_Appointment_all.Reason)="assessmen") AND ((dbo_View_Appointment_all.[Appt Date])=#9/16/2013#))
GROUP BY dbo_jk_Patients.[Chart Number], dbo_View_Appointment_all.[Appt Date], dbo_jk_Patients.[Last Name] & ", " & dbo_jk_Patients.[First Name]
ORDER BY dbo_jk_Patients.[Last Name] & ", " & dbo_jk_Patients.[First Name];
0
JohnTallAuthor Commented:
When I name the hardcoded query without the SELECT statement it runs fine, like:

Set rsSrc = CurrentDb.OpenRecordset ("qry-6030-Charts_over_17_Doctor_Group_Appts")

but when I call it like:

Set rsSrc = CurrentDb.OpenRecordset ("SELECT * FROM qry-6030-Charts_over_17_Doctor_Group_Appts")

I get the same error, so it is definitely something in the select statement that I am wrong with
0
IrogSintaCommented:
I found the problem.  SQL doesn't like the dash "-" symbol in your query name.  If you put brackets around it, it will work.  
SELECT * FROM [qry-6030-Charts_over_17_Doctor_Group_Appts]
0
JohnTallAuthor Commented:
You were right, all I had to do was remove the rsTgt.Update that was there twice

Tnx so much IrogSinta and the crew out there
0
JohnTallAuthor Commented:
Just another issue, I have indexed the target table so that it does not accept duplicates, so I need code that if err.number = 3022 than it should just move to the next record in rsSrc
0
IrogSintaCommented:
How about checking to see if it already exists in the table before trying to add a record. For instance, if the primary key is [Chart Number], it would look something like:
    If DCount("*","TargetTableName","[Chart Number]='" & rst![Chart Number] & "'") = 0 then
        rsTgt.AddNew
            rsTgt!Label_Seq = Nz(DMax("[Label_Seq]", "TableName", "[TestDate] = #" & rsSrc!TestDate & "#"), 0) + 1
            rsTgt!TestDate = rsSrc!TestDate
            rsTgt!AnotherField = rsSrc!AnotherField
        rsTgt.Update
    End If
    rsSrc.MoveNext

Open in new window

0
JohnTallAuthor Commented:
I did that, and now I need to update another field in that same table if the record exist, and if not, I need to add it, so here is the code that I use:

If DCount("*", "tbl_UDS_Prints", "[Chart]='" & rsLabSrc![Lab_Chart] & "' AND [Test_Date] = #" & rsLabSrc![Lab_Date] & "#") = 0 Then
            rsLabTgt.AddNew
                rsLabTgt!Test_Date = rsLabSrc![Lab_Date]
                rsLabTgt!Chart = rsLabSrc![Lab_Chart]
                rsLabTgt!Results_Seq = Nz(DMax("[Results_Seq]", "tbl_UDS_Prints", "[Test_Date] = #" & rsLabSrc![Lab_Date] & "#"), 0) + 1
            rsLabTgt.Update
        Else
            rsLabTgt.Edit
           
                     
              rsLabTgt!Results_Seq = Nz(DMax("[Results_Seq]", "tbl_UDS_Prints", "[Test_Date] = #" & rsLabSrc![Lab_Date] & "#"), 0) + 1
               
                               
            rsLabTgt.Update
                     
        End If
 
but my problem is that the target table doesn't change, it just stays with the same chart, and the number keeps increasing, I need it to update the EXISTING target record where rsLabTgt![Lab_Chart] = rsLabSrc![Lab_Chart]      

Sorry for me being so naive, but this must be a very simple thing that I can't get.

Tnx
0
JohnTallAuthor Commented:
I got it, I created a new recordset that open the target table where the chart and dates match, and it is working.

tnx
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.