how do i?

[George Applegate]

Questions on an access data base:

I have two tables in a data base
Employee daily ins/outs (time clock)
Employee master file

I am considering have different queries access different data (i.e.
selected department), but also by a date range. I know how to Prompt
for date range, i.e. Enter start date, enter end date. But I'd like
to refine this a bit further. All departments will want the same date
range.

Is there a way I can put the date range I want to use into another
table and then selecte records from the daily ins/outs by referencing
the dates in another table?

Right now I've been putting in the one query hard-coded dates, i.e.
logdate > 04/26/2008. But with say 20 departments, I don't want to do
that; I want separate queries set for each department so I can put
security on that they can only query their own department. But I
would like to put that 04/26/2008 somewhere in another table or
something so I only have to enter it once, and then all 20
departmental queries use that date.

Any suggestions?
thanks, ga
George Applegate
(e-mail address removed)

 

[John Spencer]

If you have a table with just one record and a StartDateField and an
EndDateField, then you can include the table in the query AND NOT JOIN IT to
any other table. Then the table and field will be available for the query.

OR you can use the DLOOKUP function to grab the field from the table.

Solution One:
Field: TransactionDate
Table: SomeTable
Criteria: >= [OneRecordTable].[StartDateField]

Solution Two:
Field: TransactionDate
Table: SomeTable
Criteria: >= DLookup("StartDateField","OneRecordTable")


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

 

[Ron2006]

If you end up using the table, then I suggest you add the department
to that table then you will NOT need to have a different query for
every department.

Use the same query and compare the data department with the or for
that matter like the department in the table to the data department.

That will work for any single department selection. and can be used
even if you don't put the dates in the table.

For that matter if you create a record for each department allowed/
desired for that particular run of the report, it will work for that
also.

Ron

 

[George Applegate]

John and Ron,

Worked like a charm!!!

Thanks for the suggestions and QUICK replies!!! Much appreciated!
ga

If you have a table with just one record and a StartDateField and an
EndDateField, then you can include the table in the query AND NOT JOIN IT to
any other table. Then the table and field will be available for the query.

OR you can use the DLOOKUP function to grab the field from the table.

Solution One:
Field: TransactionDate
Table: SomeTable
Criteria: >= [OneRecordTable].[StartDateField]

Solution Two:
Field: TransactionDate
Table: SomeTable
Criteria: >= DLookup("StartDateField","OneRecordTable")


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

Questions on an access data base:

I have two tables in a data base
Employee daily ins/outs (time clock)
Employee master file

I am considering have different queries access different data (i.e.
selected department), but also by a date range. I know how to Prompt
for date range, i.e. Enter start date, enter end date. But I'd like
to refine this a bit further. All departments will want the same date
range.

Is there a way I can put the date range I want to use into another
table and then selecte records from the daily ins/outs by referencing
the dates in another table?

Right now I've been putting in the one query hard-coded dates, i.e.
logdate > 04/26/2008. But with say 20 departments, I don't want to do
that; I want separate queries set for each department so I can put
security on that they can only query their own department. But I
would like to put that 04/26/2008 somewhere in another table or
something so I only have to enter it once, and then all 20
departmental queries use that date.

Any suggestions?
thanks, ga
George Applegate
(e-mail address removed)

George Applegate
(e-mail address removed)

 

[George Applegate]

John...or anyone...

I need help!!!

I _thought_ it worked like a charm but now I have another issue.

Actually, I have three tables in this data-base

Table 1 - time card transactions
Table 2 - Employee master
Table 3 - Date table

I have table one linked to table two on employee #.

I added table 3, the date table, but DID NOT LINK IT. All I want to
do is get records with a transaction date greater than the date in my
date table file.

But once I added this table to the query, it selects the correct
records, which is great...but now I cannot enter data into any of the
fields in the query...Why would this have happened? It seems like all
the fields are protected.

Any ideas or suggestions????????

John and Ron,

Worked like a charm!!!

Thanks for the suggestions and QUICK replies!!! Much appreciated!
ga

If you have a table with just one record and a StartDateField and an
EndDateField, then you can include the table in the query AND NOT JOIN IT to
any other table. Then the table and field will be available for the query.

OR you can use the DLOOKUP function to grab the field from the table.

Solution One:
Field: TransactionDate
Table: SomeTable
Criteria: >= [OneRecordTable].[StartDateField]

Solution Two:
Field: TransactionDate
Table: SomeTable
Criteria: >= DLookup("StartDateField","OneRecordTable")


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

Questions on an access data base:

I have two tables in a data base
Employee daily ins/outs (time clock)
Employee master file

I am considering have different queries access different data (i.e.
selected department), but also by a date range. I know how to Prompt
for date range, i.e. Enter start date, enter end date. But I'd like
to refine this a bit further. All departments will want the same date
range.

Is there a way I can put the date range I want to use into another
table and then selecte records from the daily ins/outs by referencing
the dates in another table?

Right now I've been putting in the one query hard-coded dates, i.e.
logdate > 04/26/2008. But with say 20 departments, I don't want to do
that; I want separate queries set for each department so I can put
security on that they can only query their own department. But I
would like to put that 04/26/2008 somewhere in another table or
something so I only have to enter it once, and then all 20
departmental queries use that date.

Any suggestions?
thanks, ga
George Applegate
(e-mail address removed)

George Applegate
(e-mail address removed)

George Applegate
(e-mail address removed)

 

[John Spencer]

There are many reasons for not being able to update a query's contents.
You have run into one.

Since the Date Table is not joined to the other table and probably does
not have a primary key, you have a problem.

If you do not need to display the date in the results. You can use a
subquery to get the value.

Field: TimeCardDate
Table: Time Card Transactions
Criteria: In (SELECT TheDateField from [Date Table])


Or use the DLookup function
Field: TimeCardDate
Table: Time Card Transactions
Criteria: = DLookup("TheDateField","Date Table")

If you want to display the value of TheDateField in the query result,
then add the DLookup expression as a calculated field in addition to
using the expression as criteria.


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

John...or anyone...

I need help!!!

I _thought_ it worked like a charm but now I have another issue.

Actually, I have three tables in this data-base

Table 1 - time card transactions
Table 2 - Employee master
Table 3 - Date table

I have table one linked to table two on employee #.

I added table 3, the date table, but DID NOT LINK IT. All I want to
do is get records with a transaction date greater than the date in my
date table file.

But once I added this table to the query, it selects the correct
records, which is great...but now I cannot enter data into any of the
fields in the query...Why would this have happened? It seems like all
the fields are protected.

Any ideas or suggestions????????

John and Ron,

Worked like a charm!!!

Thanks for the suggestions and QUICK replies!!! Much appreciated!
ga

If you have a table with just one record and a StartDateField and an
EndDateField, then you can include the table in the query AND NOT JOIN IT to
any other table. Then the table and field will be available for the query.

OR you can use the DLOOKUP function to grab the field from the table.

Solution One:
Field: TransactionDate
Table: SomeTable
Criteria: >= [OneRecordTable].[StartDateField]

Solution Two:
Field: TransactionDate
Table: SomeTable
Criteria: >= DLookup("StartDateField","OneRecordTable")


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

George Applegate wrote:
Questions on an access data base:

I have two tables in a data base
Employee daily ins/outs (time clock)
Employee master file

I am considering have different queries access different data (i.e.
selected department), but also by a date range. I know how to Prompt
for date range, i.e. Enter start date, enter end date. But I'd like
to refine this a bit further. All departments will want the same date
range.

Is there a way I can put the date range I want to use into another
table and then selecte records from the daily ins/outs by referencing
the dates in another table?

Right now I've been putting in the one query hard-coded dates, i.e.
logdate > 04/26/2008. But with say 20 departments, I don't want to do
that; I want separate queries set for each department so I can put
security on that they can only query their own department. But I
would like to put that 04/26/2008 somewhere in another table or
something so I only have to enter it once, and then all 20
departmental queries use that date.

Any suggestions?
thanks, ga
George Applegate
(e-mail address removed)

George Applegate
(e-mail address removed)

George Applegate
(e-mail address removed)

 

[George Applegate]

John,

The DLookup fixed it. THANKS MUCH!!!

ga

There are many reasons for not being able to update a query's contents.
You have run into one.

Since the Date Table is not joined to the other table and probably does
not have a primary key, you have a problem.

If you do not need to display the date in the results. You can use a
subquery to get the value.

Field: TimeCardDate
Table: Time Card Transactions
Criteria: In (SELECT TheDateField from [Date Table])


Or use the DLookup function
Field: TimeCardDate
Table: Time Card Transactions
Criteria: = DLookup("TheDateField","Date Table")

If you want to display the value of TheDateField in the query result,
then add the DLookup expression as a calculated field in addition to
using the expression as criteria.


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

John...or anyone...

I need help!!!

I _thought_ it worked like a charm but now I have another issue.

Actually, I have three tables in this data-base

Table 1 - time card transactions
Table 2 - Employee master
Table 3 - Date table

I have table one linked to table two on employee #.

I added table 3, the date table, but DID NOT LINK IT. All I want to
do is get records with a transaction date greater than the date in my
date table file.

But once I added this table to the query, it selects the correct
records, which is great...but now I cannot enter data into any of the
fields in the query...Why would this have happened? It seems like all
the fields are protected.

Any ideas or suggestions????????

John and Ron,

Worked like a charm!!!

Thanks for the suggestions and QUICK replies!!! Much appreciated!
ga


If you have a table with just one record and a StartDateField and an
EndDateField, then you can include the table in the query AND NOT JOIN IT to
any other table. Then the table and field will be available for the query.

OR you can use the DLOOKUP function to grab the field from the table.

Solution One:
Field: TransactionDate
Table: SomeTable
Criteria: >= [OneRecordTable].[StartDateField]

Solution Two:
Field: TransactionDate
Table: SomeTable
Criteria: >= DLookup("StartDateField","OneRecordTable")


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

George Applegate wrote:
Questions on an access data base:

I have two tables in a data base
Employee daily ins/outs (time clock)
Employee master file

I am considering have different queries access different data (i.e.
selected department), but also by a date range. I know how to Prompt
for date range, i.e. Enter start date, enter end date. But I'd like
to refine this a bit further. All departments will want the same date
range.

Is there a way I can put the date range I want to use into another
table and then selecte records from the daily ins/outs by referencing
the dates in another table?

Right now I've been putting in the one query hard-coded dates, i.e.
logdate > 04/26/2008. But with say 20 departments, I don't want to do
that; I want separate queries set for each department so I can put
security on that they can only query their own department. But I
would like to put that 04/26/2008 somewhere in another table or
something so I only have to enter it once, and then all 20
departmental queries use that date.

Any suggestions?
thanks, ga
George Applegate
(e-mail address removed)
George Applegate
(e-mail address removed)

George Applegate
(e-mail address removed)

George Applegate
(e-mail address removed)

 

[George Applegate]

John,

Do you do any access programming as a fee-based service? Just
wondered if you would be interested in doing some work for my company
on a small project, or if you could recommend someone, if you were not
interested.

I really think it's a pretty small project and wouldn't take a lot of
hours, but we want to set it up right and my expreience in access is
primitive, as you can tell.

Hopefully I'm not violating the rules of this newsgroup by posting
this.

There are many reasons for not being able to update a query's contents.
You have run into one.

Since the Date Table is not joined to the other table and probably does
not have a primary key, you have a problem.

If you do not need to display the date in the results. You can use a
subquery to get the value.

Field: TimeCardDate
Table: Time Card Transactions
Criteria: In (SELECT TheDateField from [Date Table])


Or use the DLookup function
Field: TimeCardDate
Table: Time Card Transactions
Criteria: = DLookup("TheDateField","Date Table")

If you want to display the value of TheDateField in the query result,
then add the DLookup expression as a calculated field in addition to
using the expression as criteria.


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

John...or anyone...

I need help!!!

I _thought_ it worked like a charm but now I have another issue.

Actually, I have three tables in this data-base

Table 1 - time card transactions
Table 2 - Employee master
Table 3 - Date table

I have table one linked to table two on employee #.

I added table 3, the date table, but DID NOT LINK IT. All I want to
do is get records with a transaction date greater than the date in my
date table file.

But once I added this table to the query, it selects the correct
records, which is great...but now I cannot enter data into any of the
fields in the query...Why would this have happened? It seems like all
the fields are protected.

Any ideas or suggestions????????

John and Ron,

Worked like a charm!!!

Thanks for the suggestions and QUICK replies!!! Much appreciated!
ga


If you have a table with just one record and a StartDateField and an
EndDateField, then you can include the table in the query AND NOT JOIN IT to
any other table. Then the table and field will be available for the query.

OR you can use the DLOOKUP function to grab the field from the table.

Solution One:
Field: TransactionDate
Table: SomeTable
Criteria: >= [OneRecordTable].[StartDateField]

Solution Two:
Field: TransactionDate
Table: SomeTable
Criteria: >= DLookup("StartDateField","OneRecordTable")


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

George Applegate wrote:
Questions on an access data base:

I have two tables in a data base
Employee daily ins/outs (time clock)
Employee master file

I am considering have different queries access different data (i.e.
selected department), but also by a date range. I know how to Prompt
for date range, i.e. Enter start date, enter end date. But I'd like
to refine this a bit further. All departments will want the same date
range.

Is there a way I can put the date range I want to use into another
table and then selecte records from the daily ins/outs by referencing
the dates in another table?

Right now I've been putting in the one query hard-coded dates, i.e.
logdate > 04/26/2008. But with say 20 departments, I don't want to do
that; I want separate queries set for each department so I can put
security on that they can only query their own department. But I
would like to put that 04/26/2008 somewhere in another table or
something so I only have to enter it once, and then all 20
departmental queries use that date.

Any suggestions?
thanks, ga
George Applegate
(e-mail address removed)
George Applegate
(e-mail address removed)

George Applegate
(e-mail address removed)

George Applegate
(e-mail address removed)