ACCESS FEED: Re: [MS_AccessPros] Query simplifications

Rabu, 12 Februari 2014

Re: [MS_AccessPros] Query simplifications

James-

I don't see a way to simplify that. qryPieData is getting a count of registrants by group - Unk, Dem, Rep, and the DCount is getting the total of all registrants to give you the percentage. You could perhaps use a Subquery to get the total count to get rid of qryPieData2, but what would be the point?

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

On Feb 12, 2014, at 6:03 PM, James McAlister <kc5qeg@gmail.com> wrote:

Experts,

I'm going through a application I wrote a few years back to summarize a variety of election statistics on a single page in an attempt to simplify the queries I used. I see several instances where I've created queries with other queries as the source in attempt to put the data in the proper format for graphing. This all works but is hard to follow, so I'm looking for ways to simplify or combine queries where that makes sense.

One simple example is below, where qryPieData uses DCount on qryPieData2 to pull in a total for the calculation of a percentage for use in a pie chart. Also, both queries pull in cboDay from frmInput.

So is it possible to do all of this in a single query, or is this the best approach?

Thanks—and any suggestions will be appreciated! Using Access 2012 here.

-----

qryPieData

SELECT IIf(IsNull([PctORVS]),"Unk",IIf([Registrations]![PctORVS]<0.52,"Dem","Rep")) AS Party, [Num]/[Tot] AS Pct, Count(Votes.RegistrantID) AS Num, DCount("RegistrantID","qryPieData2") AS Tot
FROM Registrations INNER JOIN Votes ON Registrations.ID = Votes.RegistrantID
WHERE (((Votes.DayNum)>=[Forms]![frmInput]![cboDay]))
GROUP BY IIf(IsNull([PctORVS]),"Unk",IIf([Registrations]![PctORVS]<0.52,"Dem","Rep"));

qryPieData2

SELECT Votes.RegistrantID
FROM Registrations INNER JOIN Votes ON Registrations.ID = Votes.RegistrantID
WHERE (((Votes.DayNum)>=[Forms]![frmInput]![cboDay]));

——
James
www.james-mc.com
Words To Live By