How do i run two queries at once?

[Guest]

is it possible to update two columns after each other, using one query?
ie...
i need to update an AGE field from Date of Birth of a person and then after
that, update a field to flag people in certain age groups?

i can do this running two different queries but can it be done in one??

please help

 

['69 Camaro]

Hi, Picasso.

i need to update an AGE field from Date of Birth of a person and then
after
that, update a field to flag people in certain age groups?

It's generally not a good idea to place a calculated value in a table,
because they quickly get out of date. I suggest you create a query that
calculates the person's age as of today in one column, and set the column
for the certain age groups based upon the value in the calculated age
column. That way the values in those two columns will never need to be
updated, so they'll never be out of date. They'll be today's values.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[Keith Wilby]

is it possible to update two columns after each other, using one query?
ie...
i need to update an AGE field from Date of Birth of a person and then
after
that, update a field to flag people in certain age groups?

i can do this running two different queries but can it be done in one??

please help

You don't need to store these values (and indeed you *shouldn't*) at all
because they can be calculated at run-time. Age is Date() - DOB. If you
post details of your other requirements for age groups I'm sure me or
someone else will be able to help.

HTH - Keith.
www.keithwilby.com

 

[Guest]

Gunny, thanks that solves my AGE problem but i do still need to find out if i
can run two queries as one...AGE might have been a bad exaple...
lets use a list of numbers...
ie
in ColumnA i have a range of Nrs...say 1-50 and the same for ColumnB,
40-300...
i want to update ColumnC to calculate A+B and then in ColumnD, update
ColumnD to have a value "over_100" if ColumnC is >100.
thus i want to do the calculation in ColumnC and the update in ColumnD at
the same time as one query or in one statement.

 

['69 Camaro]

Hi, Picasso.

i want to update ColumnC to calculate A+B and then in ColumnD, update
ColumnD to have a value "over_100" if ColumnC is >100.
thus i want to do the calculation in ColumnC and the update in ColumnD at
the same time as one query or in one statement.

My advice is the same. Calculate ColumnC "on the fly" in a SELECT query,
and ColumnD can also be calculated on the fly, too, based upon whatever
value is in ColumnC. Storing values in the table for ColumnC and ColumnD
will compromise data integrity, because one cannot guarantee that the
calculated values in ColumnC and ColumnD are based on the current values in
ColumnA and ColumnB, unless the updates to the whole table were just made
and no other user has access to the table to make changes before these
values are read. This is not an advisable scenario in a database.

The requirements of a normalized table dictate that no column is dependent
upon any other column except for the primary key. In your scenario, ColumnC
depends upon Columns A and B never changing, and ColumnD depends upon
ColumnC never changing. It is soooo easy to change Columns A or B, without
bothering to change Columns C and D, so your stored calculations will be
wrong. That's why it's much better to calculate the two columns on the fly,
regardless of whether or not columns A or B have been changed since the last
time a particular user entered or updated those records.

Quitting and turning the computer off for the day. . . .

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[Guest]

OK maybe i'm using the wrong exaples all the time??
Say for instance i have a once off table that will never change and no one
but me has access to the table. Two tables, 'CustomerList' and 'BadCustomers'
i want to match the customers in my CustomerList to see if there are any
customers with bad records with the company. but in my 'BadCustomers' table i
have the customers details as 'J SMITH' in one field and 'J' 'SMITH' as two
fields in my CustomerList table.
thus to concatenate the Initails and LastName ...
query1...
update CustomerList
set [JointName] = [Initial]&' '&[LastName]

query2
delete CustomerList.*
where [CustomerList].[JointName] = [BadCustomers].[Name]

My question is this...regardless of database ethics and should or should not
be done or which is easier that something else...
can i, instead of running two different queries, run this as one query or
one function? ie...in SQL Server you can do this as two statement and put
'go' between the statements wich would result in SQL running the second
statement as soon as the first one is done

 

[John Spencer]

No, you cannot run two queries as one query.

Even in MS SQL Server you are executing separate queries. You just have
syntax that lets you put them into one container.

In Access, you can use a VBA function to run the queries in sequence. (or a
macro).

AIR CODE Follows - no error handler, no transaction,


Public Function fRunMultipleQueries()
Dim dbAny as DAO.Database
'Only action queries (Append, Update, Delete)
'and data definition queries allowed

Set dbAny = dbEngine(0)(0)
dbAny.Execute "NameOfQuery1", dbFailOnError

dbAny.Execute "NameOfQuery2", dbFailOnError

End Function



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

OK maybe i'm using the wrong exaples all the time??
Say for instance i have a once off table that will never change and no one
but me has access to the table. Two tables, 'CustomerList' and
'BadCustomers'
i want to match the customers in my CustomerList to see if there are any
customers with bad records with the company. but in my 'BadCustomers'
table i
have the customers details as 'J SMITH' in one field and 'J' 'SMITH' as
two
fields in my CustomerList table.
thus to concatenate the Initails and LastName ...
query1...
update CustomerList
set [JointName] = [Initial]&' '&[LastName]

query2
delete CustomerList.*
where [CustomerList].[JointName] = [BadCustomers].[Name]

My question is this...regardless of database ethics and should or should
not
be done or which is easier that something else...
can i, instead of running two different queries, run this as one query or
one function? ie...in SQL Server you can do this as two statement and put
'go' between the statements wich would result in SQL running the second
statement as soon as the first one is done

You don't need to store these values (and indeed you *shouldn't*) at all
because they can be calculated at run-time. Age is Date() - DOB. If you
post details of your other requirements for age groups I'm sure me or
someone else will be able to help.

