Column and record consolidation problem.

[bobtehdog]

Column Consolidation.

My problem is this. I have to sort each of the def1-def23 columns from the old table into the def1-def6 columns from the new table preserving the other data in the records. So basically, i have 23 categories that I have to sort into 6 broader or more general categories.

I have tried updating/appending the data in the query design view, but I end up with syntactic errors every time due to my lack of knowledge. Below is a sample of the old table and the new table.

Old/current table format:
1. photonum text
2. streetnum text
3. streetname text
4. insdate date
5. front yes/no
6. right yes/no
7. left yes/no
8. rear yes/no
9. def1 yes/no
10. def2 yes/no
11. def3 yes/no
12 def4 yes/no
13 def5 yes/no
.......
31. def23 yes/no
32. bottom yes/no
33. middle yes/no
34. top yes/no

new table:
1. photonum text
2. streetnum text
3. streetname text
4. insdate date
5. front yes/no
6. right yes/no
7. left yes/no
8. rear yes/no
9. def1 yes/no
10. def2 yes/no
11. def3 yes/no
12 def4 yes/no
13 def5 yes/no
14. def6 yes/no
15. bottom yes/no
16. middle yes/no
17. top yes/no


On the same coin:
Record Consolidation

In those same tables, how would I go about consolidating records in this fashion?

so this:
photo streetnum streetname def1 def2 def3 def4 def5
1.jpg 123 maple true true false false false
2.jpg 123 maple false true true false false
3.jpg 123 maple true false false false true

can become this:
photo streetnum streetname def1 def2 def3 def def5
moot 123 maple true true true false true

Please point me in the right direction, any help would be appreciated.
There are roughly 35,000 records, which rules out doing it by hand in a timely manner.


Thank You

[NeoPa]

The second issue would be done by using the GROUP BY clause.
Queries in Access can be viewed either in the Design grid or the SQL view. I would suggest that it may be good experience for you to look at the resultant query in the design grid after entering it into the SQL view. Does that make sense? Maybe worthwhile for the previous answer too. That way you can become more comfortable using the Design grid, which is usually quicker and easier to develop in.

Anyway, on to the consolidation. This will work by getting the minimum value of the booleans gen1 - gen6 (separately). This relies on the TRUE value == -1 and FALSE == 0. Any non-zero value is treated as true in SQL but only TRUE (-1) = TRUE.
To illustrate :
If TRUE Then Print X ==> X
If -1 Then Print X ==> X
If 1 Then Print X ==> X
If TRUE = -1 Then Print X ==> X
If TRUE = 1 Then Print X ==> Nothing
If 555 Then Print X ==> X
If TRUE = 555 Then Print X ==> Nothing
Note also, those items that are neither GROUPed BY, nor processed through an aggregate function (EG. Min()), cannot be included in a GROUP BY query.

SELECT street_number,
       street_name,
       Min(gen1) AS MinGen1,
       Min(gen2) AS MinGen2,
       Min(gen3) AS MinGen3,
       Min(gen4) AS MinGen4,
       Min(gen5) AS MinGen5,
       Min(gen6) AS MinGen6
FROM new_data
GROUP BY street_number, street_name;