Help with Queries

[Guest]

Hi,

I have a 1 table that named Budgeted Hours, with fields that have a Date and
Budgeted Hours columns. Next I have another table named Vacation Hours that
stores Employee Name, Date & Hours requested. I need to have a query that
grabs the "Hours Requested" and subtract that from the "Budgeted Hours" and
display the hours available in the budget. I have a relationship tied to the
Date column of both tables but I get a mismatch error. Can someone please
help!!

Any help will be greatly appreciated.

 

[Jeff Boyce]

Michael

A couple things to consider...

First, if your columns are actually named "Date", both Access and you will
be confused. This is a reserved word in Access. Try modifying the field
name, maybe to [BudgetDate] and [VacationDate].

Next, you mention a data type mismatch but you don't describe what data
types your fields are.

Finally, you appear to be trying to connect the two tables together by the
fields holding date information. How do you intend to handle more than one
employee/vacation on any given date that might show up in the BudgetedHours
table?

This may be reading too much between the lines, but I wonder if you wish to
first sum up all the EmployeeVacation hours for each [VacationDate] (this
would work best with a Totals query), then compare the BudgetHours for that
date with the total?

 

[Guest]

Hi Jeff,

Thank you for your quick response. The goal is to see how many hours are
available on a given date before a vacation request is approved. So if we
have 50 hours on the budget for 1/1/2005 and an employee requests 8 hours
vacation, I would like to see available for 1/1/2005 42 hours before the next
request is approved for another employee.

I have 1 table that stores all budgeted hours by day for 2005 and 1 table
that is populated by a form that holds the employee's request by day along
with the hours requested.

The mismatch error is the following "Wizard is unable to open query in
Datasheet view, possible because another user has a source table open in
exclusive mode."

Thanks.

Michael

A couple things to consider...

First, if your columns are actually named "Date", both Access and you will
be confused. This is a reserved word in Access. Try modifying the field
name, maybe to [BudgetDate] and [VacationDate].

Next, you mention a data type mismatch but you don't describe what data
types your fields are.

Finally, you appear to be trying to connect the two tables together by the
fields holding date information. How do you intend to handle more than one
employee/vacation on any given date that might show up in the BudgetedHours
table?

This may be reading too much between the lines, but I wonder if you wish to
first sum up all the EmployeeVacation hours for each [VacationDate] (this
would work best with a Totals query), then compare the BudgetHours for that
date with the total?

--
Good luck

Jeff Boyce
<Access MVP>

Hi,

I have a 1 table that named Budgeted Hours, with fields that have a Date and
Budgeted Hours columns. Next I have another table named Vacation Hours that
stores Employee Name, Date & Hours requested. I need to have a query that
grabs the "Hours Requested" and subtract that from the "Budgeted Hours" and
display the hours available in the budget. I have a relationship tied to the
Date column of both tables but I get a mismatch error. Can someone please
help!!

Any help will be greatly appreciated.

 

[Jeff Boyce]

Michael

I mis-interpreted -- when you said you had a mismatch error, I assumed it
was a data type mismatch. I'm still not clear on how an "... exclusive use
...." error is a "mismatch" error.

Your description confirmed my suspicion -- you have a table with requested
vacation hours for specific employees, for specific dates, and you have a
table with budgeted hours for each date. Please re-read my response --
you'll need to create a query that sums all vacation hour requests, by date,
then create another query that connects/joins the budgeted hours table to
the sum-of-vacation, by date. You can use this second query to find the
difference.

The error message you cited implies that someone has exclusive use of the
database. If this isn't true, try checking Google.com, in the MS Access
Group, for keywords like "other user" and "exclusive".

Good luck!

Jeff Boyce
<Access MVP>

Hi Jeff,

Thank you for your quick response. The goal is to see how many hours are
available on a given date before a vacation request is approved. So if we
have 50 hours on the budget for 1/1/2005 and an employee requests 8 hours
vacation, I would like to see available for 1/1/2005 42 hours before the next
request is approved for another employee.

I have 1 table that stores all budgeted hours by day for 2005 and 1 table
that is populated by a form that holds the employee's request by day along
with the hours requested.

The mismatch error is the following "Wizard is unable to open query in
Datasheet view, possible because another user has a source table open in
exclusive mode."

Thanks.

Michael

A couple things to consider...

First, if your columns are actually named "Date", both Access and you will
be confused. This is a reserved word in Access. Try modifying the field
name, maybe to [BudgetDate] and [VacationDate].

Next, you mention a data type mismatch but you don't describe what data
types your fields are.

Finally, you appear to be trying to connect the two tables together by the
fields holding date information. How do you intend to handle more than one
employee/vacation on any given date that might show up in the BudgetedHours
table?

This may be reading too much between the lines, but I wonder if you wish to
first sum up all the EmployeeVacation hours for each [VacationDate] (this
would work best with a Totals query), then compare the BudgetHours for that
date with the total?

--
Good luck

Jeff Boyce
<Access MVP>

Hi,

I have a 1 table that named Budgeted Hours, with fields that have a

Date
and

Budgeted Hours columns. Next I have another table named Vacation

Hours
that

stores Employee Name, Date & Hours requested. I need to have a query that
grabs the "Hours Requested" and subtract that from the "Budgeted

Hours"
and

display the hours available in the budget. I have a relationship tied

to
the

Date column of both tables but I get a mismatch error. Can someone please
help!!

Any help will be greatly appreciated.