Solved

Need Access query help

Posted on 2012-12-21
4
288 Views
Last Modified: 2012-12-21
Is it possible to have the query below, give me a count but only 1 count per softslip.  So if the animal had more than one "note" it only gets counted once.  Or can someone create a new query using the information below?  Points are high because I need this today.

SELECT Count(Notes.SoftSlip) AS CountOfSoftSlip, 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;
0
Comment
Question by:J.R. Sitman
  • 2
4 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 38714071
try this
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
0
 

Author Comment

by:J.R. Sitman
ID: 38714129
It worked.  Can you give me another that groups it by location and doesn't prompt for the location?
0
 

Author Closing Comment

by:J.R. Sitman
ID: 38714813
thanks
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

837 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question