Creating a difficult qry

[Aubreylc]

Can someone give me some guidence on creating a qry? I have no control of how the original data gets entered into the table.

• I have a table named allOrders and a query named repOneOrders.
• The table data has values that start with either: F, W, P, M, and/or a numerical value.
• The values starting with: F, W, P, and M, are numbers that have significance within the company.
• The numerical values are for external use.

I need to be able to sort the data into the the query named repOneOrders.

[llkhoutx]

qry-repOneOrders has a row in the Query design window to specify what colkumns are to to sorted and whether it's an ascending or decendng sort.

[Aubreylc]

I'm not sure I understand what you mean.

The previous illustration is a rendering --not an actual qry or table--. The Sample below is the actual data. The source is from UPS which shows all activity assosiated with our account.

• Each record is a shipment from different vendors to a customer.
• Every time a vendor ships a package, they type either our purchase order number, or their own reference number, or both.
• There is no telling what field within a set range, the number will show up in from day to day.
• Each record though is important and cannot be discarded in the qry.
• The qry needs to have any number within the highlighted fields put into column A, if it has a letter prefix, and column B if it is just an integer.

[llkhoutx]

General comments:
1. Do not ever put blank spaces in field names. Spaces exacerbates VBA and SQL coding.
2. Allowing a user to data in any one of multiple fields is an extraordinarily bad design and an absolute recipe for disaster. Same violates "normalization" rules. Lookup "database normailization" in Access Help or Google.
3. You should specify which field takes precedence, purchase order number or reference number. I suggest that you make purchase order the dominate field.

Post the data you've posted in a database. I'll build you a query, but I won't enter your data.

[Aubreylc]

Thanks for the suggestion about the spaces and I appreciate the time you took to reply. If I worked for UPS I would be able to make changes at the source of the data. This is just a case of having to work with what's provided.

Let me start by explaining what this data is. I am a drop-shipper with 600+ vendors. Each day, a number of those vendors will ship upward of 10,000 packages. All of the shipments are made on a 3rd party billing account where I pick up the bill, and also answer order status inquiry's from customers. Obviously, the only way to track these packages is to get the data from UPS. So, UPS provides this data through .CSV files which are then appended.

When vendors ship packages with UPS, they either use UPS software or third party software to enter shipping information, and generate a tracking number for each package shipped to a customer.

To accommodate the array of shipping software available, UPS provides a range of fields within each record that will allow shipping associates to enter data that pertains to a specific package. The vendors shipping associate at one company might enter a PO# in field A of a record, field B, or in some cases, both. It's impractical to survey 600 of our vendors to determine what shipping software they are using and then demand they fill out certain fields.

The PO# that we give to each customer starts with either, W, P, A, F, M.


The idea of just "blowing things up" and starting from scratch sounds appealing but it will have no effect on the data source.

[llkhoutx]

Post the table in as an Excel sheet or a table in an Access database and I'll build you a query, but I won't enter your data.

columns 2 and 3 involved embedded testing and because of their complexity, the result should be determined in functions (place code in a module), although they could be computed directly in the query:

Column2 field should be:

YourNameForColumn2: pfcnBuildCol2Aplha([Reference Number Value 1], [Reference Number Value 2], [Package Reference Number Value 1], [Package Reference Number Value 2]) 

Column 2 function

Public Function pfcnBuildCol2Alpha(Arg1 As String, Arg2 As String, Arg3 As String, Arg4 As String) As String
    'is 1st character of any argument an alpha character
    'pfcn prefix implies a public function
    'convert 1st character to uppercase to avoid checking for lower case
    'numeric 1st character will of course, not convert
    If UCase(Left(Arg1, 1)) >= "A" And UCase(Left(Arg1, 1)) <= "Z" Then GoTo Alpha1
    If UCase(Left(Arg2, 1)) >= "A" And UCase(Left(Arg2, 1)) <= "Z" Then GoTo Alpha2
    If UCase(Left(Arg3, 1)) >= "A" And UCase(Left(Arg3, 1)) <= "Z" Then GoTo Alpha3
    If UCase(Left(Arg4, 1)) >= "A" And UCase(Left(Arg4, 1)) <= "Z" Then GoTo Alpha4
    '1st character is not an alpha character
    pfcnBuildCol2Alpha = ""
    GoTo Exit_Exit
Alpha1:
    pfcnBuildCol2Alpha = Arg1
    GoTo Exit_Exit
Alpha2:
    pfcnBuildCol2Alpha = Arg2
    GoTo Exit_Exit
Alpha3:
    pfcnBuildCol2Alpha = Arg3
    GoTo Exit_Exit
Alpha4:
    pfcnBuildCol2Alpha = Arg4
    
Exit_Exit:
    Exit Function
End Function 

Column2 field should be:

 YourNameForColumn3: pfcnBuildCol2Numeric([Reference Number Value 1],  [Reference Number Value 2], [Package Reference Number Value 1], [Package  Reference Number Value 2]) 

column 3 function

Public Function pfcnBuildCol3Numeric(Arg1 As String, Arg2 As String, Arg3 As String, Arg4 As String) As String
    'is 1st character of any argument a numeric character
    'pfcn prefix implies a public function
    If IsNumeric(Left(Arg1, 1)) = True Then GoTo Numeric1
    If IsNumeric(Left(Arg2, 1)) = True Then GoTo Numeric2
    If IsNumeric(Left(Arg3, 1)) = True Then GoTo Numeric3
    If IsNumeric(Left(Arg4, 1)) = True Then GoTo Numeric4
   '1st character is not an Numeric character
    pfcnBuildCol3Numeric = ""
    GoTo Exit_Exit
Numeric1:
    pfcnBuildCol3Numeric = Arg1
    GoTo Exit_Exit
Numeric2:
    pfcnBuildCol3Numeric = Arg2
    GoTo Exit_Exit
Numeric3:
    pfcnBuildCol3Numeric = Arg3
    GoTo Exit_Exit
Numeric4:
    pfcnBuildCol3Numeric = Arg4
    
Exit_Exit:
    Exit Function
End Function 

I'm sure that someone will come up with a better solution.

[Aubreylc]

I've attached an Access file which contains 2 tables and 1 qry.

1. tbl-RawData: contains the data in its Raw form from UPS.
2. tbl-RepVendor: contains primary names for each vendor which establishes a relationship between our vendors and who they outsource to, and which one of our representatives is responsible for that vendor.
3. qry-Aubrey-Vendors: contains the qry of activity relating to Aubrey's orders.

• The Package and/or Shipment Reference fields within qry-Aubrey-Vendors, will contain at random, Our Purchase order numbers which start with a 1 letter prefix. All other numbers within this range that do not start with a 1 letter prefix are for external purposes.

[llkhoutx]

Have you tried the solution I've already given you?

[Aubreylc]

No, I must have misunderstood you. I thought you said to post a dbase.

Sorry for the confusion,