asked on

Separating a Single Row of Data using Queries

I have a table in an Access 2007 database that has a bunch of different statuses for one row of information. I am trying to match this table's data to another table which separates each status into a different row. Now, my original plan was to separate the first table's records into different rows based on their statuses and append them to a new table that can be used to match data with the original table. That is, I would create a query that focused on one status and assign it a letter that was associated with the status, such as "P" for pending. Then, I would append that data, with the letter replacing the original status column, into a new table that could be used to match data with the second table.

The only problem with this plan is that it would require multiple appending tables. I'm wondering if anyone out there has made a query that could break a row of data into separate rows and assign them data so that I would only have to build and append one query instead of several separate ones. If not, does anyone have any suggestions as to how I could make a single query that could do all this work? Or am I better off building multiple appending queries like my original plan?

Please let me know if my explanation was unclear and you need more information!

als315

You can add some table with criteria (status) and replacement field:
status replacement
pending P
active A
etc
and with one query (do not make links, add field from new table to criteria (Like "*" & [TableName].[status] & "*")
you will be able to create new table

rosemary fletcher

ASKER

I'm sorry, I'm a little confused by your reply. I understand replacing the fields, but how exactly would it separate one row of data into multiple rows based on what was in the status?

Dale Fye

Assuming that your first table has a structure that looks something like:

ID Status1 Status2 Status3

You could create a query that looks like:

SELECT ID, "Status1" as Source, Status1 as StatusValue FROM yourTable WHERE Status1 IS NOT NULL
UNION ALL
SELECT ID, "Status2" as Source, Status2 as StatusValue FROM yourTable WHERE Status2 IS NOT NULL
UNION ALL
SELECT ID, "Status3" as Source, Status3 as StatusValue FROM yourTable WHERE Status3 IS NOT NULL

rosemary fletcher

ASKER

That could work, but there are times where there are multiple statuses for one ID at the same time. Wouldn't that cause problems with the selection? And will that separate the row into multiple rows every single time?

Dale Fye

this normalizes your data, and would convert a record which looks like:

ID    Status1        Status2        Status3
1     Pending        Late           Complete

to:

ID    Source     StatusValue
1     Status1    Pending
1     Status2    Late
1     Status3    Complete

Open in new window

and if one of those was missing, like:

ID    Status1        Status2        Status3
1     ending                        Complete

to:

ID    Source     StatusValue
1     Status1    Pending
1     Status3    Complete

Open in new window

rosemary fletcher

ASKER

Ok, I think this is an error on my part. The status columns do not have their status written in the record itself. Instead, it's a number of how many items with that specific information are pending, sent, cancelled, etc. Part of how I want the query to work is to separate all the data into rows based on the status and then assign a status using the information by creating a separate column with the status written in. And since the numbers are written differently each time depending on the record, there is no real way to clarify which status the record requires based on that information unless I plug it in manually.

Sorry, I should have clarified that earlier.

als315

Can you upload sample table with some dummy data?

Dale Fye

agree with als315

a row of original data, along with a breakout like what you want the results to look like would be useful. You could do this in Excel and attach the file.

rosemary fletcher

ASKER

Ok, my goal is to take the record from the table Original Import and make it look like the record in the Adjusted table. Names of columns have been altered and all data in that record is made up.
Test3.accdb

als315

Could you explain your data? Where could be status? Always in PQ, WQ, VQ?

rosemary fletcher

ASKER

Each column with a Q in the header represents the number of items that is in that status. The PQ is pending, for example. There is a chance of any number of items being in one of those columns, but at least one of those columns has to have a number greater than 1 in that column for it to count.

The query I want to create has to look at each column with a Q header, create a record focused solely on that header if it holds a number greater than 0, and then assign a status to then end consisting of at least 2 letters.

als315

So number in Q columns means nothing, only column name is meaningful?

rosemary fletcher

ASKER

Yes and no. The number in the Q column matters if it's greater than 0, because that is what tells the query that it needs to create a new row of data based on that information.

als315

VBA code is acceptable?

rosemary fletcher

ASKER

Yes, that is acceptable. Although, I am doubtful as to how well it would work.

als315

Look at sample. Code is under button on a form, Result is in a table OUT (should exist before button is pressed)
Test3.accdb

rosemary fletcher

ASKER

Thanks, this code appears to be pretty helpful. However, there is one small thing I would like to change. You see, the way you set up the code is that the Sta column ends up filled with a letter and then Q. For the table I am hoping to create, I was hoping to set it up with code such as for the PQ column, you get the word "Pend", and the WQ column gets WM, etc. Can you clarify how I might be able to do that?

ASKER CERTIFIED SOLUTION

als315

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rosemary fletcher

ASKER

Thank you, this let me move the data and adjust the status column to contain the exact phrasing that I wanted it to. Thank you so much for all your help!

als315

You are welcome