Solved

Need Access query modified

Posted on 2012-12-21

Microsoft Access

1 solution

302 Views

Last Modified: 2012-12-22

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

Comment

Question by:J.R. Sitman

[X]

Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

Help others & share knowledge
Earn cash & points
Learn & ask questions

Join The Community

6 Comments

LVL 61

Accepted Solution

by:mbizup

mbizup earned 500 total points

ID: 387148372012-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

Expert Comment

by:mbizup

ID: 387148422012-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

Expert Comment

by:Rey Obrero (Capricorn1)

ID: 387148432012-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

Author Comment

by:J.R. Sitman

ID: 387148602012-12-21

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

Author Closing Comment

by:J.R. Sitman

ID: 387149782012-12-21

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

Thanks

LVL 61

Expert Comment

by:mbizup

ID: 387154652012-12-22

Glad to help :-)

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

William Moulton Marston’s DISC Matrix applied to Email Etiquette

Article by: Dermot

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…

Creating an Access Database Taskbar Item in Windows 7 through 10

Article by: Helen

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database. This article shows how to recreate this functionality in Windows 7 through 10.

Microsoft Access

Creating and Executing a SQL Pass-thru Query

Video by: TechMommy

Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

MS Access – Working with Relationships

Video by: Jim

With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…