Please Help (1 Viewer)

contfe

Registered User.
Local time
Today, 02:58
Joined
Jul 18, 2013
Messages
10
Hello, I need help...!

I'm new to using forums for assistance but I've been doing this :banghead: at work now for a couple weeks...

Its hard to explain so I'll start form the beginning. (I hope someone can read it all and help! :()

I'm getting a .csv file from UPS which I need to make into a .csv file to upload to KWM's "tracking uploader".

Here is the main issue: one order can have multiple tracking numbers. To account for this KWM has a column requirement on their upload .csv file called LineNumber which list("autonumber"...!?) the tracking#s from 0 up for every order#.

What I've done:
  1. tblNewTracking: where I upload the .csv file from UPS
    Order#​
    Tracking#​
    Date​
    Carrier​
  2. tblOrder#: lists all orders uniquely as an FK
    Order#<FK>​
  3. tblMaster: List the unique ID and all the information I upload from the .csv's
    ID<FK>​
    Order#​
    LineNumber​
    Tracking#​
    Date​
    Carrier​

I created two queries which I used to make the tblOrder & tblMaster then later changed to append queries to the same:
  1. qryOrder#: Groups the Order numbers and it append them to the tblOrder#
  2. qryNew2Master: Appends all the information from tlbNewTracking to tblMaster and it creates a unique ID ([Order#]&[date]&[tracking#]) I use as the FK on my tblMaster.

The relationships I created are tblOrder#.Order# >>one-2-many>> tblMaster#.Order#

I created the tblMaster separately from the tblNewTracking because the .csv records from UPS overlap, this way I can have the master with all unique values.

Back to the issue at hand: LineNumber!

LineNumber needs to list starting at 0 the tracking numbers per each order number, like:

Order# LineNumber Tracking#
a1 0 a3a3sd21f6a
a1 1 a6a51d3a5sf
a2 0 a6s5df46a5s
a3 0 s65f4gs65df
a2 1 a6sd5f4a65s
a1 2 a65adfa65sd
a3 1 as65df4a65s


What can I do?
 

StarGrabber

Junior App. Developer
Local time
Today, 07:58
Joined
Oct 21, 2012
Messages
165
I would like to help but I still have some understanding problems:

1. How do you "upload" the UPS data into "tblNewTracking"?

2. How do you make the .csv file for KWM?

3. ".csv records from UPS overlap". Does this mean you get some records twice?

According to the fields of "tblNewTracking" I assume a record of the UPS file has four values, but how many "columns" has a .csv file for KWM? Five, namely "LineNumber" additionally?

If not, can you display 4 to 5 complete, randomized UPS records and the corresponding/resulting KWM records?
 

contfe

Registered User.
Local time
Today, 02:58
Joined
Jul 18, 2013
Messages
10
Thank you for your interest in helping me.

1. How do you "upload" the UPS data into "tblNewTracking"?
I use the Wizard to import data from a text file I get from UPS

2. How do you make the .csv file for KWM?
I make a query from the master and I export it. I'm making a field that tells me if the record has already been exported and using it to filter those records out.

3. ".csv records from UPS overlap". Does this mean you get some records twice?
Yes, the files I'm getting from UPS overlap one to two days to ensure we do not miss a record they imputed shortly after I downloaded the .csv

here are the steps I follow:
1. Download .csv
2. upload to tblNewTracking
3. query append: qryOrder#
4. query append: qryNew2Master
5. <insert missing step>
6. query to display data to export
7. export query
8. delete record in tblNewTracking

According to the fields of "tblNewTracking" I assume a record of the UPS file has four values, but how many "columns" has a .csv file for KWM? Five, namely "LineNumber" additionally?

the .csv file I download from ups has multiple columns, but I only need to use 4. The .csv file for KWM does require 5 columns... the extra one is indeed the "LineNumber" I'm having issues with

If not, can you display 4 to 5 complete, randomized UPS records and the corresponding/resulting KWM records?

UPS:
Tracking# Reference# Date Ship2Name Ship2City Ship2State Ship2Country Service
1Z23456789 1234 7/20/2013 Customer Name 1 Address 1 NY USA UPS
1Z23456790 1235 7/20/2013 Customer Name 2 Address 2 TX USA UPS
1Z23456791 1236 7/20/2013 Customer Name 3 Address 3 FL USA UPS
1Z23456792 1237 7/20/2013 Customer Name 4 Address 4 CA USA UPS
1Z23456793 1238 7/20/2013 Customer Name 5 Address 5 NY USA UPS
1Z23456794 1239 7/20/2013 Customer Name 6 Address 6 TX USA UPS
1Z23456795 1240 7/20/2013 Customer Name 7 Address 7 FL USA UPS
1Z23456796 1241 7/21/2013 Customer Name 8 Address 8 CA USA UPS
1Z23456797 1234 7/21/2013 Customer Name 1 Address 1 NY USA UPS
1Z23456798 1235 7/21/2013 Customer Name 2 Address 2 TX USA UPS
1Z23456799 1236 7/21/2013 Customer Name 3 Address 3 FL USA UPS
1Z23456800 1237 7/22/2013 Customer Name 4 Address 4 CA USA UPS
1Z23456801 1238 7/22/2013 Customer Name 5 Address 5 NY USA UPS
1Z23456802 1237 7/22/2013 Customer Name 4 Address 4 CA USA UPS
1Z23456803 1238 7/22/2013 Customer Name 5 Address 5 NY USA UPS
1Z23456804 1239 7/22/2013 Customer Name 6 Address 6 TX USA UPS
1Z23456805 1241 7/22/2013 Customer Name 8 Address 8 CA USA UPS
1Z23456806 1234 7/23/2013 Customer Name 1 Address 1 NY USA UPS
1Z23456807 1235 7/23/2013 Customer Name 2 Address 2 TX USA UPS


KWM:

Order# LineNumber Tracking# Carrier
1234 0 1Z23456789 UPS
1235 0 1Z23456790 UPS
1236 0 1Z23456791 UPS
1237 0 1Z23456792 UPS
1238 0 1Z23456793 UPS
1239 0 1Z23456794 UPS
1240 0 1Z23456795 UPS
1241 0 1Z23456796 UPS
1234 1 1Z23456797 UPS
1235 1 1Z23456798 UPS
1236 1 1Z23456799 UPS
1237 1 1Z23456800 UPS
1238 1 1Z23456801 UPS
1237 2 1Z23456802 UPS
1238 2 1Z23456803 UPS
1239 1 1Z23456804 UPS
1241 1 1Z23456805 UPS
1234 2 1Z23456806 UPS
1235 2 1Z23456807 UPS

(These two tables are Tab delimited)

Thank you for your help, I'm looking forward to any suggestions.
 

StarGrabber

Junior App. Developer
Local time
Today, 07:58
Joined
Oct 21, 2012
Messages
165
Thanks for the detailed explanation, but I'm missing the 5th column in the KWM table. Is it the date? I need the column order to provide a solution.
 

StarGrabber

Junior App. Developer
Local time
Today, 07:58
Joined
Oct 21, 2012
Messages
165
OK, got it.

One more question: "These two tables are Tab delimited". Do I understand you right, both the imported and the exported csv file are tab delimited?
 

contfe

Registered User.
Local time
Today, 02:58
Joined
Jul 18, 2013
Messages
10
nop, sorry the tab delimited was for the example in the post above. When I export is a .csv.... comma delimited
 

StarGrabber

Junior App. Developer
Local time
Today, 07:58
Joined
Oct 21, 2012
Messages
165
See my suggestion in the file attached.

It is based on a different table structure/data model than yours. The field "Exported" is not used - until now. But once the KWM file is uploaded successfully, you can run an action query which sets this field to "True".

The table "tblPaths" is of little importance. Its purpose is to keep the initial directories for the file/folder dialogues, only.

I tried to keep the code simple and comprehensible/selfexplaining. - I hope you can get through. If not, maybe this is another push for you to "learn fast". :)
 

Attachments

  • CSVTransformer.accdb
    528 KB · Views: 80

contfe

Registered User.
Local time
Today, 02:58
Joined
Jul 18, 2013
Messages
10
Thank you, Thank you

I'll give it a good look at this.
 

StarGrabber

Junior App. Developer
Local time
Today, 07:58
Joined
Oct 21, 2012
Messages
165
You're welcome.

It would be nice of you to let me know about your experiences in daily use, that is, if it meets your requirements.
 

contfe

Registered User.
Local time
Today, 02:58
Joined
Jul 18, 2013
Messages
10
Thank you StarGrabber, for your work on my problem.

While the use of VBA is the direction I assumed I had to take, the file you provided me is not accomplishing what I need.

I'm attaching what I've done in hopes that it will put my problem in context, If you have time can you please take a look at it an see if there is a VBA code I can add that would finally fill in that LineNumber field accordingly?

I'm still learning VBA coding and see the file you sent me as a measuring stick, and hope one day to be able to replicate it.

To clarify:

  1. The file has a csv which I append to tblNwTrckr
  2. I have a form I need to add truck orders manually
  3. I use the qryAllOrdrs to append all new order numbers to be able to group together (I need this since I'll have someone in accounting checking this for to before sending Invoices)
  4. I use the qryNw2Mstr to add all new tacking numbers form the csv/truck orders to the master sheet (Note that i custom created an ID to avoid duplicates using the tracking, date, and order number)
  5. I need to fill in the LineNumber as Mentioned in my previews post
  6. Finally I'll use a query to export the format I need to upload to KingWebMaster. (after filtering any previously uploaded tracking numbers)

Please let me know if you need me to clarify anything here.
 

Attachments

  • TrackingZip.zip
    860 KB · Views: 88
Last edited:

StarGrabber

Junior App. Developer
Local time
Today, 07:58
Joined
Oct 21, 2012
Messages
165
I have taken a look at the file you've attached. There were some surprises:

1. The UPS file has column headers. I was mistaken, thinking the headers you've posted were for illustration only.

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.

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".

My 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?

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]

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!

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?

Do you know the difference between a primary key (PK) and a foreign key (FK)? ;)

