Structure problem

[JohS]

Hi. I have a several groups of equipment which I want to store in the same
database. But I'm struggling with finding out how to structure the database
when I want several different capacities stored in the same base, like this:



PU (Pump Units) which is has its own various capacities within

a liter/minutes

b bar

c liter



NT (Nitrogen Tanks) which is has its own various capacities within

a liter

b bar



SU (Steam Units) which is has its own various capacities within

a kg/ hour

b celsius

c kw



and others.



How can I organize my database to be able to "cover" up to 3 fields which
contains the different types of capacities? JohS

 

[FredFred]

Three completely different sets of data like that should probably go into
three tables, and then the three tables go into one Access database. That
gives you what you asked for if taken literally and in Access terminology.
But if by "database" you meant "table" this recommends against your idea.

 

[JohS]

Yes, I'm not precise when I illustrated this case. I meant all done inside
an Access database, but hoped that there could be a way to get around this
capacities issue.

I would like to sort in a simple way on "one" (and maximum three) field ie.
all the PU's (Pump Units) with its fields values "liter/minutes", "bar" and
"liter", but included also the options which could be a substitute like all
the PP's (Pump for Pigging) though this only have the capacities
"liter/minutes" and "bar" (not "liter" on PP). (It's about renting out
available equipment).
This is why I hope to avoid putting it into one table for each group.
(I had tried to put all "capacities" into one table connected by codes and
then connect these codes connected into another table where the groups are,
which again are connected towards the units with its all capacities. But I
can't get this to work / connect correctly when it comes to connection per
unit and it's capacities). Any suggestion? JohS

 

[Pat Hartman]

There is no perfect solution for this problem. It would be nice to have a
flattened structure with specific fields for each type of data but that
becomes inflexible and cumbersome when there are more than a couple of types
of items in the list. I have solved this problem (it actually exists in a
number of different business applications) in the past by creating an
ultra-normalized structure. You have a table for equipment which stores all
the common fields. You have a table to define capacities so this would hold
the names "liter/minutes", "bar", "liter", etc - one per row. The third
table is a junction table that connects a piece of equipment with its
capacities. This structure allows you to support an infinite number of
capacities per item, not just the three you request. You can "flatten" the
structure if you need to by using a crosstab query.

tblEquipment:
EquipmentID (autonumber PK)
PartName
PartType (PU, NT, etc)
....
tblCapacities:
CapacityID (autonumber PK)
CapacityName (Bar, Liter, Celsius, etc.)
....
tblEquipmentCapacities:
EquipmentID (PK, field 1, FK to tblEquipment)
CapacityID (PK, field2, FK to tblCapacities)
CapacityValue
....

 

[Tony Toews [MVP]]

How can I organize my database to be able to "cover" up to 3 fields which
contains the different types of capacities? JohS

I'm with Fred and Pat. Today it's three types of capacities.
Tomorrow who can say.

Also it gets to be a pain in the you know what when doing queries on
"Show me all pumps with greater than x litres per minute" when you
have to query three separate fields on one table.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[JohS]

Thanks to you all. Johs