Database design Reccomendation

  • Thread starter Thread starter Billiam
  • Start date Start date
B

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
 
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
 
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
 
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,
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.
 
Steve said:
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
Click to expand...

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
 
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?
Click to expand...

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 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!!!
 
Steve said:
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
Click to expand...

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
 
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
 
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?
 
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.
 
Steve said:
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.
Click to expand...

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.
Click to expand...

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.
Click to expand...

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/
 
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 :)
*
 
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 :)
*
 
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
 
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
 
Back
Top