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!
rosemary fletcherAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

You can add some table with criteria (status) and replacement field:
status                replacement
pending                P
active                     A
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 fletcherAuthor Commented:
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 FyeOwner, Developing Solutions LLCCommented:
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
SELECT ID, "Status2" as Source, Status2 as StatusValue FROM yourTable WHERE Status2 IS NOT NULL
SELECT ID, "Status3" as Source, Status3 as StatusValue FROM yourTable WHERE Status3 IS NOT NULL
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

rosemary fletcherAuthor Commented:
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 FyeOwner, Developing Solutions LLCCommented:
this normalizes your data, and would convert a record which looks like:

ID    Status1        Status2        Status3
1     Pending        Late           Complete


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


ID    Source     StatusValue
1     Status1    Pending
1     Status3    Complete

Open in new window

rosemary fletcherAuthor Commented:
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.
Can you upload sample table with some dummy data?
Dale FyeOwner, Developing Solutions LLCCommented:
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 fletcherAuthor Commented:
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.
Could you explain your data? Where could be status? Always in PQ, WQ, VQ?
rosemary fletcherAuthor Commented:
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.
So number in Q columns means nothing, only column name is meaningful?
rosemary fletcherAuthor Commented:
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.
VBA code is acceptable?
rosemary fletcherAuthor Commented:
Yes, that is acceptable. Although, I am doubtful as to how well it would work.
Look at sample. Code is under button on a form, Result is in a table OUT (should exist before button is pressed)
rosemary fletcherAuthor Commented:
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?
You can create table with conversion and use it in compare query.
The other way - add it to code, but you will need determine value for each column with statuses:
Do While Not rsti.EOF
    For i = 0 To rsti.Fields.Count - 1
        Debug.Print i, rsti.Fields(i).Name, rsti.Fields(i)
        If Right(rsti.Fields(i).Name, 1) = "Q" And rsti.Fields(i) > 0 Then
            rsto!A = rsti!A
            rsto!V = rsti!V
            rsto!S = rsti!S
            rsto!D = rsti!D
            rsto!Sk = rsti!Sk
            rsto!St = rsti!St
            rsto!W = rsti!W
            rsto!Da = rsti!Da
            rsto!Al = rsti!Al
            rsto!Ac = rsti!PDa
            rsto!Q = rsti.Fields(i)
            Select Case rsti.Fields(i).Name
                Case "PQ"
                    rsto!Sta = "Pend"
                Case "VQ"
                    rsto!Sta = "Ca"
                Case "WQ"
                    rsto!Sta = "WM"
                ' Add case for each column
            End Select
        End If
    Next i

Open in new window

I prefer first way

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rosemary fletcherAuthor Commented:
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!
You are welcome
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.