I assume, the column headers of the KWM file are exactly as named in "tblMstrTrckr". Am I right?


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


[B]RecordExists = blnRecordExists[/B]

If blnRecordExists Then
    fAppendLineToTables = True
    Exit Function
End If
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.
 
Last edited:

contfe

Registered User.
Local time
Today, 02:58
Joined
Jul 18, 2013
Messages
10
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!!!!
 

StarGrabber

Junior App. Developer
Local time
Today, 07:58
Joined
Oct 21, 2012
Messages
165
I see, I see. Now I'm starting to understand the context in which your database acts. But I would like to have more information.

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 procedures in your company are as follows (please correct me if I'm wrong):

1. Orders are sent out. For those sent through UPS you receive - in certain intervals - a csv-file.

2. The csv-contents is imported into table "tblMstrTrckr", a csv-file for KWM is generated (with line numbers added manually) and uploaded.

3. The field UpLoaded of "tblMstrTrckr" is set to "True". (How? I don't think one by one.)

4. The accountant queries "tblMstrTrckr" by the field OrderID and makes sure, all tracking numbers are uploaded. In that case an invoice is sent to the customer.

For those orders not sent out through UPS you enter all the information using "frmNewTrk#". Does this mean you don't receive any other file with tracking information of the respective carrier? After you've cleared "tblNwTrckr" you run your append queries in order to obtain an equivalent record ID. What happens next? Are these records uploaded to KWM as well (in csv format)? Separated from the UPS records?

Do you split orders to two (or more) different shipping companies?


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...
I didn't realize that one tracking number can apply to many order numbers. Therefore I suggest another table structure (see file attached). The reason for that is, I don't think it's a good idea to use a field of text format as "ID". Either you take a "Long Integer" or you take a "Replication-ID" (sample: {42176361-47B8-499B-9219-44AF4BC85480} ).

By the way, you were right, the data type of field "CarrierCode" should be "Number". But for that you need another table (e.g. "tblCarriers") which provides a key to be stored in the field "CarrierCode" (implemented in attached file, too. Please note, I've renamed "tblTrackingNumbers" to "tblTrackings"). I assume you've heard something about database normalization. If not... http://en.wikipedia.org/wiki/Normalization_(database). Test the data using "qryTrackingsByOrder" thoroughly, paying special attention to the line numbers. They increase per csv-file generation, not per order number. Is this OK? (Please note, the data are exactly the same as in "CSVTransformer.accdb" of post # 8).


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
Do I understand you well, in the original UPS csv-file you get order number values like "50001", "50010", 50013" and so on, and not "50108 & 50190" or "50211 & 50228 & 50299 & ...n", right? If so, why do you have to convert this using Excel?
You see, I'm a bit confused.


After writing hundreds of code lines I think we should use them and not only focus on the line number issue and proceed the old way afterwards. Let's do it right!

Thank you for all your help! ....Oh, and I also found the Thank you button!! so Thank you!!!!
I don't wanna say I don't care about how many times people presses that button, but - and you can convince yourself of that by having a look to other posts of mine - that my primary goal is to provide a solution. Please don't press that button again until we've reached the goal. ;)
 

Attachments

  • TableStructure2.accdb
    496 KB · Views: 82
Last edited:

contfe

Registered User.
Local time
Today, 02:58
Joined
Jul 18, 2013
Messages
10
Ok, after downloading your latest example and duplicating it, I'm still running into the same missing field... So maybe my method is flawed...?

How would you tackle this?

I'm attaching a file with the .csv file from UPS (I had to change some information and omit other... but the formatting of the file is still the same) you'll notice that the for every tracking number there are anywhere from 1 to 3 reference numbers divided with the "|" symbol.

I've attached what it should end up looking like. While I can make this happen in Excel, when the information piles up Excel cannot handle it, so I was hoping to use Access and make it more streamline (IE: get myself out of the equation)

I also need to create a report for accounting. The third file is what I'm currently making for them.

Thank you for your patience and input.

I'll have to buy you a beer if you happen to come by north Florida.
 

Attachments

  • trackingSampleFiles.zip
    302.1 KB · Views: 87

StarGrabber

Junior App. Developer
Local time
Today, 07:58
Joined
Oct 21, 2012
Messages
165
Before I can answer your questions of post # 15, I need you to answer my questions of # 14. Unfortunately you didn't. But I would have needed that information to go ahead.

I've attached a file which provides you the line number. And that's what you've asked for originally ("Back to the issue at hand". It's not a solution as I meant it but I think it will serve you. If not, please report).

Instead of running "qryNw2Mstr" press the button of "frmNew2Master".
 

Attachments

  • LineNumbersOnly.accdb
    576 KB · Views: 80

Users who are viewing this thread

Top Bottom