[mso] Re: ACCESS - Update Records
- From: "Dian Chapman" <dian@xxxxxxxxxxxxx>
- To: <mso@xxxxxxxxxxxxx>
- Date: Wed, 22 May 2002 09:47:22 -0500
That's cool. I just, personally, prefer to try to stick to the highest
levels of Normalization I'm capable of doing in my DBs, so I don't shoot
myself in the foot down the road.<shrug>
Dian Chapman
Technical Consultant,
Microsoft MVP & TechTrax Editor
Tutorial web site: http://www.mousetrax.com
TechTrax Ezine: http://www.mousetrax.com/techtrax/
Word MVP support site: http://www.mvps.org/word
Microsoft support groups: http://support.microsoft.com
-----Original Message-----
From: mso-bounce@xxxxxxxxxxxxx [mailto:mso-bounce@xxxxxxxxxxxxx] On
Behalf Of Peter Chiavaro
Sent: Wednesday, May 22, 2002 9:07 AM
To: mso@xxxxxxxxxxxxx
Subject: [mso] Re: ACCESS - Update Records
Dian, Cousin Charlie,
Not to confuse the issue even more, but maybe for next time we can
consider another approach. In the programs I write for my horse clients
I am more or less FORCED to do this, but what I do is create a table
called =93Billing=94. In this table I keep all the pertinent information =
to
create an invoice or =93bill=94. CustomerID, OrderDate, ShipToName,
ShipToAddress, ShipToCity, ShipToZip, ItemID, QTY, ItemName, ItemDesc,
TotalPrice, OrderEntryDate, Etc.
OK,,, at first this does NOT sound correct because you have this SAME
information in other tables and all you=92re doing is keeping redundant
information=85BUT=85 At the EXACT TIME of the order, and as soon as the
operator clicks the =93OK Button=94 when placing the order, I run an =
Append
Query that gathers the info needed for the above table and appends it to
the billing table. What this does is FREEZES the order in time. It takes
an EXACT PICTURE of the order and stores it. NOW you can see exactly
WHOM it went to, and HOW MUCH the price was AT THAT EXACT TIME. You can
customize this table to keep any data you want to freeze.
The only thing you have to do after that is create a little maintenance
form that asks the user =93Delete Orders Older Than=94 xx/xx/xx. This =
way
they can clean up the billing table when needed. If you don=92t do this,
you=92ll have a table that just keeps getting bigger and bigger.
Like I said, I am FORCED to use this method because with horses, =93most
times=94 each horse has several owners that must be billed for the
percentage of the horse that they own. If there are 4 owners (of the
same horse), and we each own 25% of the horse, then we each get 25% of
charges billed to our account at the EXACT TIME the order was placed.
(In my case =93order=94 would be when the Veterinarian did something to =
a
horse). These horse owner people are always buying in an out of horses
or changing their percentages. You could see how I would be sunk if I
didn=92t freeze the order details. Using this method, the billing will
correctly reflect the owners=92 EXACT percentage at the EXACT TIME the
order was placed. (Who cares what percentage they had yesterday or what
percentage the will have tomorrow)
The only thing you have to guard against is =93The Dumb Blonde =
Factor=94,
the user putting in today=92s date when asked =93Delete Orders Older =
Than=94
xx/xx_/xx. POOF=85 they=92re out of business. So what I do is MOVE =
(append
then delete) the records into a table called Old Billing. You can then
KEY off the users input date to maintain the =93Old Billing=94 table. =
You
Just delete the records in the =93Old Billing=94 table using the date =
they
enter MINUS ONE YEAR. This way they can NEVER loose old billing data for
at least one year.
I think in ordinary cases, you wouldn=92t have to go through all of =
this,
but it sounds like cousin Charlie is looking to freeze the order
details, regardless of price changes made to his Items table. Unless he
is building a formal =93invoicing program=94 that needs =93Invoice =
Numbers=94 my
method should work just fine for him.
Peter C.
Stuart, Florida
* Kp@xxxxxxxx
-_-_-_-_-_-_-_-_-
ICQ =3D 412633
-----Original Message-----
Create another table with just the prices and link the relationship to
the field with prices. Then you can change the prices, but the one set
for any order will remain unchanged. Make it a combo box so you can
click a drop down to easily find the current price, if there aren't too
many or start typing and the current price will usually show up fairly
quickly. But it'll also give you the advantage of being able to enter a
new price if the current one isn't already in the table.
Dian Chapman
*************************************************************
PLEASE READ!!!!
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx
or MicrosoftOffice@xxxxxxxxxxxxxxxx
To Unsubscribe from this group, send an email to
mso-request@xxxxxxxxxxxxx with a subject line that says "unsubscribe"
(without the quotes). Do not put unsubscribe IN CAPS. Screaming
doesn't get you out any faster and the caps prevent the function from
working.
To change your email settings to digest or vacation (no mail), visit the
group's homepage for full instructions.
//www.freelists.org/webpage/mso
To be able to use the files section for sharing files with the group,
send a request to mso-moderators@xxxxxxxxxxxxx and you will be sent an
invitation with instructions.
*************************************************************
*************************************************************
PLEASE READ!!!!
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx or
MicrosoftOffice@xxxxxxxxxxxxxxxx
To Unsubscribe from this group, send an email to mso-request@xxxxxxxxxxxxx with
a subject line that says "unsubscribe" (without the quotes). Do not put
unsubscribe IN CAPS. Screaming doesn't get you out any faster and the caps
prevent the function from working.
To change your email settings to digest or vacation (no mail), visit the
group's homepage for full instructions.
//www.freelists.org/webpage/mso
To be able to use the files section for sharing files with the group, send a
request to mso-moderators@xxxxxxxxxxxxx and you will be sent an invitation with
instructions.
*************************************************************
Other related posts:
