I try again

sms23

Registered User.
Local time
Today, 20:26
Joined
May 28, 2002
Messages
19
No response to my earliar post. Perhaps not clear.I will try again.

have a table with called Info with following fields

Code Weight Date Sh[/B]ip #

Now we get this text file fixed width with the following information

Code Weight date

This is generated by weighing boxes on scale, this is done everyday and have 500 entires. Code is the destination its going . THis is what it looks like

GVA 20 11/11/2004
GVA 15 11/11/2004
LIM 1 11/11/2004
DEL .5 11/11/2004
Del 2 11/11/2004
Del 5/ 11/11/2004

Need to import this in table. Which I can. but also need to add the ship #
Meaning that if two GVA were imported in the table then Ship # would be 1 and 2. Del would get assigned 1,2,3 respectivly. The next day same process and if we imported 4 GVA records then they should get ship # 3,4,5,6 .

Thanks
 
First, avoid double posting. Just re-post under the same thread to keep it active. I'm less likely to ignore you than some folks that get totally warped over double posts.

Second, give us some time to read your post and think about it before you decide that you got no takers. Remember, we are all volunteers who visit the forums only when our real jobs allow it.

Now, to your question...

Your problem is that this is done easier in multiple steps than in a single step done on-the-fly. I'm going to advise you along those lines.

First, import your raw data to a temporary table. Pre-define the fields in the table if you wish - the import wizards allow you to specify new tables or existing tables. Just remember, if you use an existing table for the temp copy of your data, you must erase the previous day's data load before importing the next day's data.

Next, in your final table, have your raw data fields and an extra field for your shipment number. From the temp table, do an APPEND query to the final table. For the column that imports your Shipment Number, you can use something like this:

Nz( DMax( "[ShipmentNumber]", "final-table-name", "
Code:
 = """ & [Code] & """" ), 0 ) + 1

I think I got the quotes right, and they ARE important here.  What this does is two-fold.  First, if you have other shipments on record, it finds the maximum shipment number currently in the table, adds one to it, and stores that in your new record's shipment number.  BUT if there was no previous shipment of this, the DMax might return a null.  (Might be zero instead.)  So the alternative argument to Nz (the explicit 0) is returned.  And you add 1 to 0 to find that it is the first shipment of that code item.

Possible alternative syntax not involving substitution, supposing you have done the import to table TempTable and the final append is to FinalTable.

Nz( DMax( "[ShipmentNumber]", "final-table-name", "[FinalTable].[Code] = [TempTable].[Code]" ), 0 ) + 1

The key here is to realize that your import wizard will do ALMOST everything right for you, but it won't apply that numbering scheme because you can't get inside the query that actually gets built for the import operation.  So do everything else to a temp table and do the last part in the Append query I described. That way, you retain full control over the operation.
 
Last edited:
Thanks for the reply. Took a while to get it setup like you suggested. but what is happening is that When I first imported the stuff from temp table the Shipment numbers were all 1. Meaning that I had 5 ASB's in the temp table and they all got assigned ShipmentNumber 1 and also for the other destinations same thing. 2nd time I ran the querry the new additions were all assinged shipmentnubmer 2. And I did not have any entries in the Final table to start with.

Cheers
 

Users who are viewing this thread

Back
Top Bottom