relatively new to database

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

re: relatively new to database

"moriman" <moriman@btinternet.com> wrote in
news:dl23bq$p3p$1@nwrdmz01.dmz.ncs.ea.ibs-infra.bt.com:
[color=blue]
> Hi,
>
> I am fairly new to the intricacies of relational databases so please
> be gentle with me ;-)[/color]

You can write well; you can organize well; you can explain clearly; you
don't need gentle so much.
[color=blue]
> 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 :[/color]

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

**********[color=blue]
> 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[/color]

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.
**********

**********[color=blue]
> 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)[/color]

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.
********

**********[color=blue]
> I need to keep the above information for EVERY week.[/color]
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.

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

*********[color=blue]
> I also need a way to 'carry forward' unpaid bills to the next week.[/color]
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

re: relatively new to database

Hi,

Thanks for your prompt reply ;-)

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

"Lyle Fairfield" <lylefairfield@aim.com> wrote in message
news:Xns970B59342DAE9lylefairfieldaimcom@216.221.8 1.119...[color=blue]
> "moriman" <moriman@btinternet.com> wrote in
> news:dl23bq$p3p$1@nwrdmz01.dmz.ncs.ea.ibs-infra.bt.com:
>[color=green]
> > Hi,
> >
> > I am fairly new to the intricacies of relational databases so please
> > be gentle with me ;-)[/color]
>
> You can write well; you can organize well; you can explain clearly; you
> don't need gentle so much.
>[color=green]
> > 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 :[/color]
>
> It seems to me that keeping track may be more work than delivering?
>
> **********[color=green]
> > 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[/color]
>
> 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.
> **********
>[/color]

This is the same as I was considering and I found no problem implementing
this part :)
[color=blue]
> **********[color=green]
> > 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)[/color]
>
> 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.
> ********[/color]

again, OK so far...
[color=blue]
>
> **********[color=green]
> > I need to keep the above information for EVERY week.[/color]
> This could be done in a Delivery Tables,
> Fields ID (as above), CustomerID, DateID.
>[/color]


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.
[color=blue]
> 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
>[/color]


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
[color=blue]
> 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.
>
> ************
>
> *********[color=green]
> > I also need a way to 'carry forward' unpaid bills to the next week.[/color]
> 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[/color]

re: relatively new to database

moriman wrote:[color=blue]
> Hi,
>
> Thanks for your prompt reply ;-)
>
> I have added comments\queries within the post below....
>
> "Lyle Fairfield" <lylefairfield@aim.com> wrote in message
> news:Xns970B59342DAE9lylefairfieldaimcom@216.221.8 1.119...[color=green]
> > "moriman" <moriman@btinternet.com> wrote in
> > news:dl23bq$p3p$1@nwrdmz01.dmz.ncs.ea.ibs-infra.bt.com:
> >[color=darkred]
> > > Hi,
> > >
> > > I am fairly new to the intricacies of relational databases so please
> > > be gentle with me ;-)[/color]
> >
> > You can write well; you can organize well; you can explain clearly; you
> > don't need gentle so much.
> >[color=darkred]
> > > 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 :[/color]
> >
> > It seems to me that keeping track may be more work than delivering?
> >
> > **********[color=darkred]
> > > 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[/color]
> >
> > 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.
> > **********
> >[/color]
>
> This is the same as I was considering and I found no problem implementing
> this part :)
>[color=green]
> > **********[color=darkred]
> > > 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)[/color]
> >
> > 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.
> > ********[/color]
>
> again, OK so far...
>[color=green]
> >
> > **********[color=darkred]
> > > I need to keep the above information for EVERY week.[/color]
> > This could be done in a Delivery Tables,
> > Fields ID (as above), CustomerID, DateID.[/color]
>
> 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?[/color]

I would think:

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

As stated in the OP, it is the relational part that confuses me.[color=blue]
>[color=green]
> > 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[/color][/color]
[color=blue]
> If my above interpretation is correct, what and where would I be entering
> delivery information?[/color]

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.
[color=blue]
> I won't continue any further until I am sure have got this part correct.
>
> again, many thanks for your help ;-)
>
> mori
>[color=green]
> > 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.
> >
> > ************
> >
> > *********[color=darkred]
> > > I also need a way to 'carry forward' unpaid bills to the next week.[/color]
> > 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[/color][/color]

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.