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?
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?
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
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.
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.
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.
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.
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.
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.
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.
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
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.
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.
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.
tagteamQuery
I reformatted and am reposting your query SQL from post #10 for readability:
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?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]));