Concatenation matrix...Can this be done???

[esseryn]

I am using Access 97.
Let us say I have a table like the following

F1 F2
1 "car" "wheel"
2 "motorcycle" "engine"
3 "truck" "door"

F1 and F2 are field names. One through three are row numbers
What I want to create is a concatenation matrix that has the following
results:
(1,F1)&(1,F2) = "car wheel"
(1,F1)&(2,F2) = "car engine"
(1,F1)&(3,F2) = "car door"

(2,F1)&(1,F2) = "motorcycle wheel"
(2,F1)&(2,F2) = "motorcycle engine"
(2,F1)&(3,F2) = "motorcycle door"

(3,F1)&(1,F2) = "truck wheel"
(3,F1)&(2,F2) = "truck engine"
(3,F1)&(3,F2) = "truck door"

If needed, I would like to be able to add more field names. The
resulting concatenations will be appended into another table.
How would I tackle this?

 

[Nikos Yannacopoulos]

I'm afraid you started out wrong... you need to modify your database design.
Think entity classes: Vehicles, Parts etc.
Make a separate table for each class, i.e. a table for Vehicles, another for
Parts, and include a primary key in each (an autonumber field is a good
choice). So, your tables would look like:

tblVehicles
[V_ID] (autonumber, PK)
[Vehicle_Type]

tblParts
[P_ID] (autonumber, PK)
[Part_Type]

Then, you need a third table to store valid combinations:

tblVehicle_Parts
[V_ID]
[P_ID]

joined to the two others (PK not necessary here).

So, a car engine would be represented as an 1,2 record in this table,
assuming 1 is the V_ID for car, and 2 is the P-ID for engine. Of course,
there should be no 2,3 record, unless someone makes a motorcycle with doors.

Use a query with all three tables above to get Vehicle_Type and Part_Type
for valid combinations from tblVehicle_Parts, wherein you could also
concatenate if required.
Bear in mind, though, that the whole idea of relational databases is not to
duplicate stored information. That is to say, if it can be retrieved from
the primary tables by means of a query, then get it that way whenever
required, don't store in another.

HTH,
Nikos

 

[PC Datasheet]

Create two queries and include only F1 in the first query and only F2 in the
second query. Create a third query based on the first two queries. Do not join
the first two queries. In the first column of the third query put:
=[F1] & " " & [F2]

This should give you the concatenation matrix you want.

 

[esseryn]

Thank you.
You provided an elegant solution that I was able to implement in less
than two minutes.

 

[Nikos Yannacopoulos]

Esseryn,

This is indeed a quick trick, but allow me some scepticism on the "elegant"
part. Let me explain:

Keeping the two in different fields in the same table may seem OK as long as
you have a number of vehicle types and THE SAME number of parts, but what if
the numbers are different?

Also, this way you get ALL possible combinations, not just the valid ones,
e.g. now you do have bikes with doors!

I'm not trying play wise guy here, I'm only trying to point out how a quick
and dirty solution may become a painful and messy one further down the road,
as the size of your db increases. A poor design is easy to improve in the
early stages, while later in the application lifecycle, with lots of data
in, queries, forms, reports, automation etc it becomes a nightmare!

HTH,
Nikos

Thank you.
You provided an elegant solution that I was able to implement in less
than two minutes.

"PC Datasheet" <[email protected]> wrote in message

Create two queries and include only F1 in the first query and only F2 in the
second query. Create a third query based on the first two queries. Do not join
the first two queries. In the first column of the third query put:
=[F1] & " " & [F2]

This should give you the concatenation matrix you want.


--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com