Second Database - Need help with overall design

[rjcoolpix880]

Greetings,



I'm working on my second database ever. My big takeaway from my first experience was getting the overall structure right from the start and spend time thinking about what you want BEFORE you begin. So here I am

Background: (hope this is not too long)

We are doing a assessment of multiple buildings on a campus (in the past it could have be 20+). This involves teams going out and gathering a lot of information and then distilling it into big ideas and compiling it into a large book to deliver to the client. (see previous.jpg) We have a current process that involves using Excel and then taking that into Indesign for layout and adding photos. I believe that Access can do this in one nice step.

What I want is for the user to see ALL the different Uniformat sections (see uniformat.jpg) after creating a new building (see blank.jpg). Then be able to add comments and photos to as many items as they choose (see new.jpg). This includes multiple lines for a single uniformat section if they choose. It also includes creating new Uniformat sections as needed.

Question:

The issue i cannot think of how to resolve is how to automatically populate a default set of Uniformat sections for each new building that is created. I can think of a way where they can create a new building and then manually add each Uniformat section that they need, however that would be WAY too slow and i don't think the users would like that.

Thanks for any help!

[JeroenMioch]

I think its hard to grasp what you are trying to accomplish. For example i had to google what Uniformat is :P
Basicly you can do anything with access. Sometimes its a pain in the head to get things done, but it it works then you save a lot of time.
I think what you want is to create a set of forms with underlaying tables where you can input your data. Off course you want to handle different projects so youll need to work with project names wich are bound to ID's to make it all connectable.

Im thinking of a main form wich you can select shell, substructure, iterior, services and so on. Upon clicking them you go to a new (sub)form and fill in stuff. All these forms are bound to the same record in the same table.
You can create OLE objects to insert your files and photo's.

Let me know if im in the right direction and if not, explain more

[rjcoolpix880]

I realized that after i posted it it removed the image names so my post is probably more confusing than i hoped.

One of my ideas was what you said. One table: rows are the building number, and the columns are the uniformat sections (which means lots of columns). however what do you do if you want to add more than one comment per uniformat section? you cant since there is only one column per section.

[JeroenMioch]

Yes lots of columns if i see your setup. But as far as i know this is no problem for Access.

You can in fact add and delete columns programmaticly. However this approach has a serious drawback and that is that you add and delete space for comment for other buildings as well.

Fot a database as complex as your visioning i would make a report on wich you write your comments and export them in a directory specificly made for that record as .pdf files.
If the comment is no longer nessecary you simply open the directory via a button and delete files no longer needed.

Drawback of this approach is that you cant see the comment until you open the file. But you can however insert the maker of the comment and subject in the file name.
Something like :
John Evans roofing inspection.pdf

[rjcoolpix880]

what about a table that basically has 3 columns? building / uniformat section / comment. Then the report or form would just filter based on building and then sort based on uniformat section. then you could create as many rows per building as you need.

i just dont know how when someone creates a new building that it automatically would create a standard set of rows containing a set number of uniformat sections. One way that i have thought about is to cheat by manually createing 50+ buildings worth of rows with blank data so that i basically manually create the default set for each building.

[cbende2]

To have several notes for one building is simple. What your looking for is another table. Let's call it NotesT, NotesT will have 3 fields, NoteID(autonum), Notes(memo), BulindingID(foreign key).

In your main form you will have a subform that list all Notes where BuildingID = BuildingID.

This will allow you to have several different notes on one building.

[ItsMe]

I am having a hard time understanding this thread. It seems the OP is making the typical mistakes made when moving operations from Excel to Access or any other Relational Database. If your tables in your database look anything like your spreadsheets, you are doing it wrong.

What you need to do is build a survey database based on the ASTM UNIFORMAT II Classification of Building Elements. I would start by getting all of the building elements into a table. Actually, it looks like you will need three tables for the elements. You will need one for each Group Level. Then start building out your tables for data entry from there.

It would not be too difficult but you will need to have a good understanding of Relational Databases and Normalization. Then, the hard part, will be the user interface and exporting the data in a format that is acceptable for reporting.

[cbende2]

ItsMe,

I didn't take the time to read the original post, I just happened to see that he was having a problem with having more than one "Notes" for a single building. Thanks for the clarification for his project though.

[ItsMe]

ItsMe,

I didn't take the time to read the original post, I just happened to see that he was having a problem with having more than one "Notes" for a single building. Thanks for the clarification for his project though.

That's fine. I was thinking along the same lines and then decided to start over and look at the pictures provided. My statement is my opinion based on the pictures because I was having trouble following the rest.