DB desing

[Felipe]

I'm building a very complex database that involves lots of calculation, 114
to be exacted. I have lots of data to enter before the calculation, around
200 different data. I divided in several tb and link than with a id key. When
trying to make the query to do the calculation I find out that some of the
calculation where too complex for access.
Would I be beater of putting all the data in one table? All these data is
related too the cost of new construction site of my company.

 

[Rick B]

You can't put it all in one table. You have too many fields for that.

Give us an example or two of your calculations.

 

[Jeff Boyce]

Felipe

If what you are doing requires extensive (and complicated) calculations, why
are you trying to do this in Access? It sounds like you are trying to do
things that a spreadsheet was explicitly designed to do.

Regards

Jeff Boyce
<Office/Access MVP>

 

[Felipe]

Well I have to calculate the percentage of use of an engineer than calculate
the total cost of the engineer. I do that with every type of employ than I
sun every one to calculated tax on that, than I also calculate all the head
counters cost of administration of the site and so on. Plus I calculate the
insurance and the transportation and the rend of equipments and so on.
Right now we have all that in excel but it is not efficient because does not
work like database. For every new construction site we open a new file in the
excel and we losing lot’s of really valuable data.
I hope I was clear enough; English is not my first language
"Rick B" escreveu:

 

[Pat Hartman\(MVP\)]

It sounds like you just copied your spreadsheet format and called it a
table. When you convert from Excel to Access, you also need to reorient
your data to normalize it and eliminate the repeating groups. Tell us a
little more about the tables you have and the columns they contain.

 

[Felipe]

Well I divided everything in 12 tables linked by a id number. I did not just
copy the table from de spreadsheet. I analyzed de the spreadsheet and copy
only the data that was input and divided in 12 different tables. With those
data I made 12 queries to make all my calculation, and now I’m having
problems. I think that meaby the numbers of tables are too big and I have too
many relation ships between then. For me It should all be in one table, once
is all related to the same type of data. It is the same type of data because
every data in all the tables’ changes every time I enter new record. I have
one table for Cliente, but even that one always changes, there is not a same
Cliente for too different sets of records.

"Pat Hartman(MVP)" escreveu:

 

[Pat Hartman\(MVP\)]

Felipe,
Just taking a bunch of fields and spreading them over 12 tables isn't
normalizing the table. If you have so many fields that they can't be
contained in a single table, the problem is almost always that you have
"flattened" a repeating group as is normally done with Excel. For example,
in Excel, you would usually create a column for each month and probably
create a new spreadsheet each year. In Access, or ANY other relational
database, you would use a single table and the data for each month would end
up in a separate row rather than in a column. That would allow you to add
an infinite (within the constraints of total .mdb size) number of rows for
any number of years.

You still didn't tell us any more about what type of data you are keeping in
these 12 tables.

 

[Felipe]

The data I put in the tables are not related to months or any kind of date.
The base of my access data base is a spreadsheet that calculates all the cost
my company will have in a new construction site. But is not divided by mouths
or any thing like that. I calculate de quantity of people working there and
there salary then the tax and the administration cost, the rent of
equipments, preaty much all cost I will have in the end. I do want after
finishing this first part of the data base to do something that enables me to
calculate how much I’m spending and compare with how much I plan to spend.
Also would be nice to be able to put in how much I’m spending each month, but
I’m not really interest on that right now. All I want to do is data base that
calculates and keep the total cost. I have that all ready and is working all
I wanted too know is if I put all in a smaller number of tables would work
better.

The type of the data is like that.
N° of employs
% that the employs works in the site
Salary of the employ
Cost in general- like phone, copy machine, documents preparation and so on
Percentage referring to taxes
Cost of contractor

"Pat Hartman(MVP)" escreveu:

 

[Pat Hartman\(MVP\)]

Not all repeating groups are obvious. They don't stand up and say - Hi, I
am a repeating group. Months, days, names that have suffixes or prefixes
are pretty easy to identify. But you have a repeating group that you are
not "seeing". That group is "costs". By creating a separate column for
each individual cost, you are flattening the relationship. There should be
a table that defines valid costs that you will be able to use to populate a
combo box. The table to record costs should have only a couple of columns -
TransactionID (autonumber, primary key)
CostID (foreign key to tblCostType - this is the table that defines all
costs)
TransactionDate
CostAmount

With a structure like this, you won't have hundreds of calculations. You'll
have one. You need to do some reading on normalization because you are not
there yet. One of the things you will read is that 1-to-1 relationships are
EXTREMELY rare so any time you have even one, you should examine it closely
to make sure that it is not being caused by a repeating group.