Query question

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

John Petty - 28 Mar 2008 20:01 GMT

I am having trouble trying to create a query to select the largest value in a
specific field for each record in a table.

I have created a crosstab query and it works fine.

WheelID    <>
28    2
30    1
31    1
33    1
34    4
35    1
36    1

But when the crosstab results in a select query I am getting the following

WheelID    Wheel Name    Proj. No.    <>    ProjectID
28    Test Wheel 01    GQP-2444    2    13
28    Test Wheel 01    1255    2    14
28    Test Wheel 01    GQP-2444    2    12
30    Test Wheel 02    GQP-1588    1    42
31    Test Wheel 03    GQP-2866    1    43
34    Test Wheel 05    1344    4    8
35    Test Wheel 06    1512    1    44
36    Test Wheel 07    GQP-2222    1    45

As you can see, the Test Wheel 1 (WheelID 28) is showing iteration 2, 3
times with 3 different projectID's (which is incorrect since each iteration
is a separate projectID and the iteration cannot be duplicated in any one
Wheel)

Can someone help me understand what I am doing wrong and how ot fix it?

Thanks,

John Petty

Reply to this Message

John Petty - 28 Mar 2008 20:26 GMT

Sorry about this. Let me rephrase the criteria I am needing. I am trying to
create a query that will find the largest iteration for each wheel. Each
wheel can have multiple iterations, so I am using the wheeldata table (No
dupes) and the project table (multiple projects for each wheel ID) in my
query.

I can create a crosstab query and all looks fine.

WheelID    Max Iteration
28    2
30    1
31    1
33    1
34    4
35    1
36    1

But when I try to add this to a select query, I am getting the following:

WheelID    Wheel Name    Proj. No.    <>    ProjectID
28    Test Wheel 01    GQP-2444    2    13
28    Test Wheel 01    1255    2    14
28    Test Wheel 01    GQP-2444    2    12
30    Test Wheel 02    GQP-1588    1    42
31    Test Wheel 03    GQP-2866    1    43
34    Test Wheel 05    1344    4    8
35    Test Wheel 06    1512    1    44
36    Test Wheel 07    GQP-2222    1    45

As you can see above, I am getting duplicate data from Wheel 1 but different
projects

WheelID    Wheel Name    Proj. No.    <>    ProjectID
28    Test Wheel 01    GQP-2444    2    13
28    Test Wheel 01    1255    2    14
28    Test Wheel 01    GQP-2444    2    12

This is incorrect as no 1 wheel can have duplicate iterations and each
iteration is housed in its own projectID (Project number is irrelevant text
data only)

The data for Wheel 1 is actually

WheelID    Wheel Name    Proj. No.    <>    ProjectID
28    Test Wheel 01    GQP-2444    1    13
28    Test Wheel 01    1255    2    14
28    Test Wheel 01    GQP-2444    1A    12

Can someone explain what I am doing wrong and how to fix it.

Thanks,
John Petty

Reply to this Message

John Petty - 29 Mar 2008 17:32 GMT

Thanks for taking the time to read my posts on this topic. I have found a
solution.

After some research (thanks to Phil Mitchell and Evan Callahan for the book
"Fixing Access Annoyances"), I found that I could create a "Totals" query,
and that this allowed me all the flexibility I was needing and added the
ability to group properly.

I deleted out the crosstab query and instead used the "Totals" query.

The sql statement I used is as follows:

SELECT tblWheelData.WheelID, tblWheelData.WheelName AS [Wheel Name],
Max(tblFProj.FIteration) AS FIteration, Max(tblFProj.ProjectID) AS
MaxOfProjectID
FROM tblWheelData INNER JOIN tblFProj ON tblWheelData.WheelID =
tblFProj.WheelID
GROUP BY tblWheelData.WheelID, tblWheelData.WheelName
ORDER BY tblWheelData.WheelName;

Works like a charm.

Thanks again

John Petty

> Sorry about this. Let me rephrase the criteria I am needing. I am trying to
> create a query that will find the largest iteration for each wheel. Each
[quoted text clipped - 48 lines]
> Thanks,
> John Petty

Reply to this Message