Need help with query 1

[PenelopeC]

I have a database that is not normalized (its a conversion I did from an old FoxPro db) I have a list like this...

fld1 fld2 fld3 fld4 fld5
25 ORSTPOL ORST
25 ORSTPOL ORSTP
25 ORSTPOL ORSTPL

Information in fields one and two are repeated 2 to 20 times with different entries for each in fields 3, 4, and 5. There is a lot of redundant data and we're trying to consolidate the list so we can fix it! How would I do a query that would list the info in fields one and two, with a list of all the possible other fields but on the same row?

Here's a sample of the data, though the last three fields didn't paste in quite right.

804053 CLACK CO LAW 3 CLK LAW 3
804053 CLACK CO LAW 3 CLACK LAW 3
804053 CLACK CO LAW 3 CCOM 3
804053 CLACK CO LAW 3 CC LAW 3
804053 CLACK CO LAW 3 CLAK LW3
804055 CLACK CO LAW 2 CLK LAW 2
804055 CLACK CO LAW 2 CLAK LW2
804055 CLACK CO LAW 2 CC LAW 2
804055 CLACK CO LAW 2 CLACK LAW 2
804055 CLACK CO LAW 2 CCOM 2

Here's my hoped for results...

804055 CLACK CO LAW 2 CLK LAW 3 CLACK LAW 3 CCOM 3 CC LAW 3 CLAK LW3....etc, all in one row.

I know in SQL you can join a table to itself aliased with another name...would this be the same approach? Does that work in Access?

TIA!

PenelopeC
~~~>-/O~~~~~swimming right along

 

[RickSpr]

Self-joins using aliases work in Access, but I don't see how you could use such an approach for this.

I think this will do it, but I haven't tested it. I'll refer to this table as Table1, with fields Fld1 through Fld5. I assume you want the result to have a single column holding concatenated values from Fld3 through Fld5.

1. Create table Result and give it fields Fld1, Fld2, and Fld3. Ensure Fld3 is large enough to hold the concatenated values. You may want to make it a Memo field.

2. Run this query:
INSERT INTO Result (Fld1, Fld2)
SELECT DISTINCT Fld1, Fld2 FROM Table1

3. Run this query:
UPDATE Table1 INNER JOIN Result ON Table1.Fld1 = Result.Fld1 AND Table1.Fld2 = Result.Fld2
SET Result.Fld3 = Trim(Result.Fld3 & " " & Table1.Fld3)
WHERE Table1.Fld3 IS NOT NULL

4. Run the same query again, substituting Table1.Fld4 for Table1.Fld3 (2 occurrences).

5. Run the same query again, substituting Table1.Fld5 for Table1.Fld4 (2 occurrences).

After verification of the results, Result can replace Table1.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[PenelopeC]

Rick,
Thanks so much for your help. I've been working with this since yesterday (I've been a little distracted).

All of you coding and suggestions work great! Thank you SO MUCH....I'll be saving this one.

One question, it seems that the limit for the returns is 6. Some of these items have 30 entries all together. While checking my "result" table, I noticed that I successfully retrieved 6 entries, but 9 were not picked up (there are definitely some duplicates within the 30 rows.)

Does this make sense?

Thank you again! I'm already 110% further on this than I was yesterday at this time and if I don't hear anything else I'm STILL way further than I would be without your help!

PenelopeC
~~~>-/O~~~~~swimming right along

 

[RickSpr]

Do you mean that the Result table only has 6 rows, or that a maximum of 6 Fld3/Fld4/Fld5 entries from Table1 were loaded into Result.Fld3?

If the Result table only has 6 rows, open the query from step 2 (INSERT INTO...) in Design View or SQL View, then switch it to Datasheet View. If it only shows 6 rows, then one of the following must be true:
- You have a typo in the query, or
- There are only 6 unique combinations of Fld1 and Fld2 in Table1, so the number of rows in Result is correct. If that's not what you expected, explain to me why you expected more.

If you have a maximum of 6 entries in Result.Fld3, it's probably that the field isn't large enough. Open Result in Design View and check the Fld3 Type and Size properties. If the Type is Text, go ahead and set the Size to 255. Otherwise, the Type should be Memo. If the Type and Size are already large enough, I don't know what could be wrong, but I'll wait until you say that's the case before I start down that path.

By the way, on rereading your original post I realize that you want to eliminate extra spaces in Fld3. Change the middle line of the query in step 3 to the following:
SET Result.Fld3 = Trim(Result.Fld3 & " " & Trim(Table1.Fld3))


Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[PenelopeC]

Rick!! OMG - thank you! Thank You, thank you!

Totally made my Monday. My text field was 50 char, so of course it wouldn't hold everything. I made it 255 and now I'm golden.

SWEET!!

Hee!



PenelopeC
~~~>-/O~~~~~swimming right along