affect all records

[Dazza666]

Hi,

i've got a command button on a form that automatically fills or changes textboxes based on the date and someones date of birth.

Is there a way I can make the VBA affect all records at one instead of having to press the button on all records individually?

 

[gemma-the-husky]

use a stored query to update all the records

at the worst, you might need to write a function to calculate the new values, if its too complex to do "inline"

 

[Dazza666]

use a stored query to update all the records

at the worst, you might need to write a function to calculate the new values, if its too complex to do "inline"

thank you for the quick response,

I'll need to do some research on stored queries but maybe there's a better way,

my issue is the following,

I need to generate statistics based on peoples date of birth, the statistics are age groups e.g. (<18, 18-24yrs, 24+ etx), this is really a calculated value and i don't want it stored for obvious reasons, but i can't see another way right now.

I can't work out how else to get my query to work.

When i store the ageGroup as a field in a table i can write a query using order by that simply displays the age group and number of records in that category, this works fine but the data has no integrity as the ageGroup clearly won't update with the records age as time goes on.

I can't work out how to write a query to do the ageGroup calculation if it's not a stored value. Does this make sense?

thanks

 

[Brianwarnock]

This requires the Sum if approach to counting.

Assume DOB is date of birth and you have a function fgetage to do the age calculation, the following type of expression in a Totals query with Sum selected will count all of the ages between 6 and 15.

age6_15: IIf(fgetage([dob],Date())>5 And fgetage([dob],Date())<=15,1,0)


brian

 

[Dazza666]

This requires the Sum if approach to counting.

Assume DOB is date of birth and you have a function fgetage to do the age calculation, the following type of expression in a Totals query with Sum selected will count all of the ages between 6 and 15.

age6_15: IIf(fgetage([dob],Date())>5 And fgetage([dob],Date())<=15,1,0)


brian

Thanks for this,

is it possible to use this multiple times in a query for each AgeGroup i.e. <16, 17-24,25-45,46-60,61+???

 

[Brianwarnock]

Thanks for this,

is it possible to use this multiple times in a query for each AgeGroup i.e. <16, 17-24,25-45,46-60,61+???

Yes , infact that is the whole point of it, sorry I realise I should have said that, its amazing what you take for granted.

Brian

 

[Dazza666]

Yes , infact that is the whole point of it, sorry I realise I should have said that, its amazing what you take for granted.

Brian

Brilliant!

thanks for your help

 

[gemma-the-husky]

its still easiest to do this sort of stuff with a querybydesign query

ie in the database windows, click queries, new

drop in the tables you need, and design the query, until you get what you want - that IS a stored query - you can see the sql you generated by selecting view, sql

you can achieve yout subtotals etc, by designing the query - add a column to generate the appropriate age grouping, [see below - treat this is a breakpoint] then change the query into a totals query, to get overall statistics.

you dont have to do it all in one query - you can eg, save the data selection in one query [breakpoint referred to above], then use that query as the base for the final totals query. - indeed sometimes you HAVE to do it in stages to get the cottrcy final result

 

[Brianwarnock]

Gemma
Not sure what your point is.

its still easiest to do this sort of stuff with a querybydesign query

ie in the database windows, click queries, new

drop in the tables you need, and design the query, until you get what you want - that IS a stored query - you can see the sql you generated by selecting view, sql

Doesn't this apply to all queries done with the query design grid? I always start there even if I later tweak the SQL.

Didn't follow the rest of your post. To simplify the IIF statements one could initially run a query to calaculate the ages and then take that into the summary query but there seems little point, would there be a performance issue with a lot of grouping, traded off against the number of records?

I remain puzzled by your post.

Brian

 

[gemma-the-husky]

brian

i thought the op didn't understand what a stored query was - perhaps it was just semantics - i thought he was trying to write sql directly to get the results he wanted