Query question

[John Petty]

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

 

[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
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

 

[John Petty]

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