Check box results into a query

[mickeyb]

Hi, I have table which has about 10 Yes/No fields. Each record also has a key/record number and as each record represents a press cutting, there is also a cutting readership figure. I am trying to put together a query which will total up the readership for each of the 10 fields which have a 'yes' in them. Ideally the query should return a listing for the 10 Yes/No variables followed by the sum of readership. I am far from being a semi-capable programmer so apologies if this defies logic!

 

[John Big Booty]

Firstly welcome to the forum.

Any chance of posting a copy of your DB?

 

[mickeyb]

Hi John, Thanks for your help. I have attached the mdb. I would really appreciate any guidance or pointer you might be able to give me! Thanks

 

[jrsl]

You could select each one like this:

SELECT RecordEntryT.RecordNumber, RecordEntryT.Date, RecordEntryT.Month, RecordEntryT.MediaTitle, RecordEntryT.Audience, RecordEntryT.[Business advice and information], RecordEntryT.[To become the one govt site for all businesses], RecordEntryT.[Helps businesses succeed], RecordEntryT.[Advice and information is authoritative], RecordEntryT.[Customers highly value the service], RecordEntryT.[For all types & size of business, not just start ups], RecordEntryT.[Advisors have personal experience of running a business], RecordEntryT.[Is making a difference to UK businesses], RecordEntryT.[Helpline number], RecordEntryT.[Website Ref], RecordEntryT.[Service is free] AS TOTAL
FROM RecordEntryT
WHERE (((RecordEntryT.[Business advice and information])=Yes));

Thats for table: Business advice and information

256 people have clicked it. change:

WHERE (((RecordEntryT.[Business advice and information])=Yes));

To the next table and so on.

Sorry i didnt have much time and the is the fastest way for me to help you in some way.

For refrance tho, it is not a good idea to have your table names like that. Try and name them without spaces:
like "BusAdInfo" for example.

Cheers,
Justin

 

[John Big Booty]

Is the attached the sort of thing you are looking for?

BTW, there is no need to also store the month name given that you are already storing the date. You can extract the month from the date by using;

=MonthName(Month([Date]))

as the control source for an unbound text box.

As jrsl points out it is a bad idea to use spaces and other special characters in field and object names, as this complicates reference to them when you start using VBA, and will case you no end of grief. Also consider using a naming protocol to name objects for example TBL_TableName, FRM_FormName, QRY_QueryName etc.

 

[jrsl]

Nice work john

 

[mickeyb]

Hi, Thanks for the reply (particularly John) I need to sit down and really figure what you have done as it looks really interested). Point taken about Table and Query names, a useful lesson!