MS Access Query Help

[Jordan M.]

Hi,

Hoping someone can help me find a solution to the following problem I
have.

I have a query that currently pulls the following information from
Table A

Project Name Milestone Date
-------------------------\--------------------------
Project A Date 1
Project A Date 2
Project A Date 3
Project B Date 1
Project B Date 2
Project C Date 1
Project C Date 2
Project C Date 3
Project C Date 4

But What I really want to pull is the following:

Project Name Date1 Date2 Date3
Date4
-------------------------\----------------\------------------
\--------------------\-------------
Project A Date 1 Date 2 Date 3
Project A Date 1 Date 2
Project A Date 1 Date 2 Date 3
Date4

How do I go about doing something like that?

 

[Guest]

TRANSFORM First([Table A].[Milestone Date]) AS [FirstOfMilestone Date]
SELECT [Table A].[Project Name]
FROM [Table A]
GROUP BY [Table A].[Project Name]
PIVOT [Table A].[Milestone Date];

 

[Jordan M.]

TRANSFORM First([Table A].[Milestone Date]) AS [FirstOfMilestone Date]
SELECT [Table A].[Project Name]
FROM [Table A]
GROUP BY [Table A].[Project Name]
PIVOT [Table A].[Milestone Date];
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Hi,

Hoping someone can help me find a solution to the following problem I
have.

I have a query that currently pulls the following information from
Table A

Project Name Milestone Date
-------------------------\--------------------------
Project A Date 1
Project A Date 2
Project A Date 3
Project B Date 1
Project B Date 2
Project C Date 1
Project C Date 2
Project C Date 3
Project C Date 4

But What I really want to pull is the following:

Project Name Date1 Date2 Date3
Date4
-------------------------\----------------\------------------
\--------------------\-------------
Project A Date 1 Date 2 Date 3
Project A Date 1 Date 2
Project A Date 1 Date 2 Date 3
Date4

How do I go about doing something like that?- Hide quoted text -

- Show quoted text -

Hi Jerry,

That's half the solution. But if the dates aren't the same, then it
makes a new column for each date. I want to hvae the first date for
each project appear in column 1, regardless of if they are the same or
not. The PIVOT function isn't allowing me to do that.

Thoughts?

 

[Michel Walsh]

You will have to rank the records accordingly to the date, by project.

A possible solution is:

SELECT a.project, a.dateStamp, COUNT(*) as rank
FROM myTable As a INNER JOIN myTable As b
ON a.project=b.project AND a.dateStamp >= b.dateStamp
GROUP BY a.project, a.dateStamp



then, build a crosstab query over that last query, like:

TRANSFORM LAST(dateStamp)
SELECT project
FROM savedQuery
GROUP BY project
PIVOT rank


(you can use, instead of LAST, FIRST, MIN, MAX, AVG and even SUM, since
there *should* be at most one record supplying a value, per 'cell').


Hoping it may help,
Vanderghast, Access MVP

TRANSFORM First([Table A].[Milestone Date]) AS [FirstOfMilestone Date]
SELECT [Table A].[Project Name]
FROM [Table A]
GROUP BY [Table A].[Project Name]
PIVOT [Table A].[Milestone Date];
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Hi,

Hoping someone can help me find a solution to the following problem I
have.

I have a query that currently pulls the following information from
Table A

Project Name Milestone Date
-------------------------\--------------------------
Project A Date 1
Project A Date 2
Project A Date 3
Project B Date 1
Project B Date 2
Project C Date 1
Project C Date 2
Project C Date 3
Project C Date 4

But What I really want to pull is the following:

Project Name Date1 Date2 Date3
Date4
-------------------------\----------------\------------------
\--------------------\-------------
Project A Date 1 Date 2 Date 3
Project A Date 1 Date 2
Project A Date 1 Date 2 Date 3
Date4

How do I go about doing something like that?- Hide quoted text -

- Show quoted text -

Hi Jerry,

That's half the solution. But if the dates aren't the same, then it
makes a new column for each date. I want to hvae the first date for
each project appear in column 1, regardless of if they are the same or
not. The PIVOT function isn't allowing me to do that.

Thoughts?