access thread: Please evaluate if my table is normalize
Message #1 by "enzo zaragoza" <enzaux@y...> on Wed, 20 Mar 2002 02:30:51
|
|
Please see if my table is normalize or If I did it correctly.
I have two kinds of Transactions
1. Remittance
2. Cargo
Remittance transaction uses these fields:
TranNo, SenderName, BeneName, DeliveryStat, StatMsg, DateDel, DateEnc,
DelBy, Receivedby, DELIVERYTYPE, AREA, AMTINDOLLl, RATE, AMTINPHP
Cargo transaction uses these fields:
TranNo, SenderName, BeneName, DeliveryStat, StatMsg, DateDel, DateEnc,
DelBy, Receivedby, REGION, SERVICE, SIZE, MODE, QUANTITY
As you can see the first 9 fields are the same and they differ only on the
next fields, so what I did was I created a main transaction table
(tblTranmain) which holds those first 9 fields and for the remaining
fields i put them on two different tables tblTranRemit (Remittances) and
tblTranCargo(Cargo):
MAIN TRANSACTION TABLE:
TranNo, SenderName, BeneName, DeliveryStat, StatMsg, DateDel, DateEnc,
DelBy, Receivedby
REMITTANCE TABLE:
TranNo, DELIVERYTYPE, AREA, AMTINDOLLl, RATE, AMTINPHP
CARGO TABLE:
TranNo, REGION, SERVICE, SIZE, MODE, QUANTITY
Do you think what I did was right? If I did it wrong, what is the best
way to do this? Thanks!!!
Thanks/Regards,
Enzo :)
Message #2 by Omar Chaudry <OChaudry@b...> on Wed, 20 Mar 2002 09:24:21 -0000
|
|
This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.
------_=_NextPart_001_01C1CFF1.04635050
Content-Type: text/plain
Enzo,
I have a similar'ish scenario and what I have is one main transaction table
with an additional field 'TranType' which contains a flag to indicate
whether it is in your case R for Remittance or C for Cargo. I know this is a
little inefficient as five fields would contain nothing for each transaction
but it does give me an easy path for searching e.g. all remittance
transactions.
By the way how do you envisage doing that in your structure?
HTH
Omar
-----Original Message-----
From: enzo zaragoza [mailto:enzaux@y...]
Sent: 20 March 2002 02:31
To: Access
Subject: [access] Please evaluate if my table is normalize
Please see if my table is normalize or If I did it correctly.
I have two kinds of Transactions
1. Remittance
2. Cargo
Remittance transaction uses these fields:
TranNo, SenderName, BeneName, DeliveryStat, StatMsg, DateDel, DateEnc,
DelBy, Receivedby, DELIVERYTYPE, AREA, AMTINDOLLl, RATE, AMTINPHP
Cargo transaction uses these fields:
TranNo, SenderName, BeneName, DeliveryStat, StatMsg, DateDel, DateEnc,
DelBy, Receivedby, REGION, SERVICE, SIZE, MODE, QUANTITY
As you can see the first 9 fields are the same and they differ only on the
next fields, so what I did was I created a main transaction table
(tblTranmain) which holds those first 9 fields and for the remaining
fields i put them on two different tables tblTranRemit (Remittances) and
tblTranCargo(Cargo):
MAIN TRANSACTION TABLE:
TranNo, SenderName, BeneName, DeliveryStat, StatMsg, DateDel, DateEnc,
DelBy, Receivedby
REMITTANCE TABLE:
TranNo, DELIVERYTYPE, AREA, AMTINDOLLl, RATE, AMTINPHP
CARGO TABLE:
TranNo, REGION, SERVICE, SIZE, MODE, QUANTITY
Do you think what I did was right? If I did it wrong, what is the best
way to do this? Thanks!!!
Thanks/Regards,
Enzo :)
DISCLAIMER: The information in this message is confidential and may be
legally privileged. It is intended solely for the addressee. Access to this
message by anyone else is unauthorised. If you are not the intended
recipient, any disclosure, copying, or distribution of the message, or any
action or omission taken by you in reliance on it, is prohibited and may be
unlawful. Please immediately contact the sender if you have received this
message in error. Thank you.
Message #3 by brian.skelton@b... on Wed, 20 Mar 2002 10:13:32
|
|
UNION queries!
As long as you can construct your search queries in such a way that both
transaction types return the same number of fields, then just use the
UNION operator to return them as one recordset.
SELECT tblTranmain.* , DELIVERYTYPE, AREA, AMTINDOLLl, RATE,
AMTINPHP,'','','','','' FROM tblTranmain INNER JOIN tblTranRemit ON
tblTranmain.TranNo = tblTranRemit.TranNo
UNION
SELECT tblTranmain.* , '','','','','',REGION, SERVICE, SIZE, MODE,
QUANTITY FROM tblTranmain INNER JOIN tblTranCargo ON tblTranmain.TranNo =
tblTranCargo.TranNo
Brian
I have a similar'ish scenario and what I have is one main transaction table
with an additional field 'TranType' which contains a flag to indicate
whether it is in your case R for Remittance or C for Cargo. I know this is
a
little inefficient as five fields would contain nothing for each
transaction
but it does give me an easy path for searching e.g. all remittance
transactions.
By the way how do you envisage doing that in your structure?
HTH
Omar
Message #4 by "e n z o" <enzaux@g...> on Thu, 21 Mar 2002 08:53:27 +0800
|
|
This is a multi-part message in MIME format.
------=_NextPart_000_00EF_01C1D0B5.DE458300
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
RE: [access] Please evaluate if my table is normalizeOmar,
You were saying we have a similar scenario?...well definitely we
have :) because I have also a trantype field (i forgot to include in my
sample) :) and this field is my basis on which table will my tblTranMain
be JOINed with.
What do you mean by your last question? --> "By the way how do you
envisage doing that in your structure? "
Thanks,
Enzo :)
----- Original Message -----
From: Omar Chaudry
To: Access
Sent: Wednesday, March 20, 2002 5:24 PM
Subject: [access] RE: Please evaluate if my table is normalize
Enzo,
I have a similar'ish scenario and what I have is one main transaction
table with an additional field 'TranType' which contains a flag to
indicate whether it is in your case R for Remittance or C for Cargo. I
know this is a little inefficient as five fields would contain nothing
for each transaction but it does give me an easy path for searching e.g.
all remittance transactions.
By the way how do you envisage doing that in your structure?
HTH
Omar
-----Original Message-----
From: enzo zaragoza [mailto:enzaux@y...]
Sent: 20 March 2002 02:31
To: Access
Subject: [access] Please evaluate if my table is normalize
Please see if my table is normalize or If I did it correctly.
I have two kinds of Transactions
1. Remittance
2. Cargo
Remittance transaction uses these fields:
TranNo, SenderName, BeneName, DeliveryStat, StatMsg, DateDel,
DateEnc,
DelBy, Receivedby, DELIVERYTYPE, AREA, AMTINDOLLl, RATE, AMTINPHP
Cargo transaction uses these fields:
TranNo, SenderName, BeneName, DeliveryStat, StatMsg, DateDel,
DateEnc,
DelBy, Receivedby, REGION, SERVICE, SIZE, MODE, QUANTITY
As you can see the first 9 fields are the same and they differ only on
the
next fields, so what I did was I created a main transaction table
(tblTranmain) which holds those first 9 fields and for the remaining
fields i put them on two different tables tblTranRemit (Remittances)
and
tblTranCargo(Cargo):
MAIN TRANSACTION TABLE:
TranNo, SenderName, BeneName, DeliveryStat, StatMsg, DateDel,
DateEnc,
DelBy, Receivedby
REMITTANCE TABLE:
TranNo, DELIVERYTYPE, AREA, AMTINDOLLl, RATE, AMTINPHP
CARGO TABLE:
TranNo, REGION, SERVICE, SIZE, MODE, QUANTITY
Do you think what I did was right? If I did it wrong, what is the
best
way to do this? Thanks!!!
Thanks/Regards,
Enzo :)
DISCLAIMER: The information in this message is confidential and may be
legally privileged. It is intended solely for the addressee. Access to
this message by anyone else is unauthorised. If you are not the
intended recipient, any disclosure, copying, or distribution of the
message, or any action or omission taken by you in reliance on it, is
prohibited and may be unlawful. Please immediately contact the sender
if you have received this message in error. Thank you.
Message #5 by Omar Chaudry <OChaudry@b...> on Thu, 21 Mar 2002 08:43:39 -0000
|
|
This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.
------_=_NextPart_001_01C1D0B4.7F94E680
Content-Type: text/plain
Hi Enzo,
That last question related to the search by transaction type and as you say
you have a flag set for it, this answers my question.
Cheers
Omar
-----Original Message-----
From: e n z o [mailto:enzaux@g...]
Sent: 21 March 2002 00:53
To: Access
Subject: [access] RE: Please evaluate if my table is normalize
Omar,
You were saying we have a similar scenario?...well definitely we have :)
because I have also a trantype field (i forgot to include in my sample) :)
and this field is my basis on which table will my tblTranMain be JOINed
with.
What do you mean by your last question? --> "By the way how do you
envisage doing that in your structure? "
Thanks,
Enzo :)
----- Original Message -----
From: Omar Chaudry <mailto:OChaudry@b...>
To: Access <mailto:access@p...>
Sent: Wednesday, March 20, 2002 5:24 PM
Subject: [access] RE: Please evaluate if my table is normalize
Enzo,
I have a similar'ish scenario and what I have is one main transaction table
with an additional field 'TranType' which contains a flag to indicate
whether it is in your case R for Remittance or C for Cargo. I know this is a
little inefficient as five fields would contain nothing for each transaction
but it does give me an easy path for searching e.g. all remittance
transactions.
By the way how do you envisage doing that in your structure?
HTH
Omar
-----Original Message-----
From: enzo zaragoza [mailto:enzaux@y... <mailto:enzaux@y...> ]
Sent: 20 March 2002 02:31
To: Access
Subject: [access] Please evaluate if my table is normalize
Please see if my table is normalize or If I did it correctly.
I have two kinds of Transactions
1. Remittance
2. Cargo
Remittance transaction uses these fields:
TranNo, SenderName, BeneName, DeliveryStat, StatMsg, DateDel, DateEnc,
DelBy, Receivedby, DELIVERYTYPE, AREA, AMTINDOLLl, RATE, AMTINPHP
Cargo transaction uses these fields:
TranNo, SenderName, BeneName, DeliveryStat, StatMsg, DateDel, DateEnc,
DelBy, Receivedby, REGION, SERVICE, SIZE, MODE, QUANTITY
As you can see the first 9 fields are the same and they differ only on the
next fields, so what I did was I created a main transaction table
(tblTranmain) which holds those first 9 fields and for the remaining
fields i put them on two different tables tblTranRemit (Remittances) and
tblTranCargo(Cargo):
MAIN TRANSACTION TABLE:
TranNo, SenderName, BeneName, DeliveryStat, StatMsg, DateDel, DateEnc,
DelBy, Receivedby
REMITTANCE TABLE:
TranNo, DELIVERYTYPE, AREA, AMTINDOLLl, RATE, AMTINPHP
CARGO TABLE:
TranNo, REGION, SERVICE, SIZE, MODE, QUANTITY
Do you think what I did was right? If I did it wrong, what is the best
way to do this? Thanks!!!
Thanks/Regards,
Enzo :)
---
Change your mail options at http://p2p.wrox.com/manager.asp
<http://p2p.wrox.com/manager.asp> or
DISCLAIMER: The information in this message is confidential and may be
legally privileged. It is intended solely for the addressee. Access to this
message by anyone else is unauthorised. If you are not the intended
recipient, any disclosure, copying, or distribution of the message, or any
action or omission taken by you in reliance on it, is prohibited and may be
unlawful. Please immediately contact the sender if you have received this
message in error. Thank you.
---
---
DISCLAIMER: The information in this message is confidential and may be
legally privileged. It is intended solely for the addressee. Access to this
message by anyone else is unauthorised. If you are not the intended
recipient, any disclosure, copying, or distribution of the message, or any
action or omission taken by you in reliance on it, is prohibited and may be
unlawful. Please immediately contact the sender if you have received this
message in error. Thank you.
|
View Cart
