Avatar of JohnTall
JohnTallFlag for Afghanistan asked on

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
Microsoft AccessSQL

Avatar of undefined
Last Comment
JohnTall

8/22/2022 - Mon
IrogSinta

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

Open in new window

ASKER
JohnTall

And I would put that in the append query for the Label_Seq field, correct ?
Jeffrey Coachman

<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
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
IrogSinta

As always, Jeff's posts are invaluable.

Ron
IrogSinta

And I would put that in the append query for the Label_Seq field, correct ?
Yes you would.
ASKER
JohnTall

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'
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
IrogSinta

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.
ASKER
JohnTall

Tnx, would you have some sample code to provide and I will adjust it to fit my purpose ?
IrogSinta

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

I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
ASKER
JohnTall

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 ?
ASKER CERTIFIED SOLUTION
IrogSinta

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
JohnTall

Tnx, I will give it a try next week
ASKER
JohnTall

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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
IrogSinta

Just use the name of the control in the form then:
rsTgt!TestDate = Me.NameOfControlWithTestDate
ASKER
JohnTall

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
IrogSinta

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.
Your help has saved me hundreds of hours of internet surfing.
fblack61
ASKER
JohnTall

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
IrogSinta

Post the SQL of qry-6030-Charts_over_17_Doctor_Group_Appts
ASKER
JohnTall

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];
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
IrogSinta

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.
ASKER
JohnTall

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
ASKER
JohnTall

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.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
IrogSinta

Can  you show me the SQL with the date hardcoded?
ASKER
JohnTall

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];
ASKER
JohnTall

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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
IrogSinta

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]
ASKER
JohnTall

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
ASKER
JohnTall

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
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
IrogSinta

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

ASKER
JohnTall

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
ASKER
JohnTall

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

tnx
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.