Macros to run update queries

[Willow]

Hi

I am currently trying to automate a number of databases so that they
can be run 'at the push of a button'.

The first of these databases contains my master tables which are raw
downloads from and antiquated mainframe system. The data comes down in
text file format which is then imported into Access Tables. I then run
a number of update queries against the tables to convert dates and
codes etc into meaningful data.

Previously I have run these update queries manually but now intend to
use a number of macros to be able to run them at the push of a button.
I am mainly using the OpenQuery function.

However, while this is fine for most of the update queries, there are
a number of queries where the Update to information needs updating
before the query is run.

Basically the update to works out the age of the person at the end of
the month:

Int(((#31/03/2007#-[Master table - CIS Assessments]!DOB))/365.25)

Obviously the date needs changing each month before the update query
can be run.

Is there a way of doing this with a macro or does it need to be
coded?

Thanks
Helena

 

[Steve Schapel]

Helena,

You can use an expression within your query, to return the age at the
end of the current month.

The concept you are using is not the correct way to calculate age from
DOB. It will not be accurate. Try this instead...

DateDiff("yyyy",[DOB],DateSerial(Year(Date()),Month(Date())+1,0))+(Format([DOB],"mmdd")>Format(Date(),"mmdd"))

However, having said that, it sounds like you may have an Age field in
your table, that you are updating every month via the Update Query. Is
that right? If so, this would normally be regarded as an invalid
procedure. Age is a calculated value, and should never be stored in a
database table.

 

[Willow]

Steve

Thanks for the reply, however, calculating the age at the end of the
current month wouldn't work for me, as the data is usually run two
weeks in arrears.

For example, data up to and including 31st March 2007, where we need
to know the age at 31st March 2007, would not be run until mid-April
(to give staff chance to get all the relevant information entered onto
the mainframe system), hence the date parameter being set in the
query. This calculation is regularly checked and does produce an
accurate result.

The age field is in the table and needs to remain there as, after the
raw data has been updated (including the age field) the completed
table is then exported and used in several other databases and has
several queries running from it to calculate different things, many of
which are dependant on the information in the age field. If this field
was not in the table and had to be seperately calculated for each
query, it would mean the addition of around 100 queries to four
different databases. (There are several tables which have calculated
age fields in)

While I appreciate that this might not be a normally valid way of
doing things, I am not aware of any other way of getting the required
result without the addition of a ridiculously high number of
additional queries, and the process has worked for many years with
accurate results. However, as I have previously said, this has always
been done manually before (update query opened in design view, data
parameter changed, update query run) - it is the automation of the
process that I am struggling with.

Any ideas greatly appreciated.

Thanks
Helena

Helena,

You can use an expression within your query, to return the age at the
end of the current month.

The concept you are using is not the correct way to calculate age from
DOB. It will not be accurate. Try this instead...

DateDiff("yyyy",[DOB],DateSerial(Year(Date()),Month(Date())+1,0))+(Format([­DOB],"mmdd")>Format(Date(),"mmdd"))

However, having said that, it sounds like you may have an Age field in
your table, that you are updating every month via the Update Query. Is
that right? If so, this would normally be regarded as an invalid
procedure. Age is a calculated value, and should never be stored in a
database table.

--
Steve Schapel, Microsoft Access MVP

Hi

I am currently trying to automate a number of databases so that they
can be run 'at the push of a button'.

The first of these databases contains my master tables which are raw
downloads from and antiquated mainframe system. The data comes down in
text file format which is then imported into Access Tables. I then run
a number of update queries against the tables to convert dates and
codes etc into meaningful data.

Previously I have run these update queries manually but now intend to
use a number of macros to be able to run them at the push of a button.
I am mainly using the OpenQuery function.

However, while this is fine for most of the update queries, there are
a number of queries where the Update to information needs updating
before the query is run.

Basically the update to works out the age of the person at the end of
the month:

Int(((#31/03/2007#-[Master table - CIS Assessments]!DOB))/365.25)

Obviously the date needs changing each month before the update query
can be run.

Is there a way of doing this with a macro or does it need to be
coded?

Thanks
Helena- Hide quoted text -

- Show quoted text -

 

[Steve Schapel]

Willow,

Thanks for the reply, however, calculating the age at the end of the
current month wouldn't work for me, as the data is usually run two
weeks in arrears.

My apologies, since you didn't say, I assumed your original question
implied that you were talking about the current month. For the previous
month, change the expression to:
DateDiff("yyyy",[DOB],DateSerial(Year(Date()),Month(Date()),0))+(Format([­DOB],"mmdd")>Format(DateAdd("m",-1,Date()),"mmdd"))

... This calculation is regularly checked and does produce an
accurate result.

I just tested it for myself, and my very first example was wrong! I am
56. My birthday is 4 November. So, on 31 October this year, I will
still be 56. Ok. Your formula:
Int(((#31/10/2007#-#4/11/1950#))/365.25)
.... returns 57. Not correct.

.... If this field
was not in the table and had to be seperately calculated for each
query, it would mean the addition of around 100 queries to four
different databases. (There are several tables which have calculated
age fields in)

I understand. Well, I can see that it would be an unacceptably big job
to change your data schema. But I can assure you that if the system had
been designed differently at the outset, it could be a lot simpler and
more efficient than it is. So, let's just leave it at that.

Bit the main thing is, hopefully using the expression I gave you in your
update queries will do the trick, and save you from having to manually
re-enter the date parameter every month.

 

[Steve Schapel]

I'm sorry, Willow. I am wrong here. There was a American date
formatting confusion. I will check again later.

 

[Steve Schapel]

Willow,

I see that your formula is more accurate than I thought. Especially now
that most people's ages at the moment span the year 2000 leap year
correction. It seems it will only give an incorrect result now in
unusual circumstances, such as a person's birthday falling on the last
day of the month. For example, for somebody born on 30-Sep-1950, they
will turn 57 on 30-Sep-2007, but the formula:
Int(((#30/9/2007#-#30/9/1950#))/365.25)
.... returns that they are still 56.

Anyway, this is all "academic", and tangential to your original
question. So I hope you were able to use the expression I gave you,
within the update queries.