ACCESS FEED: RE: [MS_AccessPros] Query questions

Minggu, 12 Januari 2014

RE: [MS_AccessPros] Query questions

Thanks, Duane!

Yes, these are flat files of election data, and they come to me this way. I run a number of queries and then return the finished product to the client, who wants them in the same flat file structure.

It looks like I'll continue to run the two queries then, but I will out the second argument in the NZ function. And I'll also use Dlookup to pull in the election date.

Thanks for the help!

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

Sent from Mailbox on the iPhone 5

On Sun, Jan 12, 2014 at 2:17 PM, Duane Hookom <duanehookom@hotmail.com> wrote:

James,

It looks like you are treating an Access table like an Excel spreadsheet with calculated columns storing totals. It seems every field name you have is storing data. Do you have a good reason for such an un-normalized table?

Each query updates PVGPrimaryDem and PVBPrimaryGOP to different values. I'm not sure how you would want to handle this. Also, I never use Nz() without providing 2 arguments. Every Nz() you are using has only the first argument and probably assumes a 0 for the second argument.

You can use DLookup("ElectionDate","Settings") to pull the date from another table.

Duane Hookom MVP
MS Access

From: kc5qeg@gmail.com
A couple of questions here.

1. I have two queries that I'd like to combine into one if possible:

Query 1

UPDATE Registrations SET Registrations.PVGPrimaryGOP = Nz(IIf([registrations]![Primary2004PartyVoted]="R",1,0))+Nz(IIf([registrations]![Primary2006PartyVoted]="R",1,0))+Nz(IIf([registrations]![PresPrimary2008PartyVoted]="R",1,0))+Nz(IIf([registrations]![Primary2010PartyVoted]="R",1,0))+Nz(IIf([registrations]![Primary2012PartyVoted]="R",1,0)), Registrations.PVGPrimaryDem = Nz(IIf([registrations]![Primary2004PartyVoted]="D",1,0))+Nz(IIf([registrations]![Primary2006PartyVoted]="D",1,0))+Nz(IIf([registrations]![PresPrimary2008PartyVoted]="D",1,0))+Nz(IIf([registrations]![Primary2010PartyVoted]="D",1,0))+Nz(IIf([registrations]![Primary2012PartyVoted]="D",1,0)), Registrations.PVGPrimary = Nz([registrations]![Primary2004])+Nz([registrations]![Primary2006])+Nz([registrations]![Primary2008])+Nz([registrations]![Primary2010])+Nz([registrations]![Primary2012]), Registrations.PVGGeneral = Nz([registrations]![General2004])+Nz([registrations]![General2006])+Nz([registrations]![General2008])+Nz([registrations]![General2010])+Nz([registrations]![General2012]);

Query 2

UPDATE Registrations SET Registrations.PVGGeneral = 6, Registrations.PVGPrimary = 6, Registrations.PVGPrimaryDem = 6, Registrations.PVGPrimaryGOP = 6

WHERE (((Registrations.PVGGeneral)=0) AND ((Registrations.PVGPrimary)=0) AND ((Registrations.PVGPrimaryDem)=0) AND ((Registrations.PVGPrimaryGOP)=0) AND ((Registrations.date_of_registration)>#11/6/2012#));

How can I combine them to populate the four PVG fields with numbers (0 through 6) in a single operation? Right now I run Query 1(to put in 0 -6) first and then Query 2 (to put in 6), which I occasionally forget to do.

2. In query 2 I have a fixed date (#11/6/2012#) which I'd like to put in the ElectionDate field in a separate table (Settings). If I do this, how do I refer to it in the query?

I'm using Access 2010 here.

Thanks for the help!

——

James

www.james-mc.com

Words To Live By

Sent from Airmail app on Mac