Surprises:
1. The UPS file has column headers. I was mistaken, thinking the headers you've posted were for illustration only
Yes they are part of the file
2. The file import (or "upload", following the steps you described in post # 3) does not work. After the removal of the column header line it does.
Yes, I'm sorry I forgot to mentioned this step in the wizard
3. Access doesn't like to execute the query "qryNw2Mstr" due to type conversion errors. The reason is the data type of field "CarrierCode". If you want to import text (e.g. "UPS"), then it should be "Text", not "Number".
...!!! That's what it was... Thank you!!!.... I feel dumb...
Questions:
"I have a form I need to add truck orders manually"
What please are 'truck orders'? Can you give some examples? Why 'manually'? Does this refer to "5. <insert missing step>" of post # 3? And I can't find a field on your form "frmNewTrk#" with such a contents. What part of this am I missing?
Sorry, I do not mean to create confusion. This database has two purposes: first it helps me convert the data to the format needed for the KWM Upload. The second is to create a reference for our accountant of what orders (by Reference#) have been shipped out, as well as a few details about them (Like the address, the time, and what carrier was used.) Since not every order is being sent out through UPS (UPS ~ 85-90%) there are a few orders that would need to be entered manually. For this I created a very rough form that will fine tune after I work all the other details.
The missing step is to create the numbering sequence (count) that relates to the tracking# in relationship to their perspective orders.
You say, you want "to be able to group together". Group by what? [Edit:] Please don't say "by reference#" because I know this already and my file accomplishes that in precisely the same way as yours. So in this regard, there is nothing more to clarify. Nevertheless I ask you this to make sure I'm not missing something.[/Edit]
I don't think you are. As I explained above, I'm having my accountant look at the order# of her invoice and compare it to the Reference#'s in the database to verify the order was shipped before sending an invoice to our customers.
In regards to your "custom created ID to avoid duplicates using the tracking, date, and order number": in "UPScsvSample.csv" all tracking numbers are unique!
Yes, UPS creates unique tracking orders per shipping, but in one shipping container I might have two packages with different order ID's which to me are also unique. This results in this database having duplicate Order# and duplicate Reference#'s...
Can you explain the significance of
Expr1: tblNwTrckr![Reference#] & Year(tblNwTrckr![TrDate]) & IIf(Format(tblNwTrckr![TrDate],"j")=2,"0" & Format(tblNwTrckr![TrDate],"j"),Format(tblNwTrckr![TrDate],"j")) & tblNwTrckr![Tracking#] ? Is there a 'secret formula' contained? Why such costs?
With this formula I hoped to account for the duplication of the ID's I mentioned above. Here is an example:
Order#: 50001, 50002, 50003, 50004 <= Unique to me
Tracking#: 1z00001, 1z00002, 1z00003 <= Unique to UPS
However, this is what I get:
Tracking# | Order# | DateSent
1z00001 | 50001 & 50002 | 7/27/13
1z00002 | 50002 & 50003 | 7/28/13
1z00003 | 50001 & 50003 | 7/29/13
Using excel I'm converting this into:
Traking# | Order# | DateSent
1z00001 | 50001 | 7/27/13
1z00001 | 50002 | 7/27/13
1z00002 | 50002 | 7/28/13
1z00002 | 50003 | 7/28/13
1z00003 | 50001 | 7/29/13
1z00003 | 50003 | 7/29/13
So the formula is my attempt at creating an unique ID I can use as the PK for this database to avoid Duplication in the records:
Traking# | Order# | DateSent | ID(PK)
1z00001 | 50001 | 7/27/13 | 50001132081z00001
1z00001 | 50002 | 7/27/13 | 50002132081z00001
1z00002 | 50002 | 7/28/13 | 50002132091z00002
1z00002 | 50003 | 7/28/13 | 50003132091z00002
1z00003 | 50001 | 7/29/13 | 50001132101z00003
1z00003 | 50003 | 7/29/13 | 50003132101z00003
This expresion:
[tblNwTrckr]![Reference#] & Year([tblNwTrckr]![Date]) & IIf(Format([tblNwTrckr]![Date],"y")=2,"0" & Format([tblNwTrckr]![Date],"y"),Format([tblNwTrckr]![Date],"y")) & [tblNwTrckr]![Tracking#]
Should Equate to:
Order# & Year(DateSent) & DayOfYear(DateSent) & Tracking#
>>>>>Did not know where to insert this when reviewing the message so here it is<<<<<
While I'm interested in seeing any improvements that I can make to this database, my main concern at the moment is to be able to make the "LineNumber", here is what I'll need it to do:
OrderId | LineNumber | CarrierCode | TrackingNumber
50001 | 0 | UPS | 1z00001
50001 | 1 | UPS | 1z00003
50002 | 0 | UPS | 1z00001
50002 | 1 | UPS | 1z00002
50003 | 0 | UPS | 1z00002
50003 | 1 | UPS | 1z00003
Furthermore today when I download the UPS .csv file I'll get something like this (Within the context of the example above)
Traking# | Order# | DateSent
1z00003 | 50001 | 7/29/13
1z00003 | 50003 | 7/29/13
1z00004 | 50002 | 7/29/13
1z00005 | 50004 | 7/30/13
1z00005 | 50006 | 7/30/13
1z00006 | 50004 | 7/30/13
1z00006 | 50005 | 7/30/13
1z00006 | 50006 | 7/30/13
So I'll need to send another file with this:
OrderId | LineNumber | CarrierCode | TrackingNumber
50002 | 2 | UPS | 1z00004
50004 | 0 | UPS | 1z00004
50004 | 1 | UPS | 1z00006
50005 | 0 | UPS | 1z00006
50006 | 0 | UPS | 1z00005
50006 | 1 | UPS | 1z00006
>>>>>Hope this clarifies things further, and Thank you<<<<<
Do you know the difference between a primary key (PK) and a foreign key (FK)?
Primary Key, unique ID to be used for indexing purpose on the table your information is contained. Foreign Key, a primary key on another table that is somehow related to the table the information is stored. Please correct me if I'm wrong.
I assume, the column headers of the KWM file are exactly as named in "tblMstrTrckr". Am I right?
Yes!
One more thing: I've checked the procedures once more and found an error regarding to overlapping UPS records. The variable "RecordExists" which is created in "fImportUPSFile" and passed to "fAppendLineToTables" ByRef is not reset/updated there. It had the effect, that no new KWM file could be created, although there were new records. You can rectify that by adding the bold line of the code fragment below. You have my apologies.
Code:
If lngErrorNo <> 0 Then
Exit Function
End If
RecordExists = blnRecordExists
If blnRecordExists Then
fAppendLineToTables = True
Exit Function
End If
I'm assuming this is a fix to the file you sent me, I'll take a look at it.
I'm sorry my file does not do what it should do. I tried to write the code in compliance with your good and detailed explanation in post # 3. - However, I think after some further efforts we can reach your goal.
Thank you for all your help! ....Oh, and I also found the Thank you button!! so Thank you!!!!
)
