Design Help (1 Viewer)

[tmyers]

I am still having a really hard time designing a proper structure and forms for another quotation app I am attempting to build.
I was given an Excel workbook to go off of, but each sheet within the workbook uses a completely different structure. I pulled one out of the workbook that I think is laid out the best.

I have asked about this project before and attempted to do my own structure with the help of others from here, but with the amount of junction tables I ended up with, it has become to complex for me to work with. I would say the main thing I am struggling with is the forms currently.

Could someone take a look at the example worksheet and take a look at what I have built so far and see if I am on the right path? I think per usual I may have overcomplicated the living daylights out of it. The worksheet provides a somewhat nice example of how I want to structure the data, as well as what I am attempting to do.

 

[CarlettoFed]

Sorry but from the excel file you understand little and nothing, perhaps it is better if you attach in file what you need to start from to recreate the structure in Access. Also you should describe step by step what the program should do, example:

• enter the Building
• create an estimate of the works to be carried out in the building
• enter the components used in the estimate
• ...

 

[tmyers]

Sorry but from the excel file you understand little and nothing, perhaps it is better if you attach in file what you need to start from to recreate the structure in Access. Also you should describe step by step what the program should do, example:

• enter the Building
• create an estimate of the works to be carried out in the building
• enter the components used in the estimate
• ...

This is for quoting the electrical equipment for apartment complexes, both multi-building and high rise. 90% of the time this would be used for new projects, so the building(s) don't exist yet. I suppose a road map would be:
- Building count
- Assign metering to each building
- Unit count and unit composition of each building (what type of unit and how many of each a building consist of)
- Identify all the unique units and assign the equipment to each so it can be multiplied later.
- Provide a quote/report that details the cost breakdown of each building and/or unit. Most the time an overall price is all that is required.

The reason I am struggling with this is because of the whole container within a container within a container kind of layout. A building contains meters, but also contains various units of various counts which each also contain equipment themselves.

Edit:
I would like it to work that once the counts for everything is inputted and the equipment selected, the pricing is pulled from a single products table and a margin is applied to it.

 

[gemma-the-husky]

To build something as a database, you need to analyse the data you want to store
You don't need to consider your processes at all. If the data is designed correctly, then the processes become easy to write and model.

I rather disagree with @CarlettoFed I just don't think you need to be bothered at all with the details of what you want to do with the data at all. It's in the back of your mind, informing the data analysis, but you should never be thinking of the processes really. It's just the data.

so - how do you structure your plan of quantifying the equipment you need? Do you structure it room by room?

if so, then you need

parts table
buildings table
floors table (maybe)
rooms table
equipment table

and you get

1 building = m floors
1 floor = m rooms
1 room = m parts

so you structure the building, and then allocate the parts you want per room. You can then easily get total parts per floor, or per building by simple queries.

now with buildings you also get common areas, lift shafts, and so on - so I am not sure how you would design the table structure to accommodate those, but maybe you can treat the common areas as a "floor", and separate the common areas into individual "rooms". It flows much more easily if you can deal with the entire structure within the same model. That's the key really. You don't want to end up with rooms, but then have common wiring at a "floor" level. If you get to that point designate a notional "room" as being the whole floor for those particular processes. I think that's easier than having some equipment that you have to count at the room level, and others at the floor level, and even some at the building level.

Now when you design a form, you have a few cascading combos to pick the building, floor, room, and then you can pick the parts that go in the room, and quantify the number and cost of each element. There's some stepwise refinement. When you start to work on your forms it might make you realise you need to change your data tables, but if you did it right, there shouldn't be too much.

 

[tmyers]

To build something as a database, you need to analyse the data you want to store
You don't need to consider your processes at all. If the data is designed correctly, then the processes become easy to write and model.

I rather disagree with @CarlettoFed I just don't think you need to be bothered at all with the details of what you want to do with the data at all. It's in the back of your mind, informing the data analysis, but you should never be thinking of the processes really. It's just the data.

so - how do you structure your plan of quantifying the equipment you need? Do you structure it room by room?

if so, then you need

parts table
buildings table
floors table (maybe)
rooms table
equipment table

and you get

1 building = m floors
1 floor = m rooms
1 room = m parts

so you structure the building, and then allocate the parts you want per room. You can then easily get total parts per floor, or per building by simple queries.

now with buildings you also get common areas, lift shafts, and so on - so I am not sure how you would design the table structure to accommodate those, but maybe you can treat the common areas as a "floor", and separate the common areas into individual "rooms". It flows much more easily if you can deal with the entire structure within the same model. That's the key really. You don't want to end up with rooms, but then have common wiring at a "floor" level. If you get to that point designate a notional "room" as being the whole floor for those particular processes. I think that's easier than having some equipment that you have to count at the room level, and others at the floor level, and even some at the building level.

Now when you design a form, you have a few cascading combos to pick the building, floor, room, and then you can pick the parts that go in the room, and quantify the number and cost of each element. There's some stepwise refinement. When you start to work on your forms it might make you realise you need to change your data tables, but if you did it right, there shouldn't be too much.

Going with that idea, would I just add to the building table to get the equipment for the building itself? Following what you described, I think I could do it as a "floor". Floor 1 would consist of rooms (including common areas) and the equipment for the building itself (such as the electric metering centers).

I think an argument could be made that the building table would also be related to the parts table in conjunction with the room table.

 

[gemma-the-husky]

if you have equipment for a room, then the key for this would be room id/part number (say)
if you have equipment for the building as a whole, then it's a different table and the key is building id/part number (you have multiple buildings)
if you go with floors and have equipment for a floor, then you get another different table, and the key is floor id/part number.

When you come to summarise, you end up with a nasty non-updateable union query summing buildings, floors and rooms

Assuming you want to distinguish floors, as well as rooms then even for the building as a whole, I would have a notional "floor" and a notional "room", and put the building-only equipment in that notional room on that notional floor. If you want to skip the floors then do so, as long as you can distinguish the 10th floor staircase from the 11th floor staircase. Would you class them as different rooms in the building? It doesn't matter as long as you are all in agreement about exactly where any given "room" actually is.

Anyway, as long as equipment is always located in a "room", you still only have a single query to sum all the materials.

So, if your floor 1 is a notional floor, then I think you get get my thoughts. It's just a tricky naming convention as no doubt there IS a real street-level/ground floor/first floor/mezzanine/rez-de-chaussee/basement/sub-basement/undercroft etc etc