Query to create new table format

[poalman]

Hi,
I'm trying to make a query that groups a table on 2 columns, then
takes all the numbers from 1 of the ungrouped columns and puts them
into rows, with a flexible number of rows.

I haven't explained this especially well, I'll try posting a the start
and end points that I'm look for and hope someone can make some sense
of this because it's really stretching my SQL knowledge

Current Table:
Date Project SubProject Check
01/08/2007 J I 72%
01/08/2007 L None 75%
01/08/2007 Sa E 51%
01/11/2007 F None 42%
01/11/2007 J I 66%
01/01/2008 Sk None 46%
01/03/2008 C None 70%
01/03/2008 F None 100%
01/03/2008 J I 83%
01/03/2008 L None 85%
01/03/2008 M 2 93%
01/03/2008 M 3 85%
01/03/2008 R M 68%
01/03/2008 Sa E1 56%
01/06/2008 Sa E2 80%

Project SubProject Check1 Check2 Check3
J I 72% 66% 83%
L None 75% 85% 0%
Sa E1 51% 56% 0%
F None 42% 100% 0%
Sk None 46% 0% 0%
C None 70% 0% 0%
M 2 93% 0% 0%
M 3 85% 0% 0%
R M 68% 0% 0%
Sa E2 80% 0% 0%

So what has happened is its grouped the project and subproject (GROUP
BY Project, SubProject)
and the lines that were lost by the grouping, it has added the score
into a new column (check2, check 3, ect.) if there was another J & I
Check then the new table would need an additional column (Check4).
So basically I need to add as many columns as there are Projects with
the same SubProject.
Also the checks need to appear in order of the dates, so check 1 is
the earliest.

Any help would be HUGELY appreciated as I just can't crack this one!
Many Thanks!!

 

[Jeff Boyce]

Adding additional columns to add additional "checks" is just how you'd have
to handle this ... if you were using a spreadsheet!

Since Access is a relational database, trying to feed it 'sheet data will
only frustrate both you and Access.

Instead, based on your description, you have a one-to-many relationship. In
a relational database, you handle this by using a "child" table to hold the
"many" records.

If "relational" and "normalized" aren't familiar, plan to spend some time
learning about these before trying to get Access to work well...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John Spencer]

That looks like a crosstab query based on ranking. The Ranking would be based
on the date.

SELECT A.Project, A.SubProject, A.Check,
1 + Count(B.Date) as Rank
FROM YourTable as A LEFT JOIN YourTable As B
ON A.Project = B.Project
AND A.SubProject = B.SubProject
AND A.[Date] < B.Date
GROUP BY A.Project, A.SubProject, Check

Now use that in a crosstab query
TRANSFORM First(Check) as x
SELECT Project, SubProject
FROM TheSavedQuery
GROUP BY Project, SubProject
PIVOT "Check" & Rank

You might be able to do that all in one
TRANSFORM First(Check) as x
SELECT Project, SubProject
FROM (SELECT A.Project, A.SubProject, A.Check,
1 + Count(B.Date) as Rank
FROM YourTable as A LEFT JOIN YourTable As B
ON A.Project = B.Project
AND A.SubProject = B.SubProject
AND A.Date < B.Date
GROUP BY A.Project, A.SubProject, Check) as Source
GROUP BY Project, SubProject
PIVOT "Check" & Rank


John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

 

[poalman]

Jeff,
I realise it makes a lot of sense to store this in two tables with a
relationship, the reason I needed a query to shape the table into the
one I showed is because I need to be able to based a graph onto it
that has varied number of series.

John,
I used your second all in one query and worked like a charm! Just
needed to change the less than to a greater than but no biggie
Thanks you very much!

Cheers!!
Poal.

 

[Jeff Boyce]

That's what queries are for ... you don't need a table to hold everything
together when you can use a query to join the related tables.

Regards

Jeff Boyce
Microsoft Office/Access MVP