relatively new to database

[moriman]

Hi,

I am fairly new to the intricacies of relational databases so please be
gentle with me ;-)

I have just taken on a delivery run. The same (mostly) customers receive
deliveries Monday to Saturday every week. I need to keep these details of
the customers :

Full Name
Address
Area (so that list can be sorted by different areas on the run, which
can't be done simply by address)
Postal Code

For each customer I also need to record the following :

whether delivery received for each day of the week (Mon - Sat)
whether payment was received (due on Saturdays)

I need to keep the above information for EVERY week.
I also need a way to 'carry forward' unpaid bills to the next week.

I originally set up a spreadsheet to keep all this information with the
following columns :
(I have transposed columns to rows below for ease of reading on NG)

Full Name John Smith
Address 12 Main Street, Mansfield
Area Chesterfield Road
31-10-05 0
01-11-05 1
02-11-05 1
03-11-05 1
04-11-05 0
05-11-05 1
Brought Fwd £0.00
Amount Due £12.60 (calculated by £3.15 per
delivery)
Amount Paid £ 8.00
Carry Fwd £ 4.60 (calculated by
spreadsheet formula)
07-11-05 1
08-11-05 1
09-11-05 0
10-11-05 1
11-11-05 1
12-11-05 1
Brought Fwd £4.60 (brought forward from
previous Carry forward)
Amount Due £20.35 (calculated as above +
Bought fwd amount)
Amount Paid £20.00
Carry Fwd £ 0.35

etc. simply adding new columns for each new week.

As you can see, as the weeks progress, this spreadsheet will become rather
unwieldy and increasingly harder to retreive earlier information from.

Any help anyone can give me in creating a more compact database from the
above would be greatly appreciated ;-)

Many thanks in advance

mori

[Lyle Fairfield]

"moriman" <mo*****@btinternet.com> wrote in
news:dl**********@nwrdmz01.dmz.ncs.ea.ibs-infra.bt.com:

Hi,

I am fairly new to the intricacies of relational databases so please
be gentle with me ;-)
You can write well; you can organize well; you can explain clearly; you
don't need gentle so much.
I have just taken on a delivery run. The same (mostly) customers
receive deliveries Monday to Saturday every week. I need to keep these
details of the customers :
It seems to me that keeping track may be more work than delivering?

