Two table Conversion

[Neil]

Hello everyone,

I have an issue i cant seem to figure out. I thought it would be easier
to do it through queries but i'm open to any coding suggestions that
may be involved. Just a little background, the best and most accessible
tool I have is MS Access so if programming would be an easier route,
please keep it to VBA. I also have access to vb 6.0 as well as VB.net
but i'm trying to steer away from it as I do not have too much
experience with it and I'm looking for results on an urgency basis.

Anyways to the problem. I have two tables, one is a Conversion Table
(with an ever growing PARTNO list) and the Other our Customer Table.
Here is an example of what they look like:

Conversion Tbl:
PARTNO DESC ALIAS DESC
1111 PEN PE PEN (BLACK INK)
1112 PENCIL PC PENCIL 2.0
1113 ERASER ER ERASER
1114 CLIPS CL PAPER CLIPS BOXES

Customer Tbl:
BILL FNAME BILL_LNAME PARTNO1 QTY1 PARTNO2 QTY2 PARTNO3 QTY3
JOE SMITH 1111 2 1112 1
JACK JOHNSON 1112 1 1113 1 1111 1
BILLY BOB 1114 1 1112 3 1111 3


What I need to do is convert it to 1 table which will look something
like this:
Customer Tbl:
FNAME LNAME PARTNO1 QTY1 PARTNO2 QTY2 PARTNO3 QTY3
JOE SMITH PE 2 PC 1
JACK JOHNSON PC 1 ER 1 PE 1
BILLY BOB CL 1 PC 3 PE 3

I'm planning to do this because it is hard to keep tracking of our ever
growing list of our Product List. Converting it to a common Code is
easier to keep track of and creating reports. Using this conversion
table, we'll be able to easily count how many items were shipped
through our remote warehouse and so on. Again if queries is not the way
to go, I'm open to VBA suggestions.

Thank you in advance for your help.

 

[Tom Ellison]

Dear Neil:

This will do what you say, I believe:

SELECT BILL_FNAME, BILL_LNAME,
(SELECT ALIAS FROM Conversion Co
WHERE Co.PARTNO = Cu.PARTNO1)
AS PN1, QTY1,
(SELECT ALIAS FROM Conversion Co
WHERE Co.PARTNO = Cu.PARTNO2)
AS PN2, QTY2,
(SELECT ALIAS FROM Conversion Co
WHERE Co.PARTNO = Cu.PARTNO3)
AS PN3, QTY3
FROM Customer Cu

The table Customer containing 3 products and quantities is seriously badly
designed. This makes for the repetitive subqueries above. Properly
normalized, you would have 3 rows instead of 3 pairs of columns.

Tom Ellison

 

[Neil]

Thank you for replying Tom. I was afraid the subqueries were the only
option. That's why i was hoping if there was an easier way thru VBA, I
would have taken that path. Can you elaborate on your comment on
designing the Customer Tbl. I'm trying to get a picture of how it would
look and if necessary to make the job easier, I could change the
structure of the table. Just to give you an idea, the Conversion Table
has approximately 900 PARTNO's (basically same products but various
SKU's for them) and the current Customer Table has around 20,000
customers in there. Again being a newbie to SQL and databases, I
appologize for my ignorance.

 

[Casey via AccessMonster.com]

Dear Neil:

Jumping in here and elaborating on Tom's response I would suggest the
following:
Split up your customer table.
The only things you need in your customer table are as follows
Customer Tbl
CustNo Fname Lname
1 Joe Smith
2 Billy Bob
3 Jack Johnson

You would then have a second table added and you could call it what you like
but for now lets just call it
"tblConversionCustomer"
Here you would have the following:

tblConversionCustomer
CustNo PartNo Qty
1 PE 2
1 PC 1
2 PC 1
2 ER 1
2 PE 1
3 CL 1
3 PC 3
3 PE 3

That is how it could be set up, or you could not use the alias and use the
actual partno if you wanted then, incorporate Tom's query in with it. Let me
know if this helps. Doing this normalizes your table and will drastically
improve your table design and flexibility.

 

[Tom Ellison]

Dear Neil:

Do not be apologetic about ignorance. None of us were born with the ability
to created databases. You appear to be doing something about your
ignorance. No one can do better than that!

The 3 products in each row of the Customer table should be placed in another
table, with one row per product. The appearance of this table, and the way
of getting the existing data into it, are showin in the UNION ALL query I
give you below:

SELECT BILL, 1 AS Sequence, PARTNO1, QTY1
FROM Customer
WHERE Nz(PARTNO1, "") <> ""
UNION ALL
SELECT BILL, 2 AS Sequence, PARTNO2, QTY2
FROM Customer
WHERE Nz(PARTNO2, "") <> ""
UNION ALL
SELECT BILL, 3 AS Sequence, PARTNO1, QTY1
FROM Customer
WHERE Nz(PARTNO3, "") <> ""

The column I call Sequence allows you to keep the order of the parts you now
have. It is optional, but I'd recommend keeping it.

Tom Ellison

 

[Neil]

Thanks Casey,

Right after I posted here, I used Tom's query with the real data I had
and it worked great. As I'm new to databases, it's hard for me to see
how to structure tables. For the time being I was just trying to get
the job done but I will try to improve the structure on what you
elaborated. The customer table I had in my original post is basically
from a flat file that we send over to our remote warehouse from which
they ship out packages. Basically for Cust. X they have up to 53 fields
of PARTNO and responding QTY's in one row. The customer table was made
from appending these files.

 

[Neil]

Hi Tom,

Thanks again for replying to this thread. I see the new code sample you
have works towards what Casey was discussing. I used it on the dummy
table and it works well. Thanks for replying.

BTW can any of you shed some light on my other dilemma I have.
Following is the link for it:

http://groups.google.com/group/micr...110a9/996865239624571c?hl=en#996865239624571c

 

[Casey via AccessMonster.com]

Neil:

I am glad that our advice helped you out, and regarding the other dilemma you
have, I honestly have no clue. As I am yet still young to learning Access I
do not know any VBA coding and get confused by the littlest bit of it right
now. That being said, I do hope that Tom can help you out. Your up to bat Tom
;-). Goodluck Neil, Tom. Take it easy!

 

[Neil]

Thanks a lot Casey, I too hope Tom will be able to shed some light on
it. Good luck with your studies.