Help I need to create a field that auto counts up based on another field's data....

[contfe]

Hello,

So I need to make a .csv file with order tracking information. I get the database already from a .csv but I'm using access to store it an manipulate it (too much information for excel). These are the fields I get:

Tracking #
Order ID
Date


Carrier

But I need to make this into:

Order ID
LineNumber
Carrier
Tracking Number

So I need assistance with the LineNumber field. This field is has to be auto-number based on the Order ID, IE: If an order has multiple packages and they were sent on different trucks or carriers, I would have multiple tracking#s for a single Order ID, the LineNumber has to organize them by means of an counter that counts each new tracking number from 0 up, like:

Order ID	LineNumber	Tracking#
10001	0	abc
10002	0	abc
10002	1	abd
10003	0	abd
10002	2	abe
10003	1	abe

....Help!

this is what I've done so far:

1. I created a table with all the fields I receive.
2. I created a query that appends into a table all my OrderIDs without repetition
   
   1. this table only has one field, an FK, and has a one-to-many relationship to my master table OrderID.
   
   
3. I created a second query that appends all the fields plus it creates a unique value by conniving the OrderID with the Date and the Tracking#
   
   1. this is my FK to avoid adding a record twice since the original .csv laps with the previews one I get.
   
   
4. I made a query from which I intend to export the .csv I need to submit.

Thank You!!

[June7]

You need this LineNumber for the export to CSV?

Can run another UPDATE action that will populate the LineNumber field. Review https://www.accessforums.net/access/...eld-34175.html

[contfe]

I'm still lost,

Following the threads there was a suggestion of using DCount, but I had discount this since I'm uploading the records all at once with a .csv file upload...

Maybe I missed something, plz help!

[June7]

Can't create the value during import if you are using the import wizard. Run the UPDATE to populate that field after completing the import.

If you are not using the import wizard, how are you importing the csv data?

[contfe]

Yes to Wizard, I figured that i would have to add this value to the database once is added to the master or when I use the query to append the new items to the master.

My question is how?

Is there an equation that I can use that would add these value as I append the information to the master? (keeping in mind that I got to use the records already in the master to come up with the next value on several of these records)

or is there a way to use vba to add these value? (I'm extremely new at VBA which is probably why I'm so lost...)

Thank you for your help, I'll look forward to your suggestions.

[June7]

You are not importing directly to 'master'?

No, the counter field cannot be populated using Access INSERT query object.

Once records have been imported (appended) to table, run an SQL action to UPDATE the counter field. The method using DCount() as shown in referenced link is the only way I know a query can do that.

Yes, VBA could accomplish but that may be more complicated than the UPDATE sql, especially if you are not experienced programmer.

[contfe]

can you walk me down the process or link me to a board on the subject?

[June7]

The referenced link is the best 'walk through' I can offer for query method. I have no idea what you mean by 'board' - another forum?

[contfe]

Yes Forum, Which should I look for?

[June7]

I don't know any other specific forum for this issue.

If the given reference does not help (what do you not understand?), you can Google to try and find more.

[contfe]

Thank you!

...I'm still lost but you help give me some direction

Thank you!