|
 |
access thread: Query problem
Message #1 by "Steve Hardy" <hardy@a...> on Thu, 12 Dec 2002 13:29:25
|
|
I need to select certain records from a list as follows, (I did not write
the database and I am not sure it is a good design):
Name, TypeAchievedDate, type1, type2, type3, type4
The typeX are checkbox fields.
A record is entered when a type is achieved. So any employee could have
between 0 and 4 records,
Name1,TypeAchievedDate,type1
Name1,TypeAchievedDate,type2
Name1,TypeAchievedDate,type3
Name1,TypeAchievedDate,type4
I need to find out which employees have not achieved a type1 within 30
days since thier OnHire date, the OnHire date comes from another table. It
is possible for employees to get a type2, 3 or 4 before a type1.
Any suggestions
Thanks
Message #2 by joe.dunn@c... on Thu, 12 Dec 2002 16:53:49 +0000
|
|
From your e-mail posting, it appears that each time entries in Type2,
Type3 and Type4 are irrelevant. If you agree that this is so, you just need
a query that selects where Type1 is True (can be true or false for a
checkbox field).
Construct the query by joining the two relevant tables in the query grid.
Add the NameAchieved and TypeAchievedDate and Type1 fields from the main
table (name not given).
Set the selection criteria under Type1 to True.
Add an expression in the first blank column by right-clicking and selecting
BUILD
Call the expression what you like (say, WANTED) and add a colon (:) after
the expression name
After the expression name and colon, add a function to test whether the
elapsed time between TypeAchievedDate and the OnHire date
e.g.
IIF(DateDiff("d", [TableA].[TypeAchievedDate], [TableB].[OnHire]) < 31,
True, False)
You can test this by running a select query and noting whether the WANTED
column is correctly returning a True (-1) state or a False (0) state. I may
have the order of the two fields reversed so do check this first. Try
Access Help under DateDiff.
One you are happy with this, simply change the criteria under WANTED to
True
You are now selecting only records with:
Type1 = True (i.e. Type1 achieved)
WANTED = True (i.e. Type1 achieved within 30 days of OnHire)
I always prefer to use the query grid rather than write it in SQL but if
you want to use SQL just view SQL code from the VIEW menu and see how it is
put together.
Hope this helps
"Steve Hardy"
<hardy@a...> To: "Access" <access@p...>
cc:
12/12/2002 13:29 Subject: [access] Query problem
Please respond
to "Access"
I need to select certain records from a list as follows, (I did not write
the database and I am not sure it is a good design):
Name, TypeAchievedDate, type1, type2, type3, type4
The typeX are checkbox fields.
A record is entered when a type is achieved. So any employee could have
between 0 and 4 records,
Name1,TypeAchievedDate,type1
Name1,TypeAchievedDate,type2
Name1,TypeAchievedDate,type3
Name1,TypeAchievedDate,type4
I need to find out which employees have not achieved a type1 within 30
days since thier OnHire date, the OnHire date comes from another table. It
is possible for employees to get a type2, 3 or 4 before a type1.
Any suggestions
Thanks
*************************************************************************
This e-mail may contain confidential information or be privileged. It is intended to be read and used only by the named
recipient(s).
If you are not the intended recipient(s) please notify us immediately so that we can make arrangements for its return: you should
not disclose the
contents of this e-mail to any other person, or take any copies. Unless stated otherwise by an authorised individual, nothing
contained in this e-mail is
intended to create binding legal obligations between us and opinions expressed are those of the individual author.
The CIS marketing group, which is regulated for Investment Business by the Personal Investment Authority, includes:
Co-operative Insurance Society Limited Registered in England number 3615R - for life assurance and pensions
CIS Unit Managers Limited Registered in England and Wales number 2369965 (also regulated by IMRO) - for unit trusts and PEPs
CIS Policyholder Services Limited Registered in England and Wales number 3390839 - for ISAs and investment products bearing the CIS
name
Registered offices: Miller Street, Manchester M60 0AL Telephone 0161-832-8686 Internet http://www.cis.co.uk E-mail
cis@c...
CIS Deposit and Instant Access Savings Accounts are held with The Co-operative Bank p.l.c., registered in England and Wales number
990937, P.O. Box 101, 1 Balloon Street, Manchester M60 4EP, and administered by CIS Policyholder Services Limited as agent of the
Bank.
CIS is a member of the General Insurance Standards Council
CIS & the CIS logo (R) Co-operative Insurance Society Limited
********************************************************************************
Message #3 by "Steve Hardy" <hardy@a...> on Thu, 12 Dec 2002 23:59:16
|
|
Thanks for taking the time to respond in such detail. It did not quite do
what I needed but by the time I tried your suggestions and wrote another
question I worked it out.
Thanks again,
>
From your e-mail posting, it appears that each time entries in Type2,
Type3 and Type4 are irrelevant. If you agree that this is so, you just need
a query that selects where Type1 is True (can be true or false for a
checkbox field).
Construct the query by joining the two relevant tables in the query grid.
Add the NameAchieved and TypeAchievedDate and Type1 fields from the main
table (name not given).
Set the selection criteria under Type1 to True.
Add an expression in the first blank column by right-clicking and selecting
BUILD
Call the expression what you like (say, WANTED) and add a colon (:) after
the expression name
After the expression name and colon, add a function to test whether the
elapsed time between TypeAchievedDate and the OnHire date
e.g.
IIF(DateDiff("d", [TableA].[TypeAchievedDate], [TableB].[OnHire]) < 31,
True, False)
You can test this by running a select query and noting whether the WANTED
column is correctly returning a True (-1) state or a False (0) state. I may
have the order of the two fields reversed so do check this first. Try
Access Help under DateDiff.
One you are happy with this, simply change the criteria under WANTED to
True
You are now selecting only records with:
Type1 = True (i.e. Type1 achieved)
WANTED = True (i.e. Type1 achieved within 30 days of OnHire)
I always prefer to use the query grid rather than write it in SQL but if
you want to use SQL just view SQL code from the VIEW menu and see how it is
put together.
Hope this helps
"Steve
Hardy"
<hardy@a...> To: "Access"
<access@p...>
cc:
12/12/2002 13:29 Subject: [access] Query
problem
Please
respond
to "Access"
I need to select certain records from a list as follows, (I did not write
the database and I am not sure it is a good design):
Name, TypeAchievedDate, type1, type2, type3, type4
The typeX are checkbox fields.
A record is entered when a type is achieved. So any employee could have
between 0 and 4 records,
Name1,TypeAchievedDate,type1
Name1,TypeAchievedDate,type2
Name1,TypeAchievedDate,type3
Name1,TypeAchievedDate,type4
I need to find out which employees have not achieved a type1 within 30
days since thier OnHire date, the OnHire date comes from another table. It
is possible for employees to get a type2, 3 or 4 before a type1.
Any suggestions
Thanks
*************************************************************************
This e-mail may contain confidential information or be privileged. It is
intended to be read and used only by the named recipient(s).
If you are not the intended recipient(s) please notify us immediately so
that we can make arrangements for its return: you should not disclose the
contents of this e-mail to any other person, or take any copies. Unless
stated otherwise by an authorised individual, nothing contained in this e-
mail is
intended to create binding legal obligations between us and opinions
expressed are those of the individual author.
The CIS marketing group, which is regulated for Investment Business by the
Personal Investment Authority, includes:
Co-operative Insurance Society Limited Registered in England number 3615R -
for life assurance and pensions
CIS Unit Managers Limited Registered in England and Wales number 2369965
(also regulated by IMRO) - for unit trusts and PEPs
CIS Policyholder Services Limited Registered in England and Wales number
3390839 - for ISAs and investment products bearing the CIS name
Registered offices: Miller Street, Manchester M60 0AL Telephone 0161-
832-8686 Internet http://www.cis.co.uk E-mail cis@c...
CIS Deposit and Instant Access Savings Accounts are held with The Co-
operative Bank p.l.c., registered in England and Wales number 990937, P.O.
Box 101, 1 Balloon Street, Manchester M60 4EP, and administered by CIS
Policyholder Services Limited as agent of the Bank.
CIS is a member of the General Insurance Standards Council
CIS & the CIS logo (R) Co-operative Insurance Society Limited
***************************************************************************
*****
|
|
|
View Cart
