Counting problem display 1

[topwaya]

Hi,
I have the following query:

SELECT Month([WOD]) AS [Month], Count(U.PID) AS CountOfPID, U.PID, tblLoc.LocID, U.WOLoc
FROM (tblPersonal AS P INNER JOIN tblFCUtil AS U ON P.PID = U.PID) INNER JOIN tblLoc ON P.LocW = tblLoc.LocID
WHERE (((U.WOD) Between [Forms]![fdlgOps]![txtStart] And [Forms]![fdlgOps]![txtEnd]) AND (((SELECT Count(*) FROM tblFCUtil WHERE PID = U.PID AND WOD Between [Forms]![fdlgOps]![txtStart] And [Forms]![fdlgOps]![txtEnd]))>=8))
GROUP BY Month([WOD]), U.PID, tblLoc.LocID, U.WOLoc;

It displays something like:

Month CountOfPID PID LocID WOLoc
1 8 101866 RVW Bartow
1 1 101866 RVW Bartow
1 2 101868 BTW South Fort Meade
1 6 101868 BTW South Fort Meade
1 1 101870 SFM Green Bay
1 3 101870 SFM Green Bay
1 5 101870 SFM Riverview

I would like it to display:

Month CountOfPID PID LocID WOLoc
1 9 101866 RVW Bartow
1 8 101868 BTW South Fort Meade
1 4 101870 SFM Green Bay
1 5 101870 SFM Riverview

I can't figure out how to get it to combine the count when the LocID and the WOLoc are identical for the PID.

Thanks for any help!

 

[gol4]

My initial read of the query everything seems ok.
is it possible there are spaces or hidden characters in some of the PID,LocID or WOLoc fields?

trim(locID)

 

[topwaya]

Thanks very much for the quick reply! I tried it (if how I applied it is what you had in mind) and it still has the same problem. Here is how I tried it.

SELECT Month([WOD]) AS [Month], Count(U.PID) AS CountOfPID, U.PID, tblLoc.LocID, U.WOLoc, Trim(LocID) AS Expr1
FROM (tblPersonal AS P INNER JOIN tblLoc ON P.LocW = tblLoc.LocID) INNER JOIN tblFCUtil AS U ON P.PID = U.PID
WHERE (((U.WOD) Between [Forms]![fdlgOps]![txtStart] And [Forms]![fdlgOps]![txtEnd]) AND (((SELECT Count(*) FROM tblFCUtil WHERE PID = U.PID AND WOD Between [Forms]![fdlgOps]![txtStart] And [Forms]![fdlgOps]![txtEnd]))>=8))
GROUP BY Month([WOD]), U.PID, tblLoc.LocID, U.WOLoc;

Any other ideas are appreciated, thanks so much for the help!!

 

[topwaya]

OK, I tried something else. I made an alias tblLoc table: tblLoc_1, To display the WOLoc. It only shows each PID once now, but the count is wrong. Am I on the right track? Or should I back up? Any ideas?

SELECT Month([WOD]) AS [Month], Count(U.PID) AS CountOfPID, U.PID, tblLoc.LocID, tblLoc_1.LocID
FROM ((tblPersonal AS P INNER JOIN tblLoc ON P.LocW = tblLoc.LocID) INNER JOIN tblFCUtil AS U ON P.PID = U.PID) INNER JOIN tblLoc AS tblLoc_1 ON U.WOLoc = tblLoc_1.LocID
WHERE (((U.WOD) Between [Forms]![fdlgOps]![txtStart] And [Forms]![fdlgOps]![txtEnd]) AND (((SELECT Count(*) FROM tblFCUtil WHERE PID = U.PID AND WOD Between [Forms]![fdlgOps]![txtStart] And [Forms]![fdlgOps]![txtEnd]))>=8))
GROUP BY Month([WOD]), U.PID, tblLoc.LocID, tblLoc_1.LocID;

Display:

Month CountOfPID PID tblLoc.LocID tblLoc_1.LocID
1 1 101866 RVW BTW
1 2 101868 BTW SFM
1 1 101870 SFM GB
1 5 101870 SFM RVW

 

[gol4]

You may be on the right track. I dont know much about your table structures and such but my experience is when grouping that if something falls out of the group there is something making it unique. I have imported stuff with hidden characters that on initial glace make it appear to be a match.
To troubleshoot this If you just do a inner join with out the criteria do the numbers work or is it still breaking it out. Do just a select on each table to see if it returns what you would expect.
Select Distinct PID from table

I have even gone so far as sorting the tables and looking at the data to see if something is wrong

 

[topwaya]

OK, thanks so much for the clues.
If I take out the criteria, I get the same thing.
I added all the other fields in the table to see if anything was making it unique - doesn't appear so.

So I started poking around and found a much simpler query based on one table that has the same problem. The query is:

SELECT tblFCUtil.WOLoc, Count(*) AS [Total Visits]
FROM tblFCUtil
WHERE (((tblFCUtil.WOD) Between [Forms]![fdlgHFC]![txtStart] And [Forms]![fdlgHFC]![txtEnd]))
GROUP BY tblFCUtil.WOLoc
ORDER BY tblFCUtil.WOLoc;

