Design of a Sales database

[Peter Clancy]

I am designing basically a sales database, the difference between this and a
'normal' sales database is that this is recording the sale of information,
rather than things. Most of it is still straightforward, for example
providing address details is a simple quantity item (one) that will cost a
set amount (say £25), but where I am having difficulty envisaging a solution
is charging elements where it involves a time range, for example, billing
history between two dates, where the charge is either per week or part
thereof, or per month or part thereof. It is the design of the table that I
am grappling with, should I have both the ranged items and the single items
in one table or have seperate tables for the ranged and single items?
Any thoughts anyone?
Thanks

 

[Craig Hornish]

Hi,
Here are my first thoughts with the information you gave. I am assuming
a "Products", and Order Details tables.

With that said - yes keep 1 table for both ranged and single items. You
have a field added to Product that is called UM - Unit of measure.
You keep the Quantity in OD (may need to change to decimal) wich will be use
for your Ea - Each, Hr - Hours, Days etc.
Alway good to show an example so:

So tblProduct
PI Name UM Price
1 Bricks Ea $1
2 Brick Laying Hr $45

Now on the data entry form you would enter the "product" and it would show
you the price, UM, and you would enter the quantity -
Order Detail
ProductID Price Qty
1 $1 400
2 $40 6.5 - They got a discount in price (Just posted a
responce about history prices

So in reports you have the link to the product and Order details and print
out an invoice with something like

Items/Service Price Quantity/Time Extended cost
Bricks $1 400 $400
Brick Laying $40 /Hr 6.5 Hrs $260 (I
would surpress the Ea)


Now you also imply that you may be keeping data about the actual work being
done and putting in times. This would require other tables and code to
accomplish it. I will not go into complete detail unless you confirm this
but some thoughts that you will need to convey. Do you want to have the
detail that the work was done durring specific times? ie 7/6/06 8:00-10:00
(2hrs), 7/7/06 8:00-12:30 (4.5) for the bricklaying Do you need to
calculate the time difference or just have the hours entered and then
totaled for the Invoice?

Hope this is a good start

Craig Hornish
Visit weekly Access conferences
Tuesday, 11:00am-12:30pm (Eastern US)
Thursday, 3:30pm- 5:00pm (Eastern US)

http://community.compuserve.com/n/pfx/forum.aspx?webtag=ws-msdevapps

 

[Peter Clancy]

Craig,
Thanks for that, its helped me to clarify what I want to do. They are
expecting to be store the dates required for the enquiry e.g we want the
billing records from the 1st of May to the 30th of June, so the analyst will
have an entry for billing records of 01/05/2006 to 30/06/2006 and they want
the program to calculate the total cost. The report print out is then an
itemised statement which would look something like:

£
Billing records 01/05/2006 - 30/06/2006 50.00
Subscriber Details 30.00

So my Products table will have the fields
ID Description UM Price
Where UM will be selected from the list of
Ea - Each, We - Week, Mo - Month

But if I want to store the date criteria for the search would I not be
better with an order detail table that has the fields
ID Qty DateFrom DateTo Price

Where for 'Ea' items the Quantity will default to 1 and the Price will be
copied from the Products table and DateFrom and DateTo will be null. For 'We'
and 'Mo' items the Quantity can be calculated from the dates entered in the
DateFrom and DateTo fields and again the Price is duplicated from the
Products table.

So there will be a form where they enter request details, like who made the
request, with maybe a subform that displays a datasheet view of the orders
for that request. Then maybe I will have a button on this form to add
'Information Requested' details, which will display a list the available
'Products'. If the user selects an 'Ea' item this will be a straight addition
to the 'Orders' table, if they select a 'We' or 'Mo' item this will pop up a
new window to capture the dates requested, before populating the 'Orders'
table with the entered values for the dates and the calculated value for the
Quantity.
How does that sound?

Pete

 

[Craig Hornish]

Hi,
Now that you are clarified on what you want to do please clarify what
you do

I don't understand exactly what you mean by Billing records and your stament
about totaling them is a little confusing to me.

To me it could be
One item - like a cable subscription for a month - but your example
shows 2 so is that 50 for each month or 50 total.
but with your statement about toaling it then it could be Multiple items
during that period that get put under the bigger Billing Records heading
- it could be doing multiple things during that time period.

The best way to do this is to expain how you do things now.
This does mean step by step.
Make sure to define everything out - what exacltly is Billing records (to
generic) and in this situation the time period.
A real world example can be the best - if you don't want to post in the
newsgroup then you can email me or use another
similar example.

One of the things I am still trying to get a grasp on is how the services
are recorded.
A cable company that bills you per month will still give you a time period
1/5/2006 -30/5/2006 for that service and will prorate it (possibly) if you
don't use a full month. But it is really still one entry for the service.

But in the bricklaying example I had multiple days working but they could be
stated as
Bricklaying ("Billing Records"???) 1/7/2006 - 30/7/2006 $260

Well that is one question there will be more

--
Craig Hornish
chornish at cap-associates.com

Visit weekly Access conferences
Tuesday, 11:00am-12:30pm (Eastern US)
Thursday, 3:30pm- 5:00pm (Eastern US)

http://community.compuserve.com/n/pfx/forum.aspx?webtag=ws-msdevapps

"Think outside the box, because anything is possible."
"How long it will take or whether it requires divine intervention is another
issue"