relationship problems

[moriman]

Hi,

I currently have a spreadsheet with customer\delivery\cost etc. a snapshot
of which can be viewed at
http://members.lycos.co.uk/moriman/dda-web.jpg

I am trying to construct a database to store this information.

I have so far made a customer table with the following fields...

Customers -> CustomerID (autonumber (primary key)), Name, House No.,
Address, Area

What I want to be able to do is bring up a form (similar to one week in the
spreadsheet) with a complete list of customers and a delivery column which
initially has a null value where I can simply enter '1' if the customer
recieved a delivery that day. When I get that far I'll take a look at the
rest of the information I need to store.

I would be extremely grateful for any help anyone can give on what my
delivery table should contain and the relationships I need to make to be
able create the form I am looking for.

Many thanks

mori

[Phil Stanton]

The normal way to handle this sort of thing is to have your customer
table(see later), a Product table - ProductID, ProdDesc ProdPrice and a
JoinCustomerProduct -CustomerID, ProductID, Date, Quantity, Price, Paid

Normally you select the customer, select the product using a combo box and
enter the quantity. The date default is today Date(), the Price = Quantity *
ProdPrice. You then have a full record of what customers have bought and
what products have sold.

Back to the customer file. If your spreadsheet is a sample of a real sheet,
I would consider having a table of street addresses and a table of areas.
The street address would have something like
StreetID StreetName AreaID
2 Boulevard Charone 1

and the AreaTable
AreaID Area
1 St Julian's

Setting up the customer the consists of entering a name, house no and
picking the street from a combo box.

HTH

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

Hi,

I currently have a spreadsheet with customer\delivery\cost etc. a snapshot
of which can be viewed at
http://members.lycos.co.uk/moriman/dda-web.jpg

I am trying to construct a database to store this information.

I have so far made a customer table with the following fields...

Customers -> CustomerID (autonumber (primary key)), Name, House No.,
Address, Area

What I want to be able to do is bring up a form (similar to one week in
the
spreadsheet) with a complete list of customers and a delivery column which
initially has a null value where I can simply enter '1' if the customer
recieved a delivery that day. When I get that far I'll take a look at the
rest of the information I need to store.

I would be extremely grateful for any help anyone can give on what my
delivery table should contain and the relationships I need to make to be
able create the form I am looking for.

Many thanks

mori

[moriman]

Thanks for the input Phil ;-)

I think that what makes my position different from what you are explaining
is that there is only one product and customers receive 0 or 1 delivery per
day at a set price (i.e. all customers pay exactly the same for 1 unit and
*no* customer receives more than 1 delivery per day).

anyway, thx again,

mori

"Phil Stanton" <ph******@xxstantonfamily.co.uk> wrote in message
news:43***********************@ptn-nntp-reader03.plus.net...

The normal way to handle this sort of thing is to have your customer
table(see later), a Product table - ProductID, ProdDesc ProdPrice and a
JoinCustomerProduct -CustomerID, ProductID, Date, Quantity, Price, Paid

Normally you select the customer, select the product using a combo box and
enter the quantity. The date default is today Date(), the Price = Quantity * ProdPrice. You then have a full record of what customers have bought and
what products have sold.

Back to the customer file. If your spreadsheet is a sample of a real sheet, I would consider having a table of street addresses and a table of areas.
The street address would have something like
StreetID StreetName AreaID
2 Boulevard Charone 1

and the AreaTable
AreaID Area
1 St Julian's

Setting up the customer the consists of entering a name, house no and
picking the street from a combo box.

HTH

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

Hi,

I currently have a spreadsheet with customer\delivery\cost etc. a snapshot of which can be viewed at
http://members.lycos.co.uk/moriman/dda-web.jpg

I am trying to construct a database to store this information.

I have so far made a customer table with the following fields...

Customers -> CustomerID (autonumber (primary key)), Name, House No.,
Address, Area

What I want to be able to do is bring up a form (similar to one week in
the
spreadsheet) with a complete list of customers and a delivery column which initially has a null value where I can simply enter '1' if the customer
recieved a delivery that day. When I get that far I'll take a look at the rest of the information I need to store.

I would be extremely grateful for any help anyone can give on what my
delivery table should contain and the relationships I need to make to be
able create the form I am looking for.

Many thanks

mori

[Phil Stanton]

Just intrigued that there appear to be an assortment of prices - £3.90,
£2.60, £3.25 an 1.95 and in your daily deliveries, apart from the 1 and
zero, there are "X" and "A"

Anyway, what I suggested is still valid, and will give you the opportunity
to have additional products, and also makes it simple to get totals

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

Thanks for the input Phil ;-)

I think that what makes my position different from what you are explaining
is that there is only one product and customers receive 0 or 1 delivery
per
day at a set price (i.e. all customers pay exactly the same for 1 unit and
*no* customer receives more than 1 delivery per day).

anyway, thx again,

mori

"Phil Stanton" <ph******@xxstantonfamily.co.uk> wrote in message
news:43***********************@ptn-nntp-reader03.plus.net...

The normal way to handle this sort of thing is to have your customer
table(see later), a Product table - ProductID, ProdDesc ProdPrice and a
JoinCustomerProduct -CustomerID, ProductID, Date, Quantity, Price, Paid

Normally you select the customer, select the product using a combo box
and
enter the quantity. The date default is today Date(), the Price =
Quantity

*

ProdPrice. You then have a full record of what customers have bought and
what products have sold.

Back to the customer file. If your spreadsheet is a sample of a real

sheet,

I would consider having a table of street addresses and a table of areas.
The street address would have something like
StreetID StreetName AreaID
2 Boulevard Charone 1

and the AreaTable
AreaID Area
1 St Julian's

Setting up the customer the consists of entering a name, house no and
picking the street from a combo box.

HTH

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

> Hi,
>
> I currently have a spreadsheet with customer\delivery\cost etc. a snapshot > of which can be viewed at
> http://members.lycos.co.uk/moriman/dda-web.jpg
>
> I am trying to construct a database to store this information.
>
> I have so far made a customer table with the following fields...
>
> Customers -> CustomerID (autonumber (primary key)), Name, House No.,
> Address, Area
>
> What I want to be able to do is bring up a form (similar to one week in
> the
> spreadsheet) with a complete list of customers and a delivery column which > initially has a null value where I can simply enter '1' if the customer
> recieved a delivery that day. When I get that far I'll take a look at the > rest of the information I need to store.
>
> I would be extremely grateful for any help anyone can give on what my
> delivery table should contain and the relationships I need to make to
> be
> able create the form I am looking for.
>
> Many thanks
>
> mori
>
>

[moriman]

Hi again Phil ;-)

The figures you quote (£2.60, £3.25, £1.95 etc) are, in fact, *weekly*
delivery charges at a rate of £0.65 per day.

Say, for example, that customer No. 7 gets a delivery on a Mon, Tue, Thurs,
Fri & Sat, then his row appears as
Due
1 1 0 1 1 1 £3.25

that is, 5 * £0.65

The X's and A's in the spreadsheet represent customers who *never* receive a
delivery on a particular day (X) and those who do not want a delivery
because they are (A)way for a few days. These X's and A's are not important
for me at the moment in the construction of the database.

thanks again

mori
"Phil Stanton" <ph******@xxstantonfamily.co.uk> wrote in message
news:43***********************@ptn-nntp-reader04.plus.net...

Just intrigued that there appear to be an assortment of prices - £3.90,
£2.60, £3.25 an 1.95 and in your daily deliveries, apart from the 1 and
zero, there are "X" and "A"

Anyway, what I suggested is still valid, and will give you the opportunity
to have additional products, and also makes it simple to get totals

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

Thanks for the input Phil ;-)

I think that what makes my position different from what you are explaining is that there is only one product and customers receive 0 or 1 delivery
per
day at a set price (i.e. all customers pay exactly the same for 1 unit and *no* customer receives more than 1 delivery per day).

anyway, thx again,

mori

"Phil Stanton" <ph******@xxstantonfamily.co.uk> wrote in message
news:43***********************@ptn-nntp-reader03.plus.net...

The normal way to handle this sort of thing is to have your customer
table(see later), a Product table - ProductID, ProdDesc ProdPrice and a JoinCustomerProduct -CustomerID, ProductID, Date, Quantity, Price, Paid

Normally you select the customer, select the product using a combo box
and
enter the quantity. The date default is today Date(), the Price =
Quantity

*

ProdPrice. You then have a full record of what customers have bought and what products have sold.

Back to the customer file. If your spreadsheet is a sample of a real

sheet,

I would consider having a table of street addresses and a table of areas. The street address would have something like
StreetID StreetName AreaID
2 Boulevard Charone 1

and the AreaTable
AreaID Area
1 St Julian's

Setting up the customer the consists of entering a name, house no and
picking the street from a combo box.

HTH

Phil
"moriman" <mo*****@btinternet.com> wrote in message
news:dl**********@nwrdmz03.dmz.ncs.ea.ibs-infra.bt.com...
> Hi,
>
> I currently have a spreadsheet with customer\delivery\cost etc. a

snapshot

> of which can be viewed at
> http://members.lycos.co.uk/moriman/dda-web.jpg
>
> I am trying to construct a database to store this information.
>
> I have so far made a customer table with the following fields...
>
> Customers -> CustomerID (autonumber (primary key)), Name, House No.,
> Address, Area
>
> What I want to be able to do is bring up a form (similar to one week in > the
> spreadsheet) with a complete list of customers and a delivery column

which

> initially has a null value where I can simply enter '1' if the customer > recieved a delivery that day. When I get that far I'll take a look at

the

> rest of the information I need to store.
>
> I would be extremely grateful for any help anyone can give on what my
> delivery table should contain and the relationships I need to make to
> be
> able create the form I am looking for.
>
> Many thanks
>
> mori
>
>

[pietlinden]

Repeating groups of values... put all that in a child table. Link to
AccountNumber or whatever.

So you'd have something like
tblCustomers
=========
CustomerID
ACNo
Address
Area

tblDeliveries (or whatever this represents)
=========
DeliveryID (Autonumber)
CustomerID(PrimaryKey1)
DeliveryDate(PK2)
Delivered(Y/N)
BF
Due
CF

if you have CustomerID and DeliveryDate as the primary key of
tblDeliveries, it means that each customer can have only one delivery
per day. Then you just have a main form based on Customer and a
subform based on delivery.