Link to home
Start Free TrialLog in
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
Avatar of IrogSinta
IrogSinta
Flag of United States of America image

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

Open in new window

Avatar of JohnTall

ASKER

And I would put that in the append query for the Label_Seq field, correct ?
<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
As always, Jeff's posts are invaluable.

Ron
And I would put that in the append query for the Label_Seq field, correct ?
Yes you would.
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'
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.
Tnx, would you have some sample code to provide and I will adjust it to fit my purpose ?
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

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
Avatar of IrogSinta
IrogSinta
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Tnx, I will give it a try next week
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
Just use the name of the control in the form then:
rsTgt!TestDate = Me.NameOfControlWithTestDate
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
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.
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
Post the SQL of qry-6030-Charts_over_17_Doctor_Group_Appts
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];
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.
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
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.
Can  you show me the SQL with the date hardcoded?
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];
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
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]
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
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
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

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
I got it, I created a new recordset that open the target table where the chart and dates match, and it is working.

tnx