Table relationships between 42 tables

[Reality]

I cannot seem to find the correct link relationship combination that
will allow me to connect 42 Excel worksheets to 26 to 77 Word
Documents.

Example:
In my Product/Inventory Table, I have the name Ferric Ammonium Oxalate
In my Excel Work Sheet, I have 1 to 5 Word Document titles that may
apply to this one product.

My question is how to resolve garbage in and garbage out results with
the following:

Product/Inventory Table
PK=Ferric Ammonium Oxalate
Once the 42 Excel worksheet are transferred over to Access 2010 into
Tables
FK= Each of the five documents, such as;
WordChemicaTitrationProcedureListingFormatDocument

Thank you for sharing both your professional time and your
professional experiences with me in this personal matter

…
Everything below here is just background information for my question
to the Group.
…
The elderly owner of this very small office/warehouse sells and buys
chemicals. There are 26 basic chemicals*. Out of these 26 basic
chemicals there are 77 products produced and sold.

In the owner’s Windows XP C Drive folder named Chemical, the owner is
using Windows Office 2007, are these Excel Labels and Word Documents.

The basic process:
An order is received.
The order is prepared, that is the powder or liquid chemical(s) are
selected
Depending on the quantity a pale or drum is assigned
The order is named based on one of the 77 products titles
An Excel label is pulled from the Owner’s drive and printed.
Than the accompany document(s) are pulled from this same directory and
printed.
The shipping company is called and an arrangement for pickup and
delivery are made.
The deliver is picked up with the Excel label attached to the
container and the documents along with the container are shipped.

Over looking for now the tracking of buyers, sellers, quantities on
hand, last price, shippers, invoices, employees, payroll, tax rate and
tax locations and a host of other factors; an Excel Label is to be
printed and attached to each product to be shipped out. In addition to
the product and label, there are industry wide informational sheet
that should either be embedded in the label to automatically print or
some way to let the printer of the Excel Label know that other
documents need to be printed before the shipment is labeled and
shipped.

The immediate problem is how to attach the correct Excel Sheet
converted to an Access information Table, which could be up to five
documents, to the correct Excel Label. That is, each of the 26 to 77
products in inventory matched to the appropriate one of many, in some
cases five, Word Documents: calls for a PK on each of Excel Labels
with a FK on each of the 42 pages.

I am using Windows 7, 64-bit, Office 2010 Pro. I copied the titles in
the Owner’s Chemical folder to my drive and folder.

Using the owner’s 2007 Access with CD book, I sat out thinking I was
going toward a solution only to realize that I have created a living
nightmare for myself. I first copied all the examples on the CD to my
computer and then attempted to exchange the information in the
examples with that of the owner. That was my first steps into this
madness of index key issues, data not formatted correctly, you need to
fill out that table before you can enter data into this table, do want
to delete current relationships, and on and on. I finally realize that
I was either going to run an example or attempt to run an example that
was going to permanently turn off my computer.

So as the rule goes, when all else fails read the instruction. I
started with Chapter one and by Chapter 10, yes using shortcut that is
searching for the graphic figures and then reading the accompany text
in the book that applied to the example on the CD, I realized that I
could not apply what I was reading. I would see the relationship in
the text graphic figure, even though it was not my data, I attempted
to drawn the same linked relationship, the one to one, one to many,
left, right or grandparent relationship left me wondering if a pen and
paper were a more efficient solution. I looked at the solution in the
User group, and gasp in wonderment with the word wow, that solution
worked for that person. I then copy the solution word for word and
then failure occurred at the end. So as to not further my nightmare, I
decided I would stay away from using macro and VBA for the time being
until I see some type of progress towards daylight.

So I went into Excel and created 42 pages, I had to do something to
stay sane, and/or at least establish the illusion of achieving some
progress.

An Excel Label will have at least one and maybe all five applicable
Word Documents:
A Word Document that applies to that Excel Label
A Literature that applies to that Excel Label
A MSDS that applies to that Excel Label
A Titration Procedure that applies to that Excel Label
The owner’s word documents that applies to that Excel Label

Example
Presently: Excel Label Name for Ferric Ammonium Oxalate
Presently: An Excel sheet listing each category, such as;
1. WordChemicaTitrationProcedureListingFormatDocument
Ferric Ammonium Oxalate
2. WordTitrationProcedureChemicalDocumentFormatEndingWithALetter
Ferric Ammonium Oxalate CAS
3. WordTitrationProcedureChemicalDocumentFormatEndingWithAMeasurement
4. WordTitrationProcedureChemicalDocumentFormatEndingWithANumber
Ferric Ammonium Oxalate 30
5. WordTitrationProcedureChemicalDocumentFormatEndingWithAPercent
6. WordTitrationProcedureChemicalDocumentFormatEndingWithAPro

Would be associated or linked to this one Excel Label name for Ferric
Ammonium Oxalate.

*Chemistry for me is as Kryptonite is to Superman in the Superman
mythos, an ultimate weakness, so I apology for miss using chemistry in
such way as to state that it is a basic chemical. I am meaning to say
that the term, that is the legitimate Chemical term, has no
significance to me in this application other than its … well a basic
something. I suppose I could have used XYZ.

 

[David-W-Fenton]

m:

I cannot seem to find the correct link relationship combination
that will allow me to connect 42 Excel worksheets to 26 to 77 Word
Documents.

You can't actually enforce RI between Excel workstheets -- all
you're doing is drawing lines that make drag and drop in the query
designer easier.

 

[Reality]

You can't actually enforce RI between Excel workstheets -- all
you're doing is drawing lines that make drag and drop in the query
designer easier.

A thousand thank you Mr. Fenton for your response and thank you for
sharing with me your foresightedness into my problem.

Since posting, overlooking my banging my head against all available
walls, and threatening my computer to come up with an answer or else
face being thrown out the window, and after evaluating my Excel skills
on a scale of 1 to 10, 10 being the highest, my skills sat at an one,
while my Access skills on the same type of scale sat at an minus one:
I have temporarily settled on constricting each of the 42 buyer/
sellers to separate Excel worksheets with just four columns of data:
Name, Date, Item sold or purchased and invoice number.

Admittedly, this defeats the immediate purpose of the Access project
but it sets an immediate objective that being some progress even if it
is only a little progress which is progress being attained through the
combing of buyer/sellers into one format, which is Excel. This data
alone will extend from this year back to January 1, 1997.

The Plan will then become that of transferring each of the buyer/
seller Excel worksheet into Access as a Table, thus the 42 link table
requirement.

Once in the Access project I will add the pertinent information, such
as, address, buyer, seller or both, terms of payment, the name(s) of
the chemicals—72 chemical option-- bought or sold, quantity and
delivery information along with the regulated or non-regulated
information and/or the features that distinguish the two; placards
requirements, driver license of the driver properly State stamped,
route restrictions, etc.,.

Mr. Fenton, I apologize for this unavoidable delayed response, but I
certainly thank you for the light of hope that you have projected onto
this project.
[Monitors please edit as needed]

 

[John W. Vinson]

You can't actually enforce RI between Excel workstheets -- all
you're doing is drawing lines that make drag and drop in the query
designer easier.

A thousand thank you Mr. Fenton for your response and thank you for
sharing with me your foresightedness into my problem.

Since posting, overlooking my banging my head against all available
walls, and threatening my computer to come up with an answer or else
face being thrown out the window, and after evaluating my Excel skills
on a scale of 1 to 10, 10 being the highest, my skills sat at an one,
while my Access skills on the same type of scale sat at an minus one:
I have temporarily settled on constricting each of the 42 buyer/
sellers to separate Excel worksheets with just four columns of data:
Name, Date, Item sold or purchased and invoice number.

Admittedly, this defeats the immediate purpose of the Access project
but it sets an immediate objective that being some progress even if it
is only a little progress which is progress being attained through the
combing of buyer/sellers into one format, which is Excel. This data
alone will extend from this year back to January 1, 1997.

