Repositioning of fields 1

[Zed5010]

Hi,
I have a form with:
record 1: Style 1 | Color1 | Color2 | Size1 | Size2
record 2: Style 2 | Color1 | Color2 | Size1 | Size2

I need to generate a table as follows:
ID 0001 | Style 1 | Color1 | Size1
ID 0002 | Style 1 | Color1 | Size2
ID 0003 | Style 1 | Color2 | Size1
ID 0004 | Style 1 | Color2 | Size2
ID 0005 | Style 2 | Color1 | Size1
ID 0006 | Style 2 | Color1 | Size2
ID 0007 | Style 2 | Color2 | Size1
ID 0008 | Style 2 | Color2 | Size2

Appreciate all suggestions...Thanks

 

[jrbarnett]

Is this data in a table behind the form?

John

 

[Zed5010]

Yes.

Zed

 

[jrbarnett]

Firstly, use a union query to merge the different size and color fields into the same column (eg

SELECT style, color, size
FROM table
WHERE size = 'Size 1'
UNION
select style, color, size
FROM table
WHERE size = 'Size 2'

Then its simply

SELECT style, color, size
FROM query style, query color, query size

The ID can be generated using an autonumber field as the primary key and generated using SQL insert or Paste Append from the edit menu.

Note no joins between tables.

John

 

[Zed5010]

Thanks John! I'm sort of new to the UNION qry. Can you help me with it? I have a style table (see 1st part of my example record 1:...), do I create a new table to host the merged records? How is the UNION qry set up? Much appreciated!!

 

[jrbarnett]

A union query is basically a means of joining two select statements in a single result set.
There have to be exactly the same number of columns in each set, but there are no limitations to the number of sections.

To create a Union query, create a new query from the query view, choose SQL from the View menu then type in the SQL code.
It isn't possible to create a Union query using the query designer, but you can create individual parts as separate select queries then copy and paste them into the same query.
Just make sure the semi colon on the end is removed and the word UNION is put between each section.

You create a new table to host the merged records like you would create any other table - set up the fields, data types etc from the table design window.

John