Prev: create a database
Next: 2003 vs 2007 question
From: bs base bs on 22 Mar 2010 11:43
I am fairly new to Access and am designing a new database. I retreive all of
the data from various Excel worksheets. I have 5 tables that I will update
from excel on a monthly basis then display the information in forms and
reports. And, 1 tablet that I will add information directly into the database
in a seperate form.

All of the tables have information pertaining to the parts that we make. So,
I have made an additional table that lists just the part numbers and use that
in creating relationships between the other tables, but am not sure that I
need to do that. The 5 tables do not all contain the same part numbers. One
table may have 3,000 parts and another table may have 2,500 parts and only
300 matching parts.

Am I on the right track to relate these tables or do I need to approach it
another way? We add new parts numbers on a regular basis so I am seeing that
I might run into some major issues with the parts table.

From: Jeff Boyce on 22 Mar 2010 11:59
I'm not clear, based on your description, whether your Access tables are
merely receptacles for Excel data, or if you've taken advantage of the more
powerful, relationally-oriented features/functions in Access by normalizing
your data structure.

If you simply import data from Excel into Access, keeping the Excel
structure, you probably have NOT.

If you import (or link to) "raw" Excel data, then use queries to parse the
data into well-normalized relational tables in Access, then you definitively
HAVE.

More info, please ... ("how" depends on "what")

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"bs base" <bs base(a)discussions.microsoft.com> wrote in message
news:41A7543B-1793-4119-A11E-A281FBA54B3C(a)microsoft.com...
>I am fairly new to Access and am designing a new database. I retreive all
>of
> the data from various Excel worksheets. I have 5 tables that I will update
> from excel on a monthly basis then display the information in forms and
> reports. And, 1 tablet that I will add information directly into the
> database
> in a seperate form.
>
> All of the tables have information pertaining to the parts that we make.
> So,
> I have made an additional table that lists just the part numbers and use
> that
> in creating relationships between the other tables, but am not sure that I
> need to do that. The 5 tables do not all contain the same part numbers.
> One
> table may have 3,000 parts and another table may have 2,500 parts and only
> 300 matching parts.
>
> Am I on the right track to relate these tables or do I need to approach it
> another way? We add new parts numbers on a regular basis so I am seeing
> that
> I might run into some major issues with the parts table.
>


From: bs base on 22 Mar 2010 13:41
Thanks Jeff. I will be doing more with the data, but am still learning how to
take those next steps. Just didn't want to go down the wrong road.

The most important thing that I need to do with the information is converge
it into a form where I will input the part number and get the result from the
individual tables. I have that set up with subforms for each table. And it
seems to be working well.

What I am currently working on is that on each subform I will need to do
gross margin and mark up calculations where those costs / prices are compared
to data from each of the other tables. I haven't learned how to do this part,
yet.

"Jeff Boyce" wrote:

> I'm not clear, based on your description, whether your Access tables are
> merely receptacles for Excel data, or if you've taken advantage of the more
> powerful, relationally-oriented features/functions in Access by normalizing
> your data structure.
>
> If you simply import data from Excel into Access, keeping the Excel
> structure, you probably have NOT.
>
> If you import (or link to) "raw" Excel data, then use queries to parse the
> data into well-normalized relational tables in Access, then you definitively
> HAVE.
>
> More info, please ... ("how" depends on "what")
>
> Regards
>
> Jeff Boyce
> Microsoft Access MVP
>
> --
> Disclaimer: This author may have received products and services mentioned
> in this post. Mention and/or description of a product or service herein
> does not constitute endorsement thereof.
>
> Any code or pseudocode included in this post is offered "as is", with no
> guarantee as to suitability.
>
> You can thank the FTC of the USA for making this disclaimer
> possible/necessary.
>
> "bs base" <bs base(a)discussions.microsoft.com> wrote in message
> news:41A7543B-1793-4119-A11E-A281FBA54B3C(a)microsoft.com...
> >I am fairly new to Access and am designing a new database. I retreive all
> >of
> > the data from various Excel worksheets. I have 5 tables that I will update
> > from excel on a monthly basis then display the information in forms and
> > reports. And, 1 tablet that I will add information directly into the
> > database
> > in a seperate form.
> >
> > All of the tables have information pertaining to the parts that we make.
> > So,
> > I have made an additional table that lists just the part numbers and use
> > that
> > in creating relationships between the other tables, but am not sure that I
> > need to do that. The 5 tables do not all contain the same part numbers.
> > One
> > table may have 3,000 parts and another table may have 2,500 parts and only
> > 300 matching parts.
> >
> > Am I on the right track to relate these tables or do I need to approach it
> > another way? We add new parts numbers on a regular basis so I am seeing
> > that
> > I might run into some major issues with the parts table.
> >
>
>
> .
>
 | 
Pages: 1
Prev: create a database
Next: 2003 vs 2007 question