Database design Reccomendation

[Billiam]

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

 

[Duane Hookom]

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

 

[Billiam]

Sorry, I should have been more specific about the relationships and setup.
Yes I have three tables like yours, except I did not know what to do about
the date.
BTW, I have made the necessary many to many relationship table utilizing the
PK's of the Group and Product tables.

So, Duane, from a form point of view for data entry, 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?

Also, if I follow what you have prposed here for date, how would I set my
form up? Would I use dependent comboboxes for group and product and shipped
date?

Thanks again for your input and help.

Bill

 

[Billiam]

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

 

[Steve]

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 (e-mail address removed) if you want help.

Steve

 

[Duane Hookom]

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.

 

[John... Visio MVP]

Hi Bill,

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 (e-mail address removed) if you
want help.

Steve

These newsgroups are provided by Microsoft for FREE peer to peer support.
Stevie is a known troll who likes to harrass posters for work.

If you WANT frustration and waste time, stevie will gladly set you up for
disappointment.

John... Visio MVP

 

[John W. Vinson]

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.

 

[Steve]

I don't know if you are one of them but more than a dozen "regulars" mostly
MVPs are privately emailing posters in the newsgroups offering to help them
with their problem for a fee. They have found a way to somehow get the
posters' email address when the poster posts his question in the newsgroup.
I don't do that! I only selectively offer to help a poster who seems to
really need help if he contacts me. Apparently these "regulars" (MVPs) don't
want any competition.

Steve

 

[Gina Whipp]

Steve,

I am not an MVP BUT I know a out and out lie when I see it! Why would you
even say such a thing? Do you realize that you can now be sued? That's
putting aside WHO in their right mind would believe you as YOU are
constantly offering PAID services in a FREE newsgroup, whether the poster
asks for services or not!

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

 

[John... Visio MVP]

I don't know if you are one of them but more than a dozen "regulars" mostly
MVPs are privately emailing posters in the newsgroups offering to help them
with their problem for a fee. They have found a way to somehow get the
posters' email address when the poster posts his question in the newsgroup.
I don't do that! I only selectively offer to help a poster who seems to
really need help if he contacts me. Apparently these "regulars" (MVPs)
don't want any competition.

Steve

Where are you getting this crap from?

None of the regular MVPs who help in these newsgroup would stoop to such an
underhanded thing. They also have other sources of income so they do not
have to grovel for work like you do.

If you have any proof, give it and name names.

or is this just another one of your ploys to justify your existence by
posting false innuendos about the people who really help users in these
newsgroups (and they are not all MVPs).

Steve, just grow up and get a life.

John... MVP

 

[Steve]

Are you one of them?

Steve,

I am not an MVP BUT I know a out and out lie when I see it! Why would you
even say such a thing? Do you realize that you can now be sued? That's
putting aside WHO in their right mind would believe you as YOU are
constantly offering PAID services in a FREE newsgroup, whether the poster
asks for services or not!

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

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

 

[Steve]

Are you one of them?

Where are you getting this crap from?

None of the regular MVPs who help in these newsgroup would stoop to such
an underhanded thing. They also have other sources of income so they do
not have to grovel for work like you do.

If you have any proof, give it and name names.

or is this just another one of your ploys to justify your existence by
posting false innuendos about the people who really help users in these
newsgroups (and they are not all MVPs).

Steve, just grow up and get a life.

John... MVP

 

[John... Visio MVP]

So you admit to making up the story?

I help out in these newsgroups because I want to help out, not as a source
of income (something I do not need). I would be spending more time answering
Access questions if I did not have to waste time pointing out your flagarant
disregard for the purpose of these newsgroups. Of course, preventing posters
from wasting time and money on you IS helping, so far now I will have to be
content with that as my contribution.

Of course, if you are curious, I do have a couple of web-bots that let me
know when you post one of your snake oil posts.

John... Visio MVP

 

[Gina Whipp]

You know, I have THREE lawyers on retainer. Let's not see how far I will
go...

Anyone who WAS thinking of using you would see your FALSE accusations and
wonder what kind of person you are. I have NEVER solicitated work! When
would I? What would I do with my current Clients? Repeat business... but
you wouldn't know about that, would you?

 

[Duane Hookom]

I would love to compare a list of people and organizations I have helped at
no fee with any list you could compile of any MS Access MVP who solicits from
postings in these news groups.

Maybe you should contact the Eau Claire, WI Special Olympics, United
Cerebral Palsy, Senior Center, Boy Scouts of America, and several other
organizations that have received checks from people I have helped. I haven't
asked any of them for money and when they ask who they can send a check to, I
suggest a charity of my or their choice.

Please back your blantant accusations with examples.

 

[Tony Toews [MVP]]

I don't know if you are one of them but more than a dozen "regulars" mostly
MVPs are privately emailing posters in the newsgroups offering to help them
with their problem for a fee.

I'm certainly unaware of this. And it's very difficult to do as most
folks don't have email addresses viewable .

They have found a way to somehow get the
posters' email address when the poster posts his question in the newsgroup.

This is absolutely wrong. We MVPs have no more access to the
internals of Microsoft newsgroups servers than anyone else does.

I don't do that! I only selectively offer to help a poster who seems to
really need help if he contacts me. Apparently these "regulars" (MVPs) don't
want any competition.

No. We don't want sleazy people soliciting work in the newsgroups.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[strive4peace]

Hi Steve,

"I only selectively offer to help a poster who seems to really need help
if he contacts me."

that contradicts what you said in your previous message -- asking the
poster to contact you and pay you do his work.

The whole point of these newsgroups, Steve, is to freely exchange
information. Every poster needs help or they would not be asking
questions. Sometimes people want private help -- why not give a website
in your siggy so others can contact you if they have learned from you
and want some of your time privately?

~~~

"more than a dozen "regulars" mostly MVPs are privately emailing posters
in the newsgroups offering to help them with their problem for a fee. "

Bull. I simply do not believe this and doubt you can you name even ONE
MVP that does this. I do remote programming and training, but the time I
give here and in other public forums is volunteer time; it one way that
I tithe.

I would not ask a poster to email me for paid help -- or harvest their
email from the newsgroup and send unsolicited information.
Occasionally, if I need to see a database to help with a problem, I will
ask the poster to send their database so I can see it -- but whatever
help I give is provided in public where others can benefit as well.

~~~
Happy New Year, Steve


Warm Regards,
Crystal

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

*
have an awesome day
*

 

[strive4peace]

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
*

 

[Keith Wilby]

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.