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 3 1384
"moriman" <mo*****@btinte rnet.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
Hi,
Thanks for your prompt reply ;-)
I have added comments\querie s within the post below....
"Lyle Fairfield" <ly***********@ aim.com> wrote in message
news:Xn******** *************** **********@216. 221.81.119... "moriman" <mo*****@btinte rnet.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.Custo merID <-> Deliveries.Cust omerID
Dates.DatesID <-> Deliveries.Date sID
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
moriman wrote: Hi,
Thanks for your prompt reply ;-)
I have added comments\querie s within the post below....
"Lyle Fairfield" <ly***********@ aim.com> wrote in message news:Xn******** *************** **********@216. 221.81.119... "moriman" <mo*****@btinte rnet.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.Custo merID <-> Deliveries.Cust omerID Dates.DatesID <-> Deliveries.Date sID
Is this correct?
I would think:
Customers.Custo merID <- Deliveries.Cust omerID
Dates.DatesID <- Deliveries.Date sID
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. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Cherrish Vaidiyan |
last post by:
sir,
The following are the steps that i followed in setting up standby
database on Red hat Linux 9. i am using Oracle 9i.
i have followed the steps in this site :
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96653/create_ps.htm#66206
Still i give the steps i followed.
Preparing the Primary Database for Standby Database Creation
|
by: deko |
last post by:
Is there any way to work around the blank space created by hidden divs? I'm
trying to use a relatively postioned divs with show/hide behaviors to
annotate an image. The divs show/hide onMouseOver/onMouseOut but leave a
blank space on the page when hidden (the space where the divs would normally
be). Is it possible to use z-indexing to put regular content where the
hidden divs would be? When I try to do this, the layers don't unhide as...
|
by: Claude Schneegans |
last post by:
Hi,
The W3C says:
"The 'z-index' property is used to specify the stacking order of
positionable elements <#Positionable_Elements>."
and it defines positionable elements as "Elements whose 'position'
property is set to either 'absolute' or 'relative'."
<#Positionable_Elements>
Then the z-index style should work for a relative positioned SPAN for
instance, right?
|
by: TokiDoki |
last post by:
Hello there,
I have been programming python for a little while, now. But as I am
beginning to do more complex stuff, I am running into small organization
problems.
It is possible that what I want to obtain is not possible, but I would
like the advice of more experienced python programmers.
I am writing a relatively complex program in python that has now around
40 files.
|
by: Marvin Libson |
last post by:
Hi All:
I am running DB2 UDB V7.2 with FP11. Platform is Windows 2000. I
have created a java UDF and trigger. When I update my database I get
the following error:
SQL1224N A database agent could not be started to service a request,
or was
terminated as a result of a database system shutdown or a force
command.
| |
by: Kamlesh |
last post by:
Hi,
How do I know the physical database path of a database. When I goto
the DB2INSTANCE users's directory (/home/db2inst1), I see following
folders:
/db2inst1/NODE0000/SQL00001
/db2inst1/NODE0000/SQL00002
/db2inst1/NODE0000/SQL00003
/db2inst1/NODE0000/SQL00004
|
by: pintur |
last post by:
The message is:
SQL1036C Errore di I/O durante l' accesso al
database. SQLSTATE=58030
what is the proble?
what for restore tables?
thanks
|
by: Annie |
last post by:
hi guys,
I have a path of eg: http://localhost/MobileShop/MobileImages but it
doesn't work if i access it removely of course.
I can do something like http://192.166.1.1/MobileShop/MobileImages ... but
it is not a better choice.
How can I reference it relatively based on the path of the application maybe
something equal to App.Path of Visual Basic.
|
by: Tom Cole |
last post by:
While I've done javascript work for as long as I can remember (since
Netscape first released it), I've only ever used it for trivial
things, change a color here, validate a text element there, blah blah
blah. With Ajax (actually, the uncovering of the XmlHTTPRequest
object) I absolutely see the benefit of moving more of the UI work to
the client, rather than doing page refreshes.
I know there are a bunch of libraries out there...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |