J.R. Sitman
asked on
Need Access query modified
Can someone modify this query to have it group by location and not prompt for the location?
Points are high because I need this by tomorrow
Select Count(A.SoftSlip), A.Species
From
(
SELECT Notes.SoftSlip, Species.Species
FROM Species INNER JOIN (Notes INNER JOIN SoftSlips ON Notes.SoftSlip = SoftSlips.SoftSlip) ON Species.SpeciesID = SoftSlips.SpeciesID
WHERE (((Notes.NoteDate) Between [forms]![frmSwitchboard]![ txtStartDa te] And [forms]![frmSwitchboard]![ txtEndDate ]) AND ((SoftSlips.Location) Like "*" & [Enter Location] & "*") AND ((Notes.NoteType)="Trainer "))
GROUP BY Species.Species,Notes.Soft Slip
) As A
Group by A.Species
Points are high because I need this by tomorrow
Select Count(A.SoftSlip), A.Species
From
(
SELECT Notes.SoftSlip, Species.Species
FROM Species INNER JOIN (Notes INNER JOIN SoftSlips ON Notes.SoftSlip = SoftSlips.SoftSlip) ON Species.SpeciesID = SoftSlips.SpeciesID
WHERE (((Notes.NoteDate) Between [forms]![frmSwitchboard]![
GROUP BY Species.Species,Notes.Soft
) As A
Group by A.Species
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
do you mean like this
Select Count(A.SoftSlip), A.Species, A.Location
From
(
SELECT Notes.SoftSlip, Species.Species,SoftSlips. Location
FROM Species INNER JOIN (Notes INNER JOIN SoftSlips ON Notes.SoftSlip = SoftSlips.SoftSlip) ON Species.SpeciesID = SoftSlips.SpeciesID
WHERE (((Notes.NoteDate) Between [forms]![frmSwitchboard]![ txtStartDa te] And [forms]![frmSwitchboard]![ txtEndDate ]) AND ((Notes.NoteType)="Trainer "))
GROUP BY Species.Species,Notes.Soft Slip,SoftS lips.Locat ion
) As A
Group by A.Species,A.Location
Select Count(A.SoftSlip), A.Species, A.Location
From
(
SELECT Notes.SoftSlip, Species.Species,SoftSlips.
FROM Species INNER JOIN (Notes INNER JOIN SoftSlips ON Notes.SoftSlip = SoftSlips.SoftSlip) ON Species.SpeciesID = SoftSlips.SpeciesID
WHERE (((Notes.NoteDate) Between [forms]![frmSwitchboard]![
GROUP BY Species.Species,Notes.Soft
) As A
Group by A.Species,A.Location
ASKER
I'm at Sushi bar having Sake. Ill ck later if I can. :)
ASKER
Both of the solutions worked, but I'm accepting this one because it came in first.
Thanks
Thanks
Glad to help :-)
Open in new window