relationship problems

[moriman]

Hi,

I currently have a spreadsheet with customer\delive ry\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]

Re: relationship problems

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
JoinCustomerPro duct -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" <moriman@btinte rnet.com> wrote in message
news:dlf6pd$seh $1@nwrdmz03.dmz .ncs.ea.ibs-infra.bt.com...[color=blue]
> Hi,
>
> I currently have a spreadsheet with customer\delive ry\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
>
>[/color]

[moriman]

Re: relationship problems

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" <philnoxx@xxsta ntonfamily.co.u k> wrote in message
news:437b6b63$0 $82669$ed2619ec @ptn-nntp-reader03.plus.n et...[color=blue]
> 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
> JoinCustomerPro duct -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[/color]
*[color=blue]
> 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[/color]
sheet,[color=blue]
> 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" <moriman@btinte rnet.com> wrote in message
> news:dlf6pd$seh $1@nwrdmz03.dmz .ncs.ea.ibs-infra.bt.com...[color=green]
> > Hi,
> >
> > I currently have a spreadsheet with customer\delive ry\cost etc. a[/color][/color]
snapshot[color=blue][color=green]
> > 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[/color][/color]
which[color=blue][color=green]
> > 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[/color][/color]
the[color=blue][color=green]
> > 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
> >
> >[/color]
>
>[/color]

[Phil Stanton]

Re: relationship problems

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" <moriman@btinte rnet.com> wrote in message
news:dlfs6c$lfb $1@nwrdmz03.dmz .ncs.ea.ibs-infra.bt.com...[color=blue]
> 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" <philnoxx@xxsta ntonfamily.co.u k> wrote in message
> news:437b6b63$0 $82669$ed2619ec @ptn-nntp-reader03.plus.n et...[color=green]
>> 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
>> JoinCustomerPro duct -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[/color]
> *[color=green]
>> 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[/color]
> sheet,[color=green]
>> 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" <moriman@btinte rnet.com> wrote in message
>> news:dlf6pd$seh $1@nwrdmz03.dmz .ncs.ea.ibs-infra.bt.com...[color=darkred]
>> > Hi,
>> >
>> > I currently have a spreadsheet with customer\delive ry\cost etc. a[/color][/color]
> snapshot[color=green][color=darkred]
>> > 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[/color][/color]
> which[color=green][color=darkred]
>> > 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[/color][/color]
> the[color=green][color=darkred]
>> > 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
>> >
>> >[/color]
>>
>>[/color]
>
>[/color]

[moriman]

Re: relationship problems

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" <philnoxx@xxsta ntonfamily.co.u k> wrote in message
news:437bbcba$0 $63070$ed2e19e4 @ptn-nntp-reader04.plus.n et...[color=blue]
> 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" <moriman@btinte rnet.com> wrote in message
> news:dlfs6c$lfb $1@nwrdmz03.dmz .ncs.ea.ibs-infra.bt.com...[color=green]
> > Thanks for the input Phil ;-)
> >
> > I think that what makes my position different from what you are[/color][/color]
explaining[color=blue][color=green]
> > 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[/color][/color]
and[color=blue][color=green]
> > *no* customer receives more than 1 delivery per day).
> >
> > anyway, thx again,
> >
> > mori
> >
> >
> >
> > "Phil Stanton" <philnoxx@xxsta ntonfamily.co.u k> wrote in message
> > news:437b6b63$0 $82669$ed2619ec @ptn-nntp-reader03.plus.n et...[color=darkred]
> >> The normal way to handle this sort of thing is to have your customer
> >> table(see later), a Product table - ProductID, ProdDesc ProdPrice and[/color][/color][/color]
a[color=blue][color=green][color=darkred]
> >> JoinCustomerPro duct -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[/color]
> > *[color=darkred]
> >> ProdPrice. You then have a full record of what customers have bought[/color][/color][/color]
and[color=blue][color=green][color=darkred]
> >> what products have sold.
> >>
> >> Back to the customer file. If your spreadsheet is a sample of a real[/color]
> > sheet,[color=darkred]
> >> I would consider having a table of street addresses and a table of[/color][/color][/color]
areas.[color=blue][color=green][color=darkred]
> >> 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" <moriman@btinte rnet.com> wrote in message
> >> news:dlf6pd$seh $1@nwrdmz03.dmz .ncs.ea.ibs-infra.bt.com...
> >> > Hi,
> >> >
> >> > I currently have a spreadsheet with customer\delive ry\cost etc. a[/color]
> > snapshot[color=darkred]
> >> > 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[/color][/color][/color]
in[color=blue][color=green][color=darkred]
> >> > the
> >> > spreadsheet) with a complete list of customers and a delivery column[/color]
> > which[color=darkred]
> >> > initially has a null value where I can simply enter '1' if the[/color][/color][/color]
customer[color=blue][color=green][color=darkred]
> >> > recieved a delivery that day. When I get that far I'll take a look at[/color]
> > the[color=darkred]
> >> > 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
> >> >
> >> >
> >>
> >>[/color]
> >
> >[/color]
>
>[/color]

[pietlinden@hotmail.com]

Re: relationship problems

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(Prim aryKey1)
DeliveryDate(PK 2)
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.