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]

You haven't said anything about how the 23 old categories are merged into the new ones. You have 6 new categories but no information as to what goes where.

[bobtehdog]

You haven't said anything about how the 23 old categories are merged into the new ones. You have 6 new categories but no information as to what goes where.

That is simple enough.

def1 gets numbers 2,4,5,12, 21, 22 and 23
def2 gets just number 3
def3 gets just number 1
def4 gets numbers 6,9,10,11,13,15 ,16 and 17
def5 gets number 19
def6 gets numbers 7,8,14 and 20

Thank you.

[NeoPa]

Simple would be finding the relevant information in the question ;)

Unfortunately, if I'm reading what you have written correctly, then you have also omitted to explain how your consolidation should work. It seems that a group of the old records get written as a single new one. Perhaps you could explain how this should work in your scenario?

[bobtehdog]

Simple would be finding the relevant information in the question ;)

Unfortunately, if I'm reading what you have written correctly, then you have also omitted to explain how your consolidation should work. It seems that a group of the old records get written as a single new one. Perhaps you could explain how this should work in your scenario?

I am using access 2007 by the way. Thank You for your patience.

I'm sorry for not being clear enough. I posted two separate issues in the same thread.

The first issue is that i have 35,000 records that are identified by photo number and address. Each record has columns def1 through def23, which needs to change into def1-def6 in the new table. So if def2, def4, and def5 are the only boxes checked in the old table for that record, the new table will only have def1 checked and all the other information verbatim. What I have been trying to do is an append query and messing with the conditional statements.

The second issue involves a way to summarize these records, so as to only display the address once, and have some kind of statement that checks the box for that address if it has been checked in the data table.
old data:
123 street 01100
123 street 00110
123 street 01001

summary data:
123 street 01111

Again, these were two separate issues. I apologize for any confusion and appreciate any help. I have been trying this mostly using queries. If i should use another part of access please let me know.

[NeoPa]

I appreciate this last response. It makes the whole thing a lot clearer.
It probably would have been better to separate the two questions into their individual threads originally, but I think we can make progress from here nevertheless.
Your problem is complicated, not so much by the fundamental nature of it, but more by the large number of fields and the non-standard way that different fields are worked out.
Give me some time and I will try to put something together for you. This will not be the final answer mind, simply pointers to the right way of going about it. Concepts.

Let me clarify your second question somewhat :
Which fields, being the same, should indicate that the records should be treated as one? Is it possibly StreetName & StreetNum?
I presume the consolidation is done on the data in the new table which results from question one.
For the six boolean (Yes/No) categories you want them grouped together but the booleans should be treated as being ORed together? By that, I mean should the result be TRUE if any of the component fields be TRUE, but otherwise FALSE?

PS. I will be going to bed very soon so any response will have to wait at least until tomorrow now I'm afraid.

[bobtehdog]

This is all very correct. I would like to add that I have been learning scores about how to and how not to do things in access, starting with the design.

I appreciate this last response. It makes the whole thing a lot clearer.
It probably would have been better to separate the two questions into their individual threads originally, but I think we can make progress from here nevertheless.
Your problem is complicated, not so much by the fundamental nature of it, but more by the large number of fields and the non-standard way that different fields are worked out.
Give me some time and I will try to put something together for you. This will not be the final answer mind, simply pointers to the right way of going about it. Concepts.

Let me clarify your second question somewhat :
Which fields, being the same, should indicate that the records should be treated as one? Is it possibly StreetName & StreetNum?
I presume the consolidation is done on the data in the new table which results from question one.
For the six boolean (Yes/No) categories you want them grouped together but the booleans should be treated as being ORed together? By that, I mean should the result be TRUE if any of the component fields be TRUE, but otherwise FALSE?

PS. I will be going to bed very soon so any response will have to wait at least until tomorrow now I'm afraid.

[NeoPa]

This is all very correct. I would like to add that I have been learning scores about how to and how not to do things in access, starting with the design.

I should take this to mean that all my guesses were on target then. Good.
I should warn you that I have a couple of hectic days ahead - looking at universities for my son. I will do what I can of course, but please be patient with me as my access to the web will be limited during that period.

[bobtehdog]

ok, my solution was this. new_data is a blank table. data_tmp is a copy the original table. i've been sitting on a "learn sql in ten minutes" book. i took about four hours to study the book, and started to fiddle with access. got real sick of access 2007, and started a blank query in sql view. didn't take long for me to work this out after getting my bearings. anything more complex, and i would have been toast for sure. thank you for your help.

INSERT INTO new_data
 ( photo_num, inspection_date, street_number, street_name, front, [right], [left], rear, gen1, gen2, gen3, gen4, gen5, gen6, bottom, middle, [top] )
SELECT 
photo_num, inspection_date, street_number, street_name, front, [right], [left], rear, 
def2 OR def4 OR def5 OR def12 OR def21 OR def22 OR def23, 
def3, 
def1, 
def6 OR def9 OR def10 OR def11 OR def12 OR def13 OR def15 OR def16 OR def17 OR def18,
def19,
def7 OR def8 OR def14 OR def20,
bottom,
middle,
[top]
FROM data_tmp;

[NeoPa]

Good answer, and helpfully laid out (otherwise this could get messy).
For clarification purposes (Your SQL should work fine without this), I would add the names of the formula fields (gen1-gen6) into your SQL. This specifies explicitly to any reader what goes where and should make life a little easier later :

INSERT INTO new_data
 ( photo_num, inspection_date, street_number, street_name, front, [right], [left], rear, gen1, gen2, gen3, gen4, gen5, gen6, bottom, middle, [top] )
SELECT
 photo_num, inspection_date, street_number, street_name, front, [right], [left], rear, 
 def2 OR def4 OR def5 OR def12 OR def21 OR def22 OR def23 AS gen1, 
 def3 AS gen2, 
 def1 AS gen3, 
 def6 OR def9 OR def10 OR def11 OR def12 OR def13 OR def15 OR def16 OR def17 OR def18 AS gen4,
 def19 AS gen5,
 def7 OR def8 OR def14 OR def20 AS gen6,
 bottom,
 middle,
 [top]
FROM data_tmp;