Database design Reccomendation

"Steve" wrote in message
m...
Are you one of them?



Why are you asking when you allegedly already know? Oh, hang on, I know the
answer to this one ... as do we all.

"Steve" wrote in message
m...
Are you one of them?



We all know what you are one of.

"Gina Whipp" schreef in bericht
...
Steve,

I thought I have seen it all but you have just shown me how low you can
go!!!


You DID check out http://home.tiscali.nl/arracom/whoissteve.html
??
I guess you did sometime ago...

Steve is only repeating his old infamous tricks and lies...

Regards,
Arno R

Hi Steve,

Thanks very much for your help. I think Crystal's response is the most
constructive, Steve, and I hope you will consider it...

Best regards,

Billiam

"Steve" wrote:

Hi Bill,

Start with these tables:

TblGroup
GroupID
GroupName (GroupA, GroupB)

TblProduct
ProductID
ProductName (ProductA, ProductB, Other Product)

TblProductShipment
ProductShipmentID
GroupID
ProductID
ShipmentDate
Quantity

Total shipped on any date = Sum(Quantity on the ShipmentDate)

Percentage for a product on any date = Total / Quantity for the product

Total shipped on any date by a Group = Sum(Quantity on the ShipmentDate for
the group)

Percentage for a group on any date = Total shipped by group / Total shipped
on any date

Bill, if you want to save yourself frustration and save time, I can set this
up for you for a modest fee. Contact me at if you want help.

Steve


"Billiam" wrote in message
news
Also, just to be clear on the transactions, I need to detail which group
sent
which product(s) on which date and what percentage of the total shipments
(ie
there are shipments which are neither ProductA or ProductB on any given
day.
In other words, I need to know the percentage of productA to the total
shipments for the day. Should i set up a ProductOther group...really it is
the leftover amount of the whole, and the data input perosn would have to
compute that which I do not want.

Hope that clarifies things a little.

Bill

"Duane Hookom" wrote:

I don't understand what your "transactions" will look like. I can only
assume
you need a table of Groups with fields like:
tblGroups (initially only 2 records)
===========
GroupID PK Autonumber
GroupName
You would have a similar table for Products
tblProducts (initially only 2 records)
===========
ProductID PK Autonumber
ProductName
The final table would be the transaction
tblShipments
============
ShipID PK Autonumber
ProductID
GroupID
ShipDate
PiecesShipped


--
Duane Hookom
Microsoft Access MVP


"Billiam" wrote:

I need to track pieces (2 types) shipped on a daily/monthly/yearly
comparable
basis for two groups groupA & GroupB. More specifically, I will be
comparing
both groups individually on a historical basis, in addition to each
groups
daily/monthly/yearly relationship to the number of pieces shipped with
respect to ProductA & productB Also, the data needs to be input
manually, as
the system that ships these pieces is a seperate outside company ie
Purolator/Fedex, so I cannot "datamine' the info.

There are only two "products" which need to be tracked.

We need to be able to produce reports on an individual group basis, and
combined Group basis:

Also,

Daily shipping compared over the last "x" years with percentage
increase/decrease
Monthly "" ""
""
Yearly "" ""
""

I am thinking the following for Table layouts:

tblGroup
GroupID (autonumber)
GroupA
GroupB

tblProduct
ProductID (autonumber)
product1
product2

I am not sure what to do with the date fields? Should I have a Day
table, a
month table, and a year table? I am thinking I would use a dependent
combobox
date form to select the date (The reason I am thinking of doing this
this way
is The year, for example, would determine whether or not Feb has 29
days, in
addition to, if I choose December from the first combobox, the second
combo
box would show 31 days)

With respect to the above, I would like the year to default to the
current
year (we are still updating previous years data) but am unsure how to
do this.

Also, I would like a warning if there is already data stored for a
specific
day to make the data input person aware they may be entering informaton
in on
the wrong date.

Any help would be sincerely appreciated!

Thanks,

Bill

Thank you, John. I did not know that.

Best regards,

Billiam


"John W. Vinson" wrote:

On Wed, 7 Jan 2009 07:49:01 -0800, Billiam
wrote:

I wanted to avoid data
entry errors by limiting the date field to established dates i.e February
only has 28 days except in a Leap year', and i thought I would have to use
dependent comboboxes to do this. Am I overcomplicating this?

Yes. Access parses dates very cleverly. If you type 2/30/09 into a date field
you'll get a beep and an error message; the error is trappable if you want to
give the user an even friendlier (or more informative) message.
--

John W. Vinson [MVP]

Hi Crystal,

Thank you for your very thoughtful reponse! Afraid I have been struggling
with a health issue, and so just trying to get my head back into Access...

Indeed I would like to see what you have for dates, and will e-mail you, if
you do not mind. Thank you for being so considerate and thoughtful...it
really is sincerley appreciated...

Please accept my very best wishes to you for a Happy, Healthy, Prospeous &
Peaceful New Year!

Best Regards,

Billiam



"strive4peace" wrote:

Hi Billiam,

as John said, if date/time is defined as the data type, Access will only
allow valid dates (and times) to be entered

do this:

use a textbox with no InputMask* to collect a date. I use the
double-click event to pop up a calendar -- Duane gave you a link to a
calendar by Lebans and there are many others out there if you do a
search. My popup calendar uses the ActiveX control but it also has
things to allow you to quickly set a date. I also have a popup calendar
for picking a date range and you can do things like click 'Q1' and have
it automatically fill Jan 1 - Mar 31. If you want these forms, email me
and I will send them.

I find that using an InputMask interferes with a popup calendar and it
precludes you from being able to enter, ie, '8p' and have Access expand
it to 8:00 PM

If you have only certain dates that you want to be chosen, you can use a
combo instead of a textbox -- and still popup a calendar on the
double-click event

~~~

nice to 'see' you again, Bill/William! Happy New Year


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
(: have an awesome day
*




John W. Vinson wrote:
On Wed, 7 Jan 2009 07:49:01 -0800, Billiam
wrote:

I wanted to avoid data
entry errors by limiting the date field to established dates i.e February
only has 28 days except in a Leap year', and i thought I would have to use
dependent comboboxes to do this. Am I overcomplicating this?

Yes. Access parses dates very cleverly. If you type 2/30/09 into a date field
you'll get a beep and an error message; the error is trappable if you want to
give the user an even friendlier (or more informative) message.

Well, in these posts, Steve has transitioned from forum abuse to patently
offensive and abusive behavior.

Well, in these posts, Steve has transitioned from forum abuse to patently
offensive and abusive behavior.

It was some time ago but after his recent accusation I went and checked it
out again.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II
"StopThisAdvertising" StopThisAdvertising@Data**** wrote in message
...

"Gina Whipp" schreef in bericht
...
Steve,

I thought I have seen it all but you have just shown me how low you can
go!!!


You DID check out http://home.tiscali.nl/arracom/whoissteve.html ??
I guess you did sometime ago...

Steve is only repeating his old infamous tricks and lies...

Regards,
Arno R

Hi Duane,

Thank you very much for the help. I appreciate it very much!

Best regards,

Bill

"Duane Hookom" wrote:

Steve,
Good reply until your advertising. I don't understand why you can't behave
like nearly every other participant in these news groups. I don't recall
seeing anyone else try to blantantly sell their services like you do.

Billiam,
If you want to ensure data entry integrity, use combo boxes for groups and
products. I recommend using a pop-up calendar form for dates. If you search
the web for "Access calendar form", you should find lots of good ones. I
think the one from Stephen Lebans at www.lebans.com is highly recommended.
--
Duane Hookom
Microsoft Access MVP


"Steve" wrote:

Hi Bill,

Start with these tables:

TblGroup
GroupID
GroupName (GroupA, GroupB)

TblProduct
ProductID
ProductName (ProductA, ProductB, Other Product)

TblProductShipment
ProductShipmentID
GroupID
ProductID
ShipmentDate
Quantity

Total shipped on any date = Sum(Quantity on the ShipmentDate)

Percentage for a product on any date = Total / Quantity for the product

Total shipped on any date by a Group = Sum(Quantity on the ShipmentDate for
the group)

Percentage for a group on any date = Total shipped by group / Total shipped
on any date

Bill, if you want to save yourself frustration and save time, I can set this
up for you for a modest fee. Contact me at if you want help.

Steve


"Billiam" wrote in message
news
Also, just to be clear on the transactions, I need to detail which group
sent
which product(s) on which date and what percentage of the total shipments
(ie
there are shipments which are neither ProductA or ProductB on any given
day.
In other words, I need to know the percentage of productA to the total
shipments for the day. Should i set up a ProductOther group...really it is
the leftover amount of the whole, and the data input perosn would have to
compute that which I do not want.

Hope that clarifies things a little.

Bill

"Duane Hookom" wrote:

I don't understand what your "transactions" will look like. I can only
assume
you need a table of Groups with fields like:
tblGroups (initially only 2 records)
===========
GroupID PK Autonumber
GroupName
You would have a similar table for Products
tblProducts (initially only 2 records)
===========
ProductID PK Autonumber
ProductName
The final table would be the transaction
tblShipments
============
ShipID PK Autonumber
ProductID
GroupID
ShipDate
PiecesShipped


--
Duane Hookom
Microsoft Access MVP


"Billiam" wrote:

I need to track pieces (2 types) shipped on a daily/monthly/yearly
comparable
basis for two groups groupA & GroupB. More specifically, I will be
comparing
both groups individually on a historical basis, in addition to each
groups
daily/monthly/yearly relationship to the number of pieces shipped with
respect to ProductA & productB Also, the data needs to be input
manually, as
the system that ships these pieces is a seperate outside company ie
Purolator/Fedex, so I cannot "datamine' the info.

There are only two "products" which need to be tracked.

We need to be able to produce reports on an individual group basis, and
combined Group basis:

Also,

Daily shipping compared over the last "x" years with percentage
increase/decrease
Monthly "" ""
""
Yearly "" ""
""

I am thinking the following for Table layouts:

tblGroup
GroupID (autonumber)
GroupA
GroupB

tblProduct
ProductID (autonumber)
product1
product2

I am not sure what to do with the date fields? Should I have a Day
table, a
month table, and a year table? I am thinking I would use a dependent
combobox
date form to select the date (The reason I am thinking of doing this
this way
is The year, for example, would determine whether or not Feb has 29
days, in
addition to, if I choose December from the first combobox, the second
combo
box would show 31 days)

With respect to the above, I would like the year to default to the
current
year (we are still updating previous years data) but am unsure how to
do this.

Also, I would like a warning if there is already data stored for a
specific
day to make the data input person aware they may be entering informaton
in on
the wrong date.

Any help would be sincerely appreciated!

Thanks,

Bill