HTH - Keith.
www.keithwilby.com

 

[Guest]

OK now that's what i'm talking about...tahnks a mill and i'll give it a try.
i'm not a whiz in this but i'll give you a shout if i don't get it right...

thanks again

No, you cannot run two queries as one query.

Even in MS SQL Server you are executing separate queries. You just have
syntax that lets you put them into one container.

In Access, you can use a VBA function to run the queries in sequence. (or a
macro).

AIR CODE Follows - no error handler, no transaction,


Public Function fRunMultipleQueries()
Dim dbAny as DAO.Database
'Only action queries (Append, Update, Delete)
'and data definition queries allowed

Set dbAny = dbEngine(0)(0)
dbAny.Execute "NameOfQuery1", dbFailOnError

dbAny.Execute "NameOfQuery2", dbFailOnError

End Function



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

OK maybe i'm using the wrong exaples all the time??
Say for instance i have a once off table that will never change and no one
but me has access to the table. Two tables, 'CustomerList' and
'BadCustomers'
i want to match the customers in my CustomerList to see if there are any
customers with bad records with the company. but in my 'BadCustomers'
table i
have the customers details as 'J SMITH' in one field and 'J' 'SMITH' as
two
fields in my CustomerList table.
thus to concatenate the Initails and LastName ...
query1...
update CustomerList
set [JointName] = [Initial]&' '&[LastName]

query2
delete CustomerList.*
where [CustomerList].[JointName] = [BadCustomers].[Name]

My question is this...regardless of database ethics and should or should
not
be done or which is easier that something else...
can i, instead of running two different queries, run this as one query or
one function? ie...in SQL Server you can do this as two statement and put
'go' between the statements wich would result in SQL running the second
statement as soon as the first one is done

is it possible to update two columns after each other, using one query?
ie...
i need to update an AGE field from Date of Birth of a person and then
after
that, update a field to flag people in certain age groups?

i can do this running two different queries but can it be done in one??

please help

You don't need to store these values (and indeed you *shouldn't*) at all
because they can be calculated at run-time. Age is Date() - DOB. If you
post details of your other requirements for age groups I'm sure me or
someone else will be able to help.

HTH - Keith.
www.keithwilby.com