ACCESS FEED: Re: [MS_AccessPros] Query simplifications

Rabu, 12 Februari 2014

Re: [MS_AccessPros] Query simplifications

Thanks, John. That will help me in sorting through some of the others, which I may post here as well if I can't figure them out.

——

James

www.james-mc.com

Words To Live By

From: John Viescas John Viescas
Reply: ms_access_professionals@yahoogroups.com ms_access_professionals@yahoogroups.com
Date: February 12, 2014 at 11:16:15 AM
To: ms_access_professionals@yahoogroups.com ms_access_professionals@yahoogroups.com
Subject: 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