The tblFCUtil has these fields:
PID (personal ID)
WOD (work out date)
WOTme(work out time)
WOLoc (work out location)
WOCmt (work out comment)

PID, WOD, & WOTme are all the Primary Key.

The query above Displays:
Work Out Location Total Visits
Bartow 9
Green Bay 1
Green Bay 3
Riverview 7
South Fort Meade 4
South Fort Meade 6

If I add the other fields that should not be falling out of group the WOD and WOTme and WOCmt all appear to break the groups correctly. But when I add the PID it displays:

Work Out Location Total Visits Personal ID
Bartow 9 101866
Green Bay 1 101870
Green Bay 3 101870
Riverview 1 101867
Riverview 1 101869
Riverview 5 101870
South Fort Meade 1 101866
South Fort Meade 2 101868
South Fort Meade 1 101872
South Fort Meade 6 101868

So, it is combining the count for some, but not for others.

I checked the relationship for the LOCATION, it refers to a LookUp Table: tblLoc

Am I supposed to be able to have referential integrity between tblFCUtil.WOLoc and tblLoc.LocID? I have a relationship there, but it's a conditional relationship:
The LookUp Row Source is:

SELECT tblLoc.LocID, tblLoc.LocName FROM tblLoc WHERE (((tblLoc.FCSite)=Yes)) ORDER BY tblLoc.LocName;

The tblPersonal.PID has a One-to-Many relationship with tblFCUtil.PID with referential integrity.

btw- I don't know what "Hidden Characters" are or how to find them... I'm a newbie.

I opened the tblFCUtil and sorted the field: tblFCUtil.WOLoc and it did NOT sort correctly.

Here is what it displayed after I sorted (ascending) the WOLoc field:

PID Workout Date Workout Time Work Out Location
101866 1 /25/2005 3:00:00 PM Bartow
101868 1 /7 /2005 5:00:00 AM South Fort Meade
101866 1 /1 /2005 3:00:00 PM Bartow
101866 2 /28/2005 3:00:00 PM Bartow
101866 2 /25/2005 3:00:00 PM Bartow
101866 2 /20/2005 3:00:00 PM Bartow
101866 2 /15/2005 3:00:00 PM Bartow
101866 2 /4 /2005 3:00:00 PM Bartow
101868 1 /11/2005 5:00:00 AM South Fort Meade
101866 2 /2 /2005 3:00:00 PM Bartow
101868 1 /10/2005 5:00:00 AM South Fort Meade
101866 1 /18/2005 3:00:00 PM Bartow
101866 1 /17/2005 3:00:00 PM Bartow
101866 1 /16/2005 3:00:00 PM Bartow
101866 1 /15/2005 3:00:00 PM Bartow
101866 1 /9 /2005 3:00:00 PM Bartow
101866 1 /8 /2005 3:00:00 PM Bartow
101866 1 /7 /2005 3:00:00 PM Bartow
101866 2 /3 /2005 3:00:00 PM Bartow
101870 1 /5 /2005 12:00:00 PM Green Bay
101870 1 /7 /2005 12:00:00 PM Green Bay
101870 1 /6 /2005 12:00:00 PM Green Bay
101868 1 /13/2005 5:00:00 AM South Fort Meade
101868 1 /14/2005 5:00:00 AM South Fort Meade
101868 1 /15/2005 5:00:00 AM South Fort Meade
101866 2 /1 /2005 3:00:00 PM Bartow
101870 2 /15/2005 12:00:00 PM Fort Meade
101870 1 /4 /2005 12:00:00 PM Green Bay
101870 2 /4 /2005 12:00:00 PM Green Bay
101870 1 /15/2005 12:00:00 PM Riverview
101870 1 /12/2005 12:00:00 PM Riverview
101870 1 /11/2005 12:00:00 PM Riverview
101870 1 /8 /2005 12:00:00 PM Riverview
101867 1 /2 /2005 12:00:00 PM Riverview
101867 2 /2 /2005 12:00:00 PM Riverview
101869 2 /3 /2005 12:00:00 PM Riverview
101869 1 /3 /2005 12:00:00 PM Riverview
101870 1 /10/2005 12:00:00 PM Riverview
101868 1 /3 /2005 5:00:00 AM South Fort Meade
101868 2 /3 /2005 3:00:00 PM South Fort Meade
101868 1 /5 /2005 5:00:00 PM South Fort Meade
101872 1 /2 /2005 12:00:00 PM South Fort Meade
101868 2 /5 /2005 5:00:00 PM South Fort Meade
101866 1 /2 /2005 12:00:00 PM South Fort Meade


Any other ideas on where to look or how to solve?
Thanks so much for all your help!!

 

[topwaya]

I'm not sure what was wrong before. But what I did was I went and deleted the tblFCUtil.WOLoc relationship to tblLoc.LocID.

Then I redid the LookUp Wizard for the tblFCUtil.WOLoc.

What had happened, is that some of the locations were stored in the database as the LocID (BTW) and some where stored as the LocName (Bartow). I went through and stored them all as the LocID, and now the query works!!!

Yeah!!!
I never would have figured it out if you didn't point me in the right direction of what to look for.
Thank you so much!!!