Re: Move from one table to other table in a different format
- From: John W. Vinson <jvinson@xxxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Fri, 04 Apr 2008 20:47:42 -0600
On Fri, 4 Apr 2008 17:57:24 -0700 (PDT), "learning_codes@xxxxxxxxxxx"
<learning_codes@xxxxxxxxxxx> wrote:
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.
--
John W. Vinson [MVP]
.
- References:
- Move from one table to other table in a different format
- From: learning_codes@xxxxxxxxxxx
- Move from one table to other table in a different format
- Prev by Date: Re: Open form to new record
- Next by Date: Re: Off Topic Post
- Previous by thread: Move from one table to other table in a different format
- Index(es):
Relevant Pages
|