The Plan will then become that of transferring each of the buyer/
seller Excel worksheet into Access as a Table, thus the 42 link table
requirement.

Once in the Access project I will add the pertinent information, such
as, address, buyer, seller or both, terms of payment, the name(s) of
the chemicals—72 chemical option-- bought or sold, quantity and
delivery information along with the regulated or non-regulated
information and/or the features that distinguish the two; placards
requirements, driver license of the driver properly State stamped,
route restrictions, etc.,.

Mr. Fenton, I apologize for this unavoidable delayed response, but I
certainly thank you for the light of hope that you have projected onto
this project.
[Monitors please edit as needed]

You don't need 42 tables. You need 2! Or maybe 3.

In Access, you would have one table for Buyers (with fields shuch as BuyerID -
an autonumber primary key; BuyerName; Address; and other info about the
buyer/seller as an entity). In a separate table you would have Transactions,
with fields for BuyerID (linked to Buyers, who bought or sold); SaleDate
(don't use the reserved word Date as a fieldname), ItemID (a link to a table
of items, or if they're all unique you could just have a text field for the
name of the item) and InvoiceNo.

It would certainly be WRONG to have separate Access tables for each buyer
<shudder>. Instead you would have one big table with a field identifying the
buyer.

You can link to the existing Excel data and run Append queries to migrate the
data into your normalized tables.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Reality]

A thousand thank you Mr. Fenton for your response and thank you for
sharing with me your foresightedness into my problem.

Since posting, overlooking my banging my head against all available
walls, and threatening my computer to come up with an answer or else
face being thrown out the window, and after evaluating my Excel skills
on a scale of 1 to 10, 10 being the highest, my skills sat at an one,
while my Access skills on the same type of scale sat at an minus one:
I have temporarily settled on constricting each of the 42 buyer/
sellers to separate Excel worksheets with just four columns of data:
Name, Date, Item sold or purchased and invoice number.

Admittedly, this defeats the immediate purpose of the Access project
but it sets an immediate objective that being some progress even if it
is only a little progress which is progress being attained through the
combing of buyer/sellers into one format, which is Excel. This data
alone will extend from this year back to January 1, 1997.

The Plan will then become that of transferring each of the buyer/
seller Excel worksheet into Access as a Table, thus the 42 link table
requirement.

Once in the Access project I will add the pertinent information, such
as, address, buyer, seller or both, terms of payment, the name(s) of
the chemicals—72 chemical option-- bought or sold, quantity and
delivery information along with the regulated or non-regulated
information and/or the features that distinguish the two; placards
requirements, driver license of the driver properly State stamped,
route restrictions, etc.,.

Mr. Fenton, I apologize for this unavoidable delayed response, but I
certainly thank you for the light of hope that you have projected onto
this project.
[Monitors please edit as needed]

You don't need 42 tables. You need 2! Or maybe 3.

In Access, you would have one table for Buyers (with fields shuch as BuyerID -
an autonumber primary key; BuyerName; Address; and other info about the
buyer/seller as an entity). In a separate table you would have Transactions,
with fields for BuyerID (linked to Buyers, who bought or sold); SaleDate
(don't use the reserved word Date as a fieldname), ItemID (a link to a table
of items, or if they're all unique you could just have a text field for the
name of the item) and InvoiceNo.

It would certainly be WRONG to have separate Access tables for each buyer
<shudder>. Instead you would have one big table with a field identifying the
buyer.

You can link to the existing Excel data and run Append queries to migratethe
data into your normalized tables.
--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com

Mr. Vinson:
Thank you for stopping this continual circular nightmare—I have indeed
implemented your solution: I have received the effective and efficient
results.

Thank you for understanding my inability to see the Access solution
and I do thank you for showing to me how to perceive the Life Lesson
that was involved in this entanglement.

Please stay well, Live in Peace

G. Kennedy
[Moderators for the benefit of this Group please edit appropriately]

 

[John W. Vinson]

Thank you for understanding my inability to see the Access solution
and I do thank you for showing to me how to perceive the Life Lesson
that was involved in this entanglement.

<g> Glad to have been of assistance!
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com