Large project

[Ernesto]

Hello all!

I wasn't sure where to start...so i wanted to begin my questioning
here; cause I've had nothing but good results from doing research on
this group.

My department sells a variety of accessory for vehicles (ie alloy
wheels, body kits, spoilers, etc). On average we have aprox. 30-40
accessories per vehicle. And we have about 30 vehicles. We are
currently using Excel as a tool to price our accessories. But it is
very difficult for a person like myself who is trying to track and
report on historical performance of individual accessories across our
vehicles, because that data is across may different worksheets. And to
add the problem the worksheets only house the latest price update; it
does not house changes.

So, in order to answer that question, a co-worker designed an Access
dB. Basically there is one dB for each vehicle. In each dB, there is
one table which contains ALL pricing data fields (it also include a
variety of misc fields). The problem now is, if a manager wants to ask
a question like "How is my alloy wheel doing across 3 or 4 vehicles?",
I still need to look at all the dBs, append them, and report off of
them. You see the issue?

What i was hoping to do is this...use Excel as a front end and pull all
data fields into Access. Reason for using Excel is because all of our
admins are comfortable with Excel, and they know it [Excel] very well.
Does that sound like a reasonable idea? Or would there be another way
to do this...I am open to suggestions.

Please let me know if you have any ideas/comments/opinions...I'd like
to get some fresh ideas...i'm running on E.

Thanks!
Ernesto

 

[ifoundgoldbug]

Greetings

I am by no means a DB design expert. but a really easy way for you to
look at all of that data at once would to be a linked table which can
directly read/write to another database file. this way you could have
all 30 of your DB's linked to 1 master DB where you could run your data
mining from.

again it is just a thought (and the way that i went about doing our
database) and again i am no db design expert but over all the linked
tables is a simple effective way to do what you want.

Hope it helped

Gold Bug

 

[Tom Wickerath]

Hi Ernesto,

The tables in your 30 databases should be combined into one database. I
don't mean simply importing or linking all of the individual tables. Like
data should be combined with like data in each table. You'll likely need to
add one or more fields to identify the car type for each set of data added to
the table. You'll also likely need a table of the 30 car types.

My recommendation is to study up on database normalization, and then try to
sketch out a workable database design on paper, using pencil and a good
erasure. After you have a suitable design, then try to implement it.

http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#DatabaseDesign101


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[JK]

Ernesto,

If I understand correctly, You have 2 tables (Cars & Parts). In this case
you need one more table which groups the parts call it say, "Generic",
"classification" or whatever

Generic_ID -> auto number
Generic Name-> alloy wheels, mirrors, bumper bars etc,
etc.

Each part is associated with a generic name by adding a field Generic_ID to
Parts table and creating a one to many relation between Parts and Generic.
In this way you be able to look at all alloy wheels across all cars

Regards/JK

Greetings

I am by no means a DB design expert. but a really easy way for you to
look at all of that data at once would to be a linked table which can
directly read/write to another database file. this way you could have
all 30 of your DB's linked to 1 master DB where you could run your data
mining from.

again it is just a thought (and the way that i went about doing our
database) and again i am no db design expert but over all the linked
tables is a simple effective way to do what you want.

Hope it helped

Gold Bug

Hello all!

I wasn't sure where to start...so i wanted to begin my questioning
here; cause I've had nothing but good results from doing research on
this group.

My department sells a variety of accessory for vehicles (ie alloy
wheels, body kits, spoilers, etc). On average we have aprox. 30-40
accessories per vehicle. And we have about 30 vehicles. We are
currently using Excel as a tool to price our accessories. But it is
very difficult for a person like myself who is trying to track and
report on historical performance of individual accessories across our
vehicles, because that data is across may different worksheets. And to
add the problem the worksheets only house the latest price update; it
does not house changes.

So, in order to answer that question, a co-worker designed an Access
dB. Basically there is one dB for each vehicle. In each dB, there is
one table which contains ALL pricing data fields (it also include a
variety of misc fields). The problem now is, if a manager wants to ask
a question like "How is my alloy wheel doing across 3 or 4 vehicles?",
I still need to look at all the dBs, append them, and report off of
them. You see the issue?

What i was hoping to do is this...use Excel as a front end and pull all
data fields into Access. Reason for using Excel is because all of our
admins are comfortable with Excel, and they know it [Excel] very well.
Does that sound like a reasonable idea? Or would there be another way
to do this...I am open to suggestions.

Please let me know if you have any ideas/comments/opinions...I'd like
to get some fresh ideas...i'm running on E.

Thanks!
Ernesto