********** Full Name
Address
Area (so that list can be sorted by different areas on the run,
which can't be done simply by address)
Postal Code
This would be my main table with an autonumber ID field. You could decide
if you need to divide the name and address information into parts like
first name, last name, street etc.
**********

********** For each customer I also need to record the following :

whether delivery received for each day of the week (Mon - Sat)
whether payment was received (due on Saturdays)
I would not record not Delivered, but only Delivered.

To do this I would create a Dates Table for the next year or five or ten
years (your choice; more dates can always be added) . Yes, I know that
most of your responders here will suggest the direct entry of the dates;
I'm not so opposed to that, but why enter when you can choose?

My Dates Table would have fields ID (autonumber), Date, (if you call it
Date then you must remember to preface all your date references with
something which tells Access/VBA what date, such as VBA.Date or
Dates.Date, so you may want to call the Field something else). Perhaps,
if your charge per delivery may change, you will want to add a Cost or
Charge Field in which to record how much a Delivery for that particular
Date cost.
********

********** I need to keep the above information for EVERY week. This could be done in a Delivery Tables,
Fields ID (as above), CustomerID, DateID.

So your first three records might be:
1,1,1 (assuming this is the first record, John Smith is the first
customer, and 2005-11-01 is the first date in the table)
2,1,2
and
3,1,4

On your delivery entry form it will not look like
1,1,1
2,1,2
3,1,4

It will look like
John Smith
2005-11-01
2005-11-02
2005-11-04

(please, note that there is no relationship at all between the ID of the
date and the day of the Date; that is Nov 4 happening to be record 4 is
coincidental, it could be record 456123.

************

********* I also need a way to 'carry forward' unpaid bills to the next week.

A Payments Table would have
Fields ID, CustomerID, DateID, Amount
The first record here might be
1,1,6,8.00
(first record, first customer viz John Smith, 6th date (Nov 6), £ 8.00
**********

And where you might rightfully say is the carried forward, or the record
of delivery? These would appear on reports based on queries which would
do the calculations. These queries might be a bit complicated for a
beginner but (referring to my no need to be gentle statement) if you
asked about them here after you created you tables you would get some
advice and be able to do them your self shortly.

**********

Relationships

Among your tables you would create relationships. For instance the
CUSTOMERID fields of Deliveries and Payments would be mapped to the ID of
Customers, ensuring that no deliveries or payments were recorded for non-
existent customers.
I suppose some might think that I wrote this longish answer because I am
avoiding modifying a particularly complex Stored Procedure. They are
right.

What have I forgotten? Probably lots of things. But don't worry, someone
will fill them in.

--
Lyle Fairfield

[moriman]

Hi,

Thanks for your prompt reply ;-)

I have added comments\queries within the post below....

"Lyle Fairfield" <ly***********@aim.com> wrote in message
news:Xn*********************************@216.221.8 1.119...

"moriman" <mo*****@btinternet.com> wrote in
news:dl**********@nwrdmz01.dmz.ncs.ea.ibs-infra.bt.com:

Hi,

I am fairly new to the intricacies of relational databases so please
be gentle with me ;-)
You can write well; you can organize well; you can explain clearly; you
don't need gentle so much.

I have just taken on a delivery run. The same (mostly) customers
receive deliveries Monday to Saturday every week. I need to keep these
details of the customers :

It seems to me that keeping track may be more work than delivering?

**********

Full Name
Address
Area (so that list can be sorted by different areas on the run,
which can't be done simply by address)
Postal Code

This would be my main table with an autonumber ID field. You could decide
if you need to divide the name and address information into parts like
first name, last name, street etc.
**********

This is the same as I was considering and I found no problem implementing
this part :)
**********

For each customer I also need to record the following :

whether delivery received for each day of the week (Mon - Sat)
whether payment was received (due on Saturdays)
I would not record not Delivered, but only Delivered.

To do this I would create a Dates Table for the next year or five or ten
years (your choice; more dates can always be added) . Yes, I know that
most of your responders here will suggest the direct entry of the dates;
I'm not so opposed to that, but why enter when you can choose?

My Dates Table would have fields ID (autonumber), Date, (if you call it
Date then you must remember to preface all your date references with
something which tells Access/VBA what date, such as VBA.Date or
Dates.Date, so you may want to call the Field something else). Perhaps,
if your charge per delivery may change, you will want to add a Cost or
Charge Field in which to record how much a Delivery for that particular
Date cost.
********

again, OK so far...

**********

I need to keep the above information for EVERY week. This could be done in a Delivery Tables,
Fields ID (as above), CustomerID, DateID.

So far I've created the following tables (fields) :

Customers (CustomerID) (Name) (Address) (Area)
Dates (DatesID) (Delivery Date)
Deliveries (ID) (CustomerID) (DatesID)

hmmm, starting to struggle now :(
btw, I'm using MS Access 2000

under Relationships I have connected :

Customers.CustomerID <-> Deliveries.CustomerID
Dates.DatesID <-> Deliveries.DatesID

Is this correct?
As stated in the OP, it is the relational part that confuses me.
So your first three records might be:
1,1,1 (assuming this is the first record, John Smith is the first
customer, and 2005-11-01 is the first date in the table)
2,1,2
and
3,1,4
If my above interpretation is correct, what and where would I be entering
delivery information?
I won't continue any further until I am sure have got this part correct.

again, many thanks for your help ;-)

mori
On your delivery entry form it will not look like
1,1,1
2,1,2
3,1,4

It will look like
John Smith
2005-11-01
2005-11-02
2005-11-04

(please, note that there is no relationship at all between the ID of the
date and the day of the Date; that is Nov 4 happening to be record 4 is
coincidental, it could be record 456123.

************

*********

I also need a way to 'carry forward' unpaid bills to the next week.

A Payments Table would have
Fields ID, CustomerID, DateID, Amount
The first record here might be
1,1,6,8.00
(first record, first customer viz John Smith, 6th date (Nov 6), £ 8.00
**********

And where you might rightfully say is the carried forward, or the record
of delivery? These would appear on reports based on queries which would
do the calculations. These queries might be a bit complicated for a
beginner but (referring to my no need to be gentle statement) if you
asked about them here after you created you tables you would get some
advice and be able to do them your self shortly.

**********

Relationships

Among your tables you would create relationships. For instance the
CUSTOMERID fields of Deliveries and Payments would be mapped to the ID of
Customers, ensuring that no deliveries or payments were recorded for non-
existent customers.
I suppose some might think that I wrote this longish answer because I am
avoiding modifying a particularly complex Stored Procedure. They are
right.

What have I forgotten? Probably lots of things. But don't worry, someone
will fill them in.

--
Lyle Fairfield

[lylefair]

moriman wrote:

Hi,

Thanks for your prompt reply ;-)

I have added comments\queries within the post below....

"Lyle Fairfield" <ly***********@aim.com> wrote in message
news:Xn*********************************@216.221.8 1.119...

"moriman" <mo*****@btinternet.com> wrote in
news:dl**********@nwrdmz01.dmz.ncs.ea.ibs-infra.bt.com:

Hi,

I am fairly new to the intricacies of relational databases so please
be gentle with me ;-)
You can write well; you can organize well; you can explain clearly; you
don't need gentle so much.

I have just taken on a delivery run. The same (mostly) customers
receive deliveries Monday to Saturday every week. I need to keep these
details of the customers :

It seems to me that keeping track may be more work than delivering?

**********

Full Name
Address
Area (so that list can be sorted by different areas on the run,
which can't be done simply by address)
Postal Code

This would be my main table with an autonumber ID field. You could decide
if you need to divide the name and address information into parts like
first name, last name, street etc.
**********

This is the same as I was considering and I found no problem implementing
this part :)

**********

For each customer I also need to record the following :

whether delivery received for each day of the week (Mon - Sat)
whether payment was received (due on Saturdays)

I would not record not Delivered, but only Delivered.

To do this I would create a Dates Table for the next year or five or ten
years (your choice; more dates can always be added) . Yes, I know that
most of your responders here will suggest the direct entry of the dates;
I'm not so opposed to that, but why enter when you can choose?

My Dates Table would have fields ID (autonumber), Date, (if you call it
Date then you must remember to preface all your date references with
something which tells Access/VBA what date, such as VBA.Date or
Dates.Date, so you may want to call the Field something else). Perhaps,
if your charge per delivery may change, you will want to add a Cost or
Charge Field in which to record how much a Delivery for that particular
Date cost.
********

again, OK so far...

**********

I need to keep the above information for EVERY week.

This could be done in a Delivery Tables,
Fields ID (as above), CustomerID, DateID.

So far I've created the following tables (fields) :

Customers (CustomerID) (Name) (Address) (Area)
Dates (DatesID) (Delivery Date)
Deliveries (ID) (CustomerID) (DatesID)

hmmm, starting to struggle now :(
btw, I'm using MS Access 2000

under Relationships I have connected :

Customers.CustomerID <-> Deliveries.CustomerID
Dates.DatesID <-> Deliveries.DatesID

Is this correct?

I would think:

Customers.CustomerID <- Deliveries.CustomerID
Dates.DatesID <- Deliveries.DatesID

As stated in the OP, it is the relational part that confuses me.

So your first three records might be:
1,1,1 (assuming this is the first record, John Smith is the first
customer, and 2005-11-01 is the first date in the table)
2,1,2
and
3,1,4

If my above interpretation is correct, what and where would I be entering
delivery information?
In a form (probably continuous) bound to the Delivery Table (via some
SQL or Query); probably with two combo boxes, one listing all the
customers (Showing the Customer Name and hiding the CustomerID which
would be bound to the CustomerID field, and ditto for Dates.
Of course, if there are too many customers or dates one would have to
filter these.
I won't continue any further until I am sure have got this part correct.

again, many thanks for your help ;-)

mori

On your delivery entry form it will not look like
1,1,1
2,1,2
3,1,4

It will look like
John Smith
2005-11-01
2005-11-02
2005-11-04

(please, note that there is no relationship at all between the ID of the
date and the day of the Date; that is Nov 4 happening to be record 4 is
coincidental, it could be record 456123.

************

*********

I also need a way to 'carry forward' unpaid bills to the next week.

A Payments Table would have
Fields ID, CustomerID, DateID, Amount
The first record here might be
1,1,6,8.00
(first record, first customer viz John Smith, 6th date (Nov 6), £ 8.00
**********

And where you might rightfully say is the carried forward, or the record
of delivery? These would appear on reports based on queries which would
do the calculations. These queries might be a bit complicated for a
beginner but (referring to my no need to be gentle statement) if you
asked about them here after you created you tables you would get some
advice and be able to do them your self shortly.

**********

Relationships

Among your tables you would create relationships. For instance the
CUSTOMERID fields of Deliveries and Payments would be mapped to the ID of
Customers, ensuring that no deliveries or payments were recorded for non-
existent customers.
I suppose some might think that I wrote this longish answer because I am
avoiding modifying a particularly complex Stored Procedure. They are
right.

What have I forgotten? Probably lots of things. But don't worry, someone
will fill them in.

--
Lyle Fairfield

Well, I thought I read but can't find now a phrase that indicated your
concern about entry by WEEK. Maybe I imagined it. Regardless the WEEK
is irrelevant to data entry. It's probably relevant to reporting, and
that's where we can group by week, customer, date, area, whatever.