Append Query: Parse through string & append to each row

[compooper]

Here's the deal:
I have two tables: table1 & table2

table1 & table2 have the same orderids and thus are related to one another

For example, table1:
ORDERID FNAME LNAME DOB SvcCode SvcDescription
0001 Bob Smith 01/01/2011 123, 456, 789, 1010 Cat, Dog, Watermelon, Chinese Lantern - ordered by me
0002 Jane Doe 01/01/1900 123, 989, 1010, 1212 Cat - She's so cute, Beach, Chinese Lantern, Chicken

Then table2 would say:
ORDERID FNAME LNAME SvcDescription
0001 Bob Smith Cat
0001 Bob Smith Dog
0001 Bob Smith Watermelon
0001 Bob Smith Chinese Lantern - ordered by me
0002 Jane Doe Cat - she's so cute
0002 Jane Doe Beach
0002 Jane Doe Chinese Lantern
0002 Jane Doe Chicken

I have created an append query that takes information from both tables and merges them together.
I.E. MergeTable would look something like this:

MergeID CompanyID Fname Lname Dob SvcCode SvcDescription
1 co1 Bob Smith 01/01/2011 Cat
2 co1 Bob Smith 01/01/2011 Dog
3 co1 Bob Smith 01/01/2011 Watermelon
4 co1 Bob Smith 01/01/2011 Chinese Lantern - ordered by me
5 co1 Jane Doe 01/01/1900 Cat - she's so cute
6 co1 Jane Doe 01/01/1900 Beach
7 co1 Jane Doe 01/01/1900 Chinese Lantern


8 co1 Jane Doe 01/01/1900 Chicken

The problem I am having is trying to extract the SvcCode from table1.
At first I was trying to use a datamapping and match the codes using a list I created with the SvcDescription, but there are many irregularities that way.

Sometimes the SvcDescription is mis-spelt, additional comments are added & there is always the possibility that the svcdescription and svccode is not my list.

So now I think the easiest way is to parse through the SvcCodes and fill in the blanks.

Not exactly sure how to do this.
All help is appreciated

[June7]

You want to normalize the data in Table1. So those 2 records will become 8 records. Are the service descriptions the same in both tables for each of the ORDERID values?

Why do you need to merge with Table2? It doesn't offer any additional data.

From the example data for Table1, it appears that the service codes and service descriptions are in order with respect to each other. If that is the case, parse them in that order and save. No data mapping needed. Worry about spelling corrections later. Are there always 4 codes and 4 descriptions? Because of irregular length of the codes (3 or 4 digits) and the description string, think I would use VBA to parse the comma delimited data.

How big is Table1?

[rpeare]

if June is correct and you want to break up both the svccode and the svcdescription you'll have to do it with VB script.

What june means by normalizing is that you appear to be carrying a lot of duplicate information from table to table which you do not need.

For instance (just based on the information you're showing) I would have the following tables:

tblPeople
PeopleID FirstName LastName DOB
1        Bob       Smith    1/1/2011
2        Jane      Doe      1/1/1900

tblServices
SvcID SvcCode SvcDesc
1     123     Cat
2     456     Dog
3     789     Watermelon
4     1010    Chinese Lantern
5     989     Beach
6     1212    Chicken

tblOrders
OrderID  OrderNumber PersonID
1        0001        1
2        0002        2

tblOrderItems
OrderItemID OrderID SvcID PeopleID
1           1       1     1
2           1       2     1
3           1       3     1 
4           1       4     1
5           2       1     2
6           2       5     2
7           2       4     2
8           2       6     2

All of my ID fields are for the purposes of this example autonumber fields.

In this way you eliminate the variability of people typing in extra comments or misspelling words in your data entry, you can limit their choices (on services) a pre-determined list which they can not deviate from. You can provide for comments (i.e. 'ordered by me' 'it's so cute') if you want to instead.

All this being said there is a way to parse your information but it will involve putting the information into a different table before you can use it and finding out whether it's possible to have fewer or more than 4 entries per line, that's pretty critical when programming a solution. Or at the very least knowing what the maximum number of SvcCodes/SvcDescriptions one can have in those fields (and assuming nothing in the svcdescriptions has a comma)

[compooper]

Thanks for the replies.

table1 & table2 are excel sheets I receive on a biweekly basis from another company.
Thus, I can't change their setup.

What I am trying to accomplish is, take the information I deem important and append them to a different table (tblBIG)


I have been able to create a procedure that does this for invoices from other companies.
1. import the file
2. append the neccessary information
3. delete the imported file

I have seperate tables for client, service and order data, which is related to tblBIG.
So its pretty normalized... i think.

table1 has all the information I need.
table2 has the svc_description seperated with their corresponding prices so it makes life easier.
table1 unfortunately gives me a combined price for all of the services, so its nearly impossible to figure out each products price.
But if I could figure out how to parse through svc_codes, then I will only need to use table2 for the prices.

the svc_codes & svc_description have matching & unknown amount of commas/svcs. They are in order.
There is no maximum number of svcs one can have, minimum would be 1.

How do you parse through and append in order?

[June7]

Skeleton of pseudocode:

1. Open recordset of source table
2. Open recordset of destination table
3. Loop through source recordset
4. Use Split function on each of the multi-value fields to parse the values to one-dimension arrays (no need to pre-determine how many values in the set), requires consistency in structure - comma used only to delimit values and not otherwise in the string and values always in pairs
5. Loop through the arrays and save values to records of destination

[compooper]

Okay thanks.
I'll give that a go in a bit.

Quick side question:
How do you EXIT an Entire Function.

For example:
Function1 calls function2.
Function2 has an error handler, that says exit function
but then Function1 proceeds after.

I want both function1 and function2 to end

[June7]

If Function 2 errors have it set and return to the calling function a value like "False" or "None" or "Fail". Then in Function 1 call Function 2 with:
If Function2 = "Fail" Then
'do this
Else
'do this
End If

[rpeare]

Here's an example just open the form in the database and click the button look at the tbl_Orders and tbl_OrderServices before and after.