Help with Old Database - new requests

[Linda]

I have an old database which they want to add some complex
relationships to now:

They want to put in: up-to ten Fee Codes (Fee Code Charged-
1 to 10) by Vendor Contract Num by City - then lookup by
Fee Code-X the fee amount (Fee-1 thru Fee-124) then sum
those codes used and print or show in a report the
specific code and $-amount plus total $ amount......

The specific one-relationship is: Vendor Contract Num +
Vendor Name + City; everything else can be multipules:
More than one Vendor Name per City.
More than one City per Vendor Name.
More than one Vendor Contract Num per Vendor Name
More than one Vendor Name per Vendor Contract Num

Vendor Table
Autonumber PK
Vendor Contract Num text
Vendor Name text
Vendor City text
Fee-1 $
Fee-2 $
Fee-3 $ to Fee-124 $

Fee Codes Table (New table I just setup)
Fee Code text PK
Fee Code-1 text
Fee Code-2 text
Fee Code-3 to Fee Code 124

Transaction Table
Autonumber PK
Vendor Contract Num text
Vendor Name text
Vendor City text
Fee Code Charged-1
Fee Code Charged-2
Fee Code Charged-3
Fee Code Charged-4 to Fee Code Charged-10

I may not have enough time to change all the tables to
what they should be setup as - unless it can't be done any
other way.

I am on my second year with MS Access, but just came to
this company

I would be greatful for constructive help (I understand
what this messy looks like!!!!)

Linda

 

[John Vinson]

I have an old database which they want to add some complex
relationships to now:

They want to put in: up-to ten Fee Codes (Fee Code Charged-
1 to 10) by Vendor Contract Num by City - then lookup by
Fee Code-X the fee amount (Fee-1 thru Fee-124) then sum
those codes used and print or show in a report the
specific code and $-amount plus total $ amount......

WHOA!!!! Stop RIGHT there.

"Fields are expensive, records are cheap". If you have up to ten Fee
Codes, someday you will have ELEVEN fee codes, and need to restructure
all your tables, and all your forms, and all your reports, and all
your queries. If you have a one to many relationship, *model it as a
one to many relationship*, not as redundant fields.

The specific one-relationship is: Vendor Contract Num +
Vendor Name + City; everything else can be multipules:
More than one Vendor Name per City.
More than one City per Vendor Name.
More than one Vendor Contract Num per Vendor Name
More than one Vendor Name per Vendor Contract Num

Vendor Table
Autonumber PK
Vendor Contract Num text
Vendor Name text
Vendor City text
Fee-1 $
Fee-2 $
Fee-3 $ to Fee-124 $

That's where you went wrong (one of the places). Instead of ANY of
these Fee fields, have a VendorFee table:

VendorID <Long Integer, link to the Autonumber>
FeeNum <Integer 1 to 124, or maybe to 125 or 131 tomorrow>
Fee <Currency>

VendorID and FeeNum would be a joint two-field Primary Key, and the
table would have up to 124 rows for each vendor.

Fee Codes Table (New table I just setup)
Fee Code text PK
Fee Code-1 text
Fee Code-2 text
Fee Code-3 to Fee Code 124

Same logic. A single table with FeeNum (1 to 124) as its Primary Key,
and a single text field. Join it to the VendorFee table to link the
fee currency value to the fee code text.

Transaction Table
Autonumber PK
Vendor Contract Num text
Vendor Name text
Vendor City text
Fee Code Charged-1
Fee Code Charged-2
Fee Code Charged-3
Fee Code Charged-4 to Fee Code Charged-10

and again - you have a Many to Many link from Vendor Contracts to Fee
Codes.

I may not have enough time to change all the tables to
what they should be setup as - unless it can't be done any
other way.

Well, they should certainly NOT be set up the wide-flat way you are
proposing!!

I am on my second year with MS Access, but just came to
this company

I would be greatful for constructive help (I understand
what this messy looks like!!!!)

Good luck introducing your coworkers to the wonderful world of
normalized data structures!

 

[Linda]

THANK YOU SO MUCH JOHN:
Because of your comments my Boss will allow me the time
to setup all tables correctly !!!!!

I do have a question about how the "City" is handled?

The specific one-relationship is: Vendor Contract Num +
Vendor Name + City; everything else can be multipules:
More than one Vendor Name per City.
More than one City per Vendor Name.
More than one Vendor Contract Num per Vendor Name
More than one Vendor Name per Vendor Contract Num

Also, how will I read the Fee Codes "Charged" to the
Vendor and then find the Fee associated with the Fee Code,
show each Code and Fee and than sum it all up?

Do I need an input form that they put in Fee Codes Charged?

Do I need a report or invoice report ?


THANKS SO MUCH FOR ALL YOUR HELP JOHN !!!!!!!!

 

[Linda]

THANK YOU SO MUCH JOHN:
Because of your comments my Boss will allow me the time
to setup all tables correctly !!!!!

I do have a question about how the "City" is handled?

The specific one-relationship is: Vendor Contract Num +
Vendor Name + City; everything else can be multipules:
More than one Vendor Name per City.
More than one City per Vendor Name.
More than one Vendor Contract Num per Vendor Name
More than one Vendor Name per Vendor Contract Num

Also, how will I read the Fee Codes "Charged" to the
Vendor and then find the Fee associated with the Fee Code,
show each Code and Fee and than sum it all up?

Do I need an input form that they put in Fee Codes Charged?

Do I need a report or invoice report ?


THANKS SO MUCH FOR ALL YOUR HELP JOHN !!!!!!!!

 

[Linda]

THANK YOU SO MUCH JOHN:
Because of your comments my Boss will allow me the time
to setup all tables correctly !!!!!

I do have a question about how the "City" is handled?

The specific one-relationship is: Vendor Contract Num +
Vendor Name + City; everything else can be multipules:
More than one Vendor Name per City.
More than one City per Vendor Name.
More than one Vendor Contract Num per Vendor Name
More than one Vendor Name per Vendor Contract Num

Also, how will I read the Fee Codes "Charged" to the
Vendor and then find the Fee associated with the Fee Code,
show each Code and Fee and than sum it all up?

Do I need an input form that they put in Fee Codes Charged?

Do I need a report or invoice report ?


THANKS SO MUCH FOR ALL YOUR HELP JOHN !!!!!!!!

 

[John Vinson]

On Wed, 26 May 2004 13:10:58 -0700, "Linda"

Answers inline.

THANK YOU SO MUCH JOHN:
Because of your comments my Boss will allow me the time
to setup all tables correctly !!!!!

I do have a question about how the "City" is handled?

The specific one-relationship is: Vendor Contract Num +
Vendor Name + City; everything else can be multipules:
More than one Vendor Name per City.
More than one City per Vendor Name.
More than one Vendor Contract Num per Vendor Name
More than one Vendor Name per Vendor Contract Num

Every time you have a many to many relationship you need THREE tables:
the first "one" side; the other "one" side; and a third table to model
the relationship itself. If you need to deal independently with
VendorC in Saskatoon, vs. with VendorC in Puerto Vallarta, you would
need three tables:

Cities
CityID
CityName <there are LOTS of cities named, say, Springfield>
State
Country

Vendors
VendorID
VendorName
<other info about the vendor>

VendorCities
VendorID <link to Vendors>
CityID <link to Cities>

Also, how will I read the Fee Codes "Charged" to the
Vendor and then find the Fee associated with the Fee Code,
show each Code and Fee and than sum it all up?

With a Totals query joining all of the relevant tables together.

Do I need an input form that they put in Fee Codes Charged?

You need an input form - or a subform, probably in this case using a
Form based on the Vendors table with a Subform based on FeesCharged -
for pretty much *any* input that you want to enter.

Do I need a report or invoice report ?

You'll need a Report for any combination of information that you want
printed to paper. If you need an printed invoice, you need a Report
(you can call it Invoice Report or rptInvoice or whatever you like,
Access won't treat it any differently than any other report); you'll
typically have several different reports, each based on a Query
joining information from multiple tables.