Move from one table to other table in a different format

[learning_codes]

Hi,

I don't know where to start. I would be appreciated your help. I
have approximately 850 records (Table 1)

Table 1 has 100 Field Names that have a product name but should be a
value not the field name. It is very difficult to explain but I will
make it an example:

I want to create a button to do the work from Table #1 to Table #2.
When I import the data from excel spreadsheet to Access XP and Product
A, B, C become Label but it should be value than label. Table #1 is
the same format as excel spreadsheet.

TABLE #1
OrderID Product A Product B Product C........... up to 100
1 Mon Wed
Fri Mon to Fri
2 Tues Fri
Thurs Mon to Fri


TABLE#2 (This is what I need to set this format)
OrderID InStock Mon Tues Wed Thurs
Friday
1 Product A Yes No No
No No
1 Product B No No Yes
No No
1 Product C No No No
No Yes
1 Product D same thing with Product A, B C
1 Product E .....
2 Product A No Yes No
No No
2 Product B No No No
No Yes
2 Product C No No No
Yes No
2 Product D same thing with Product A, B C
2 Product E

Your help would be much appreciated. I'm having a nightmare with 850
records that I'm trying to convert from Table #1 to Table #2.

 

[John W. Vinson]

TABLE #1
OrderID Product A Product B Product C........... up to 100
1 Mon Wed
Fri Mon to Fri
2 Tues Fri
Thurs Mon to Fri


TABLE#2 (This is what I need to set this format)
OrderID InStock Mon Tues Wed Thurs
Friday
1 Product A Yes No No
No No
1 Product B No No Yes
No No

I'd suggest you go a step further in normalization. Having fields named Mon
Tues Wed Thurs Fri is just as bad as having fields named Product A Product B!
You shouldn't be storing data in fieldnames at all.

A better design would be:

OrderID
Product
DayInStock

The DayInStock function could be the day name, or maybe better an integer with
values 1 to 7 (or 2 to 6 if weekends are never an issue) to go along with
the Weekday function, value 1 = Sunday, 7 = Saturday.

It will help if you have a days translation table DayTable, fields DayList and
DayInStock with records like

Mon 2
Tue 3
Wed 4
....
Mon to Fri 2
Mon to Fri 3
Mon to Fri 4
Mon to Fri 5
Mon to Fry 6
Mon to Tue 2
Mon to Tue 3
<all possible used combinations>

You can then use a Normalizing Union Query like:

SELECT Table1.OrderID, DayTable.DayInStock, "Product A" AS Product
FROM Table1 INNER JOIN DayTable
ON Table1.[Product A] = DayTable.DayList
UNION ALL
SELECT Table1.OrderID, DayTable.DayInStock, "Product B" AS Product
FROM Table1 INNER JOIN DayTable
ON Table1.[Product B] = DayTable.DayList
UNION ALL
SELECT Table1.OrderID, DayTable.DayInStock, "Product C" AS Product
FROM Table1 INNER JOIN DayTable
ON Table1.[Product C] = DayTable.DayList

<etcetera>

You probably won't get all 100 fields without getting the QUERY TOO COMPLEX
error, but you can do 25 fields at a time, I suspect.

Base an Append query on this to populate the tall-thin normalized table; if
you need the grid with day names as the header row, create a Crosstab query
based on the tall-thin.

If the days actually correspond to real-life dates, I'd take it yet a step
further and have a Date/Time field.