Hi,

I'm new to Access and databases in general but am quite Excel literate
- which I'm not sure is a help or hindrance with this. I need to
convert my excel swamp to a tidy access database. I've made some
progress but have hit a wall. I've had quite a poke around over the
last couple of days for online help but maybe missing some terminology
for things tolcik in to place.

In MSAccess I've a table of 'bookings'. The table has columns for
Username, 'Status', Account#, Duration, and Item booked.

I've been able to make a query to give me the Duration booked and I
need the query to look up another table for the "fee per hour" so I
can calculate the 'Total Fee' for the booking event. (I later need to
sum these totals based on Acount# - but that's alter...).

The fee per hour is determined by the group status (internal or
external etc.) and the 'Item' booked.
The Table with the "Fee per hour" has Columns for 'Group Status', and
6 other columns with the name of the item as heading.

So I'd like a query to use the 'Item' and 'Status' values in the of a
'booking' table to return its fee per hour from the fee schedule
table. Is this possible or Excel-thinking?

Any pointers much appreciated.

Thanks,

Tony

rubiocollins (AT) gmail (DOT) com wrote:
You could import the Excel fee rate worksheet into Access. Then open
the Query Designer and add both tables. Link the stateus field (drag
status from one table to the other). Then add that fee to a column.

I'm only guessing, but is your application for something like hiring tools
out by the day.

If so you really need to consder your tables.

I would have 1 table for Items. Key ItemID. Presumably the same item is
going to be used for many bookings.
1 Table for Customers / Users with name and AccountNo
1 Table for Status unless the only possibilities are "Internal" or
"External" in which case a Yes/No field in the Customer table will be OK
1 Table of Prices including ItemID, Date, FeePerHour
Then the table that brings everything together with the Customer, Item, Date
and duration.
The fee would be calculated by looking up the price for that item on the
date booked.

HTH

Phil

"Salad" <oil (AT) vinegar (DOT) com> wrote

Thanks for your help. I think i'm getting there. I've been able to
link the tables but only to a particular fee-status.
As suggested, I've a table of of Prices including ItemID, Date,
FeePerHour_Internal, Fee_Per_hour_External, etc.

I can get the system to return a User fee (row) from any one fee
column that I specify to the final query but not one dependent on
'status'.

Thanks again.
Tony




For example, If I have the Fee table

On Apr 23, 6:19*am, "Phil Stanton" <p... (AT) myfamilyname (DOT) co.uk> wrote:

You hav'nt made it clear whether status is a Yes/No field in your Customer
table, in which case your Fee table should have 2 columns i.e. InternalFee &
ExternalFee or whether you are using a StatusTable, in which case your Fee
Table needs a StatusID related to the Status table

Phil

<rubiocollins (AT) gmail (DOT) com> wrote


Thanks for your help. I think i'm getting there. I've been able to
link the tables but only to a particular fee-status.
As suggested, I've a table of of Prices including ItemID, Date,
FeePerHour_Internal, Fee_Per_hour_External, etc.

I can get the system to return a User fee (row) from any one fee
column that I specify to the final query but not one dependent on
'status'.

Thanks again.
Tony




For example, If I have the Fee table

On Apr 23, 6:19 am, "Phil Stanton" <p... (AT) myfamilyname (DOT) co.uk> wrote:

We have more than two status - internal, external, member, and a
couple of others.

So I have three tables. One called CustomerTab which has an access
assigned ID#, CustomerName and CustomerStatus.
One called FeeTable which has an Access assigned ID#; CustomerStatus,
Item1Fee, Item2Fee, Item3Fee etc.
One called BookingsTab which has an Access assigned ID#; CustomerName;
ItemBooked (which has the values Item1 or Item2 or Item3, etc) and
Duration.

I can make a query that returns CustomerName; Duration and Item1Fee
(for example), but not the fee for the item specified in the
ItemBooked field.

(I apologise if I'm not being clear - it's tough to explain...)

Thanks,

Tony



On Apr 23, 7:47*am, "Phil Stanton" <p... (AT) myfamilyname (DOT) co.uk> wrote:

In that case have a table for Status
StatusID AutoNumber
StatusDesc Text No dupicates
In your CustomerTab you will also have StatusID
In you FeeTable it will look like this
FeeID AutoNumber 1
StatusID Number, Long
FeeDate Date
Fee Currency

so the table might look like this
FeeID ItemID StatusID Feedate Fee
1 1 2 1-Jan-08
£10.00 (StatusID 2 points to External)
2 1 3 1-Jan-08
£10.50 (StatusID 3 points to member)
3 1 2 1-Nov-08
£10.30
4 1 1 1-Nov-08
£ 9.60 (Internal)
5 2 2 1-Jan-08
£28.00 (StatusID 2 points to External)
6 2 3 1-Jan-08
£30.00 (StatusID 3 points to member)
7 2 2 1-Nov-08
£33.00
8 2 1 1-Nov-08
£ 30.00 (Internal)
9 2 2 1-Nov-08
£29.00

Your booking Table should also contain a DateBookedFrom, because that
information will be used to "run down" the FeeDate column in the Fees Table
to look for
the largest date befor the booking date. So if Item 2 is booked on 1-Sep-08
by an External, the fee of £28 will be found, but that will have risen to
£29.00 if the booking was made after November 1st

Phil

<rubiocollins (AT) gmail (DOT) com> wrote

We have more than two status - internal, external, member, and a
couple of others.

So I have three tables. One called CustomerTab which has an access
assigned ID#, CustomerName and CustomerStatus.
One called FeeTable which has an Access assigned ID#; CustomerStatus,
Item1Fee, Item2Fee, Item3Fee etc.
One called BookingsTab which has an Access assigned ID#; CustomerName;
ItemBooked (which has the values Item1 or Item2 or Item3, etc) and
Duration.

I can make a query that returns CustomerName; Duration and Item1Fee
(for example), but not the fee for the item specified in the
ItemBooked field.

(I apologise if I'm not being clear - it's tough to explain...)

Thanks,

Tony



On Apr 23, 7:47 am, "Phil Stanton" <p... (AT) myfamilyname (DOT) co.uk> wrote: