yes/no

[Guest]

Hi -> signature says it all...
I know a little about access programming (which may be dangerous) LOL
I am creating a little DB for me to keep track of overtime, travel Claims,
etc.
We have a set rate for travel, meals (breakfast, Lunch, Dinner)... I am hung
up there.

Not sure what is the best way to handle this.
This is what I've done:
tables - Rates, Travel, OT, Repairs, etc
Our Rates go up every now and then so I figured they should have their own
table.
then (will use travel as an example) I travel 100 kms one day and get a meal
allowance for lunch. When I designed it I thought it would look best with
Checkboxes Yes/No for each meal. With a meal total field to calculate meals
based off of check boxes.

Does this make sense or should I have made a each meal a field and enter the
amount in there? The checkboxes are tricky. I was thinking of
if checkbox is true then it would lookup rate in rates table and add the
amount to the total meal claim field.

Any other ideas?

PLEASE cuz

 

[Guest]

For one thing you will probably want a Meals table. Perhaps your database
structure will include an employee table, a trips table, a meals table, miles
table, etc. I'm not sure how you need to track this information, but if it
is to be by employee you would make EmployeeID the primary key (PK) of your
Employees table (tblEmployee), and the foreign key (FK) of your trips table
(tblTrips). You assign a PK in table design view, but you establish a FK in
the Relationships window by adding tblEmployee and tblTrips to the
relationship window and dragging EmployeeID from one table to the other.
Click "Enforce referential integrity". I think Help is clear on this; post
again if you have questions.
Now create a form (frmEmployee) based on tblEmployee, and another
(fsubTrips) based on tblTrips. With tblEmployee open in design view, drag
the icon for fsubTrips onto it. This establishes a form/subform to
correspond to the table relationship. Any data entered in the subform will
be stored in tblTrips and linked to tblEmployee through the EmployeeID field.
If EmployeeID in tblEmployee is 999, every record in tblTrips with
EmployeeID 999 will be associated with that employee.
Similarly, tblTrips would have a PK (TripID), which would be the FK in
tblMeals (and also in tblCarExpenses, etc.). Set up relationships in the
same way as before between the PK and FK fields, and create subforms within
fsubTrips for meals, travel expenses, etc.
This is one possible general approach. There are others, depending on your
exact needs. I have used a naming convention for tables, forms, etc., but
you can call them what you wish. I think you will find it helps to keep
names as short and descriptive as is possible.

 

[Guest]

Ok...
I have done that basically... The only difference is, is that I tried to
get fancy to add the Check box to the form to try to calculate the meal total
and it's not in a table on it's own. I was trying to get the Sub form to go
through and couldn't figure it out.
THANKS....

You got it exactly right. When I select an Employee name I want the option
(subforms) to put in a travel claim, Overtime form, Leave application and
repair log. The travel claims include meal fields like Name, travelDate,
KMS, Account, Purpose, MealTotal, TravelTotal and ClaimTotal with those damn
check boxes.

You think I should still create a tblMeal or change the checkbox fields to
Number fields?

If option 2 - how do i pull the calculation from the tblRates to calculate
the MealTotal on the ClaimForm?

 

[Guest]

Let me suggest a table structure something like the following:

tblEmployee
EmployeeID (PK)
LastName
FirstName
etc.

tblTrip
TripID (PK)
EmployeeID (FK)
Destination
StartDate
FinishDate
etc.

tblMeals
MealID (PK)
TripID (FK)
Breakfasts
Lunches
Dinners

tblTransportation
TransID (PK)
TripID (FK)
Conveyance (car, train, etc.)
Miles
etc.


Build a form (frmEmployee) based on tblEmployee, another (fsubTrip) based on
tblTrip; similarly, make forms fsubMeals and fsubTrans. I like to name forms
intended as subforms with the fsub prefix, but that is not required. Make
fsubTrip a subform of frmEmployee, and fsubMeals and fsubTrans subforms of
fsubTrip.
I think this will work for what you need, but I need to bail on one of the
issues, namely how to add the reimbursement rate. It should be simple
enough, but for whatever reason I am not getting it to work and I don't know
why. Remember that the reimbursement rate will change over time, so you will
need to store the actual value.

 

[Guest]

I take back something I said about database structure. Your top form will be
the trip form, with the EmployeeID field to be linked to an EmployeeID from
tblEmployee. tblEmployeeID will be relatively static, I expect, with changes
only when there is a personnel change, address change for an employeee, etc.
Perhaps you would use a combo box on frmTrips to select an employee from
tblEmployee. The combo box wizard can help with that.
tblMeals could contain a calculated text box in which the number of
breakfasts is multiplied by the current rate for breakfast. The part I can't
get for some reason is how to add the current rate to a particular meals
record.
Your project is fairly complex in some ways. For instance, do two employees
ever go on the same trip? That would change some things. I would encourage
you to post a new question after you have worked out a few basic things with
your structure.

 

[Guest]

LOL... I was thinking too that it is quite complicated. There could be 2
employees going on one trip, but one would have travel claim. If there were
incidentals for meals they would both be reimbursed but they would have to
make the claim separtely. I am trying to shy away from this being to
complicated due to that it's only for me. I was thinking of building a huge
database for everyone to put their claims into, but it would be well beyond
me. This would be my second database completed. One issue that I found with
your solution is that (in my mind) the transportation, meal and trip should
be synchronized. But when I enter in data all the items listed are at
different numbers. For example: One trip with a meal claim the meal claim
ID and trip number ID should be identical, but their not. I think I have to
put more thought into this.

 

[Guest]

Sorry one more real stupid thing, but it is so annoying
Do you know what format to pick a number so that it doesn't round up all the
time.
I have tried so many different ways and it always, always, always rounds up.
lol