Creating a Table Based On Two Records And Corresponding Field Values

[tfixnicholas]

I am a beginner at Access, can understand some basic VBA and I have a very tricky problem ahead of me.

I have one table with many records and many fields. For simplicity, here's a mini version.

Building Name	System1	System2	System3	System4
BLD1	System1A	System2C	System 3F	System4A
BLD2	System1B	System2B	System 3C	System4A
BLD3	System1A	System 2A	System 3C	System4C

<tbody>
</tbody>

In my real table there is about 1000 records and 25 Systems (fields)

I have a form with two subforms, each subform allows me to select one record from the table.

Let's say I selected BLD1 and Bld3, I want to create a button that will take the two selected records and create a table similar to the one below.

System	Bld1	Bld3
System1	System1A	System1A
System2	System2C	System 2A
System3	System 3F	System 3C
System4	System4A	System4C

<tbody>
</tbody>

(The actual end result would have about 25 records, one for each system.

What is the best way to go about accomplishing this?

 

[Micron]

You might have to do it in two parts and it will be a lot of work because what you're trying to do is TRANSPOSE columns into rows. To go the other way, we have the built in cross tab query. What you want to do will require a UNION query. To transpose columns to rows:

SELECT System1 AS Bld1 FROM TableName WHERE System1 Is Not Null AND [Building Name] = "BLD1" 
UNION ALL
SELECT System2 AS Bld1 FROM TableName WHERE System2 Is Not Null AND [Building Name] = "BLD1" 
UNION ALL
SELECT System3 AS Bld1 FROM TableName WHERE System3 Is Not Null AND [Building Name] = "BLD1" 
UNION ALL
SELECT System4 AS Bld1 FROM TableName WHERE System4 Is Not Null AND [Building Name] = "BLD1" 
repeat for Bld3.

However, you add complexity because you want table field names to become data in the System field - I've never seen it done nor asked about. So you may have to use the union query as a source for a make table, select or append query (you do not say where you want your results to go first). This other query can create a field with SELECT "System1" AS System and you will get a field called System with a value of System1 in the records along with the data from the other query. However, you don't want System1 in every row, so you can use SELECT "" AS System; to get blanks and use an update query after.

The only other option I can suggest (which would be the easiest if it works) is to write

SELECT "System1" AS System, [COLOR=#ff0000][B]System1[/B][/COLOR] AS Bld1 FROM TableName WHERE System1 Is Not Null AND [Building Name] = "BLD1"

and change "System1" to match each system number where red above. I'm just not sure how Access will handle the duplicate reference to System1, but maybe it'll be OK because the quotes make it an alias. I'm thinking you might get a "Duplicate" type of error. Note that you must use quotes around the created field name. If you try this, I think I'd do as in the first example to get that part working first, only then adding another level of complexity.

 

[Joe4]

Just an FYI...

The structure of your data table actually goes against the rules of normalization, and would not be considered good database design. You should not have repeating system fields, but rather a structure like this:

Fields
Building Name
System Number
System Value

So then your table would look something like:

[B]Building Name     System Number      System Value
[/B]BLD1                1                 SystemA1
BLD1                2                 System2C
BLD1                3                 System3F
BLD1                4                 System4A
BLD2                1                 System1B
...

You should be able to get from there to the structure you showed originally via a Cross-Tab Query.

Having a good structure allows you more flexibility to do things more easily. A good example of an issue with your original structure is the following:
You said that you have 25 different System fields. What if someone came to you and asked you to return all records for anyone using "System4A"? In your query, you would need to search 25 different fields (what a nightmare)! In the normalized structure I presented, you only have to search one.

For more information on Database Design and Rules of Normalization, see the following article: http://sbuweb.tcu.edu/bjones/20263/Access/AC101_FundamentalsDB_Design.pdf