Results 1 to 14 of 14
  1. #1
    tagteam is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2013
    Posts
    486

    create auto incrementing value in make table query

    I need to auto increment a value from 1 - the end of the lines when using a make table query.

    Is this something I can do when i originally create the table or do I need to run an update query?

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    a make table query makes the table so there is no 'originally create'

    You can create your table first and include an autonumber field then use an append query

  3. #3
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    tagteam,
    Can you give us a few lines of simple English describing WHAT you are trying to achieve?
    If readers know/understand your requirement, you will get more focused responses.
    Good luck.

  4. #4
    tagteam is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2013
    Posts
    486
    I am using a make table query to create a custom fixed width output file. When I create the table one of the fields is LineNumber. I was looking online and it did not appear that there was way to properly populate the LineNumber field during the make table query because in access you cant really use SQL, because the Jet Database engine.

    I was posting here in case anyone had any other thoughts.

    What I need to do us run a make table query that uses criteria to take data from table1 and create table2. During that process I want to reset the values in the LineNumber field to be auto incremented. So each time the query is run it will create the new table and set LineNumber to be 1 for record 1 and 2 for record 2 and 3 for record 3 etc, etc.

    I think Ajax may be correct and this can only be done after the table is created. If that is the case then I could use some help with either VBA or an update query to make that happen.

  5. #5
    GinaWhipp's Avatar
    GinaWhipp is offline Competent Performer
    Windows 7 64bit Access 2013 32bit
    Join Date
    Jul 2011
    Location
    Ohio, USA
    Posts
    377
    As Orange said, would be nice to know what you are trying to accomplish. That said, see fCompileNumbers() on...
    https://www.access-diva.com/r9.html

    It does create a Table incriminating numbers. You may be able to adapt it to your needs.

  6. #6
    tagteam is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2013
    Posts
    486
    All I am trying to do is set the Field LineNumber to start at 1 and increment until the last record. I want to do it when creating the table but if I cant then i can use an update query. Either way is fine, I am just having a hard time trying to find good info on either of them. My google queries my not be saying quite the right thing.

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    What exact SQL are you using - SELECT INTO or CREATE TABLE?

    Why repeatedly create and delete table? Table can be permanent and data temporary. A line number can be calculated in a SELECT query although it can perform slowly with large dataset because this involves either DCount() or a nested subquery. Why another table and not just query for the export?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    tagteam is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2013
    Posts
    486
    I am using the standard access make table query so it is using the SELECT INTO

    I am inheriting this database so the original logic for create new table vs empty table and append is unknown to me. If it were easier to do this with an append I am sure I could rewrite the process and delete the data and then run an append query to append the data but I would have to redo several queries and rewrite a fair bit of vba.

    Each time I run this it is a small dataset, usually 1-50 lines and no more.

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Provide your full SQL statement.

    Some sample data could be helpful.

    If linenumber is calculated in query, likely table would not even be needed for export.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    tagteam is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2013
    Posts
    486
    From what I am reading it looks like their is a way to do it in the alter

    here is the sql and I am not sure how or where to put the alter table in there or if it should be done after. Or maybe it is run the query, and just add this bit at the end as a new statement ending with semicolon

    something like

    ALTER TABLE MyTable ADD [LineNumber] COUNTER

    Code:
    SELECT 1 AS RecordFormat, tbl_Customers.CustomerShortCode AS BilledParty, tbl_ImportedRepairs.InvoiceNumber, tbl_ImportedRepairs.CarInitial, Format(Val([CarNumber]),"000000") AS CarNumber1, Format(Val([Quantity]),"0000") AS Quantity1, tbl_ImportedRepairs.ConditionCode, Format(Val(Abs([LaborChargeNet])*100),"0000000") AS LaborCharge, Format(Val(Abs([MaterialChargeNet])*100),"00000000") AS MaterialCharge, IIf([MaterialChargeValue]<0,"C","D") AS MaterialSign, tbl_ImportedRepairs.ReservedCRB7, tbl_ImportedRepairs.FreeUserArea INTO Create500ByteFROM tbl_ImportedRepairs INNER JOIN tbl_Customers ON tbl_ImportedRepairs.PaymentRespID = tbl_Customers.CustomerIDNumber
    WHERE (((tbl_ImportedRepairs.InvoiceNumber)=[Forms]![frmInvoicesWithTrinityBatchTate]![InvoiceNumber]));
    
    
    
    Alter Table MyTable Add [LineNumber] Counter

  11. #11
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    The ALTER TABLE would be another query object.

    Would have to enter the SQL into query SQLView since there is no designer for that.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  12. #12
    tagteam is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2013
    Posts
    486
    yes, I could do that. This query is called by VBA so I could just add another statement right after it to alter the table.

    A couple of questions:
    1. do you think that would work
    2. if so and i put it in another query to run right after it do I have to leave off the field LIneNumber in the original query bc it looks like this statement is going to create that field.

  13. #13
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    1. Yes

    and

    2. Yes
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  14. #14
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    tagteamQuery
    I reformatted and am reposting your query SQL from post #10 for readability:
    Code:
    SELECT 1 AS RecordFormat
    	,tbl_Customers.CustomerShortCode AS BilledParty
    	,tbl_ImportedRepairs.InvoiceNumber
    	,tbl_ImportedRepairs.CarInitial
    	,Format(Val([CarNumber]), "000000") AS CarNumber1
    	,Format(Val([Quantity]), "0000") AS Quantity1
    	,tbl_ImportedRepairs.ConditionCode
    	,Format(Val(Abs([LaborChargeNet]) * 100), "0000000") AS LaborCharge
    	,Format(Val(Abs([MaterialChargeNet]) * 100), "00000000") AS MaterialCharge
    	,IIf([MaterialChargeValue] < 0, "C", "D") AS MaterialSign
    	,tbl_ImportedRepairs.ReservedCRB7
    	,tbl_ImportedRepairs.FreeUserArea
    INTO Create500ByteFROM tbl_ImportedRepairs
    INNER JOIN tbl_Customers ON tbl_ImportedRepairs.PaymentRespID = tbl_Customers.CustomerIDNumber
    WHERE (((tbl_ImportedRepairs.InvoiceNumber) = [Forms] ! [frmInvoicesWithTrinityBatchTate] ! [InvoiceNumber]));
    I am still wondering if you could give us a plain English --non database jargon --description of the "business process(es) involved and the purpose of the line numbers?

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 11-30-2011, 11:02 AM
  2. Replies: 4
    Last Post: 07-27-2011, 09:25 AM
  3. Replies: 11
    Last Post: 07-14-2011, 03:03 PM
  4. Replies: 1
    Last Post: 09-29-2010, 08:01 AM
  5. Custom & Auto Incrementing Job Number
    By mastromb in forum Programming
    Replies: 1
    Last Post: 01-05-2010, 02:58 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums