Move data from one field into another (2003)

Topic

New Reply

Is there a way to make a query that returns data appended into a new field? I have data rows with item1, item2, item3 etc as separate fields. All of these fields contain the same kind of data, just in different positions. I want the query results (not a new table) to give me a count of all those items in one single field. I start with this
ID item1 item2 item3 item4 item5
1… 475 …. 01004
2… 475 …. 01004
3… 18 …. 00026 …. 00565 …. 00565

and want to end up with this
Item… count
475 … 2
18 … 1
1004 … 2
26 … 1
565 … 2

Is there a reasonable way to do this?
Thanks so much.

Reply | Quote

Replies

It would be much more convenient to change to a normalized table structure:

ID	Item	Value
1	1	475
1	2	01004
2	1	475
2	2	01004
3	1	18
3	2	00026
3	3	00565
3	4	00565

You can use VBA to do this; if you search for normalize in this forum you’ll find examples, and you can download a free database with sample code from Roger’s Access Library. Using the normalized structure, the query you need is a straightforward totals query.

Reply | Quote

Thank you. I’ll play with that. I’d have to rename every table (up to 10/day) and then modify the field names in the Module for every query. At that point it might be simpler to aggregate each field into Excel and then pivot it. I’m trying to avoid having to make new tables since the tables may have a million or more rows each. But this at least gives me some ideas. I do appreciate it.
Judy

Reply | Quote

> I’d have to rename every table (up to 10/day)

Where do these tables come from? If you’re importing them from another database system, chances are the data are stored in a normalized format there, and denormalized for export, so it might be worthwhile investigating whether you can get the original data.

Reply | Quote

Yes, I’m pulling data from a warehouse. I could pull one row for each item, but I have reasons for not wanting to do it that way. I want to pull all items (max 5) in one row.
I’ll keep playing with it- something is bound to occur. Thanks for your help.
Judy

Reply | Quote

It’s your decision, of course, but you are making things much more difficult for yourself by flaunting the rules for relational databases.

Reply | Quote