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]![txtStartDate] And [forms]![frmSwitchboard]![txtEndDate]) AND ((SoftSlips.Location) Like "*" & [Enter Location] & "*") AND ((Notes.NoteType)="Trainer"))
GROUP BY Species.Species,Notes.SoftSlip
) As A
Group by A.Species

Microsoft Access

Last Comment

mbizup

8/22/2022 - Mon

ASKER CERTIFIED SOLUTION

mbizup

12/21/2012

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

GET A PERSONALIZED SOLUTION

Ask your own question & get feedback from real experts

Find out why thousands trust the EE community with their toughest problems.

mbizup

12/21/2012

The above will group by location and species. If you want to omit the grouping by species:

Select Count(A.SoftSlip), 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]![txtStartDate] And [forms]![frmSwitchboard]![txtEndDate])  AND (Notes.NoteType ="Trainer")
ORDER BY SoftSlips.Location, Species.Species,Notes.SoftSlip
) As A
Group by A.Location

Open in new window

Rey Obrero (Capricorn1)

12/21/2012

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]![txtStartDate] And [forms]![frmSwitchboard]![txtEndDate]) AND ((Notes.NoteType)="Trainer"))
GROUP BY Species.Species,Notes.SoftSlip,SoftSlips.Location
) As A
Group by A.Species,A.Location

J.R. Sitman

12/21/2012

ASKER

I'm at Sushi bar having Sake. Ill ck later if I can. :)

Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!

James Murphy

J.R. Sitman

12/21/2012

ASKER

Both of the solutions worked, but I'm accepting this one because it came in first.

Thanks

mbizup

12/22/2012

Glad to help :-)