Experts Exchange connects you with the people and services you need so you can get back to work.

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

J.R. Sitman

Asked: 2012-12-21

Advertise Here

Microsoft Access

1 Solution

LVL 61

mbizupCommented: 2012-12-21

Give this a try:

Select Count(A.SoftSlip), A.Location, A.Species
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 SoftSlips.Location, Species.Species,Notes.SoftSlip
) As A
Group by A.Location, A.Species

Open in new window

LVL 61

mbizupCommented: 2012-12-21

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

LVL 120

Rey Obrero (Capricorn1)Commented: 2012-12-21

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