Question Building a new Database, Various Questions

[New.Dimension6267]

Working on Access 2007, I'm building a new database to track cost in construction. I have a working template of it in Excel but I decided that I would benefit from the queries available in Access to get more out of my data.

This is my first time building a database, so I did some reading (e.g. Excel for dummies) and a couple of forums but there seems to be always something that I can't figure out so I thought I'd post my progress here and ask for help.

I've attached my access file and a scanned copy of the final Report that I need to create so you get a sense of the logic.



Current Problem
My current problem is that the records showing in the form "Basic" are not editable.

 

[plog]

They aren't editable because you are basing the form off of a datasource that isn't editable. A form should be based off a table.

My guess is that you should base your main form off of the Basic Table. You would then create 2 sub-forms each based on the other 2 tables in your database and place them on the main form.

Honestly though, forms and reports are a ways down the road. You first need to structure your tables correctly. I'm not saying they are improper, but based on the report you want to create, not all of the data is accounted for in your database tables. For now, focus on your tables and worry about forms and reports once you have those squared away.

 

[New.Dimension6267]

Thanks for the input, all the data is accounted for, the other fields will be just sums of the data I have in Basic Table.

I created the form using the form Wizard and selected the fields I wanted from the 3 tables that I have. I then opened the newly created form in Design View and deleted DescCategory and DescType because they weren't drop down lists. Next step was to create a combo box the result of which you see in the form.

Did I create the combo box wrong? Or is your subform suggestion the only way to do what I want? Note that I really don't want to look like several forms in one, I want it to look like a coherent single form.

 

[plog]

Like I said, it should be 1 form per table. Now, some of those forms can be sub-forms on another table, but each form should be based on a table.

So, yes, you need 3 forms--one for each table. For simplicity sake let's refer to the 3 forms using the names of the tables they are based on: Basic, Category & Type. I looked at your relationships and it says there can be many records in Category to 1 record in Basic. The same relationship exists between Basic and Type. So, in terms of forms, your Basic form would have 2 sub-forms--the Category form and the Type form.

It doesn't have to look like several forms, but from a technical aspect you will have 3 forms. For tips on how to make it look seemless you should post in the Forms section of this site.

 

[New.Dimension6267]

How about this idea, I can create a query that has all the fields I need for the form and I can base the form on it. I tried doing that but data entry is messed up.

 

[plog]

Then that sounds like a bad idea.

If only there was someone willing to post on this thread and give you the advice you want to hear as opposed to me who keeps telling you the correct way that it should be done.

 

[New.Dimension6267]

Well that was downright rude...
FYI I tried using subforms and I didn't know how to make it work. I'm reading Crysal's guide on sub-forms, a reading that was suggested in another thread on this forum. Meanwhile I thought I'd try using queries to fix my problem.

If you're against people trying different things to solve their problem may be you shouldn't be on a help forum.

 

[DBinPhilly]

I've taken your original design and redesigned it in something a little more logical from a database point of view.

Check it out and see if it helps you in learning how to set up tables with relationships.

Your original table layouts just wouldn't work in the real world.

 

[MarkK]

What is a "basic?" In a database you keep track of things that exist in the world. In a construction management database I would expect to see tables like . . .

Job.
Customer.
Order.
Estimate.
Product.

You've got a category and you've got a type, but a category of what? A type of what? Before you make forms you need to do lots more work with your tables. What do you want the system to do? Also, read up on Database Normalization, which is like the theory of how real-world objects, and how they are related to each other, can be modeled in database tables.
hth

 

[New.Dimension6267]

@lagbolt
Category is the CSI masterformat categories I have listed.
Type is consist of whether it's a budget entry or cost...
I read about Table normalization, thought mine were.

@DBinPhilly
Thank you for the example! I guess my mistake was that I didn't have one table that contained all the data (looking at your basic table). Do all access database have 1 table that groups everything together?

Current Problem:
An example entry would have the following information: $100 is under 1000 - Additinal General Conditions (Category) and is of the type Original Budget. How can I group them like the scanned example attached? I uploaded my db with the progress I made so far.

 

[jdraw]

I recommend you work through a sample database, from business overview to finished database structure. Such a tutorial exists and shows you how to identify entities, tables, relationships, primary and foreign keys...
The tutorial is at
http://www.rogersaccesslibrary.com/Tutorials/Entity_Relationship.zip

You will gain experience and appreciate what plog has been trying to tell you. There are design principles for building databases that make/allow them to support the business as intended and are easier to maintain.

This approach

If you're against people trying different things to solve their problem may be you shouldn't be on a help forum.

is great for raw science, but if you want to make good use of a forum you might want to think seriously of the advice you're being given.

Consider this analogy - you want to pilot a 747. You could
a) jump in the cockpit and do some trial and error, with tremendous luck and time you might just succeed, or
b) take some pilot training from experienced trainers and pilots; enroll in a program that leads to certification; evolve your flying skills through progressive training, aircraft, experience......

We have experienced those posters who opted for choice a). We're trying to help you see the merits of development path/option b). However, the choice is yours.

Good luck with your project.

 

[New.Dimension6267]

@jdraw
Thanks for the tutorial, printing and reading.

You will gain experience and appreciate what plog has been trying to tell you. There are design principles for building databases that make/allow them to support the business as intended and are easier to maintain.

I tried doing what he said and failed so I asked about another method, I appreciated his help at first but when it became sarcasm it stopped being help. I don't know the design principles so I'm trying to find them out, but all documents I found are for earlier versions of access.

I followed DBinPhilly example and it worked, is it not an ideal setup?

 

[DBinPhilly]

I looked at your newer version. If you wish to only have total raw numbers, I guess this might kinda work.

But if you want totals by job, you will have to set up a different database for every job.
If that's what you want, why not continue to do it in Excel?

Instead, look at the database I sent you that allows you to differentiate data by job#.

I've been designing databases for construction companies since 1974. You are going very minimalist, which is fine. But you have to separate information by job to be of much use.

Later today I'll add a bit to the database I sent you to show you how to get the report you want from the format I gave you.

 

[New.Dimension6267]

Didn't think I'd need to differentiate by job# (this project is a small test-run for me) but you're right. I'm going to revise the database.
Thank you!

 

[DBinPhilly]

No subforms are necessary in this case. There is one table that is the crux of the program.

The other tables are merely used for pull-down fields and are static. It would be easier to have a form for each of the pull-down fields to add additional static data if there is a possibility that your original list is incomplete. Or you could give the user the ability to add additional options through the pull-down field on the main form itself.

 

[New.Dimension6267]

Or you could give the user the ability to add additional options through the pull-down field on the main form itself.

I'm not planning on doing this but it peaked my interest, what feature do I need to lookup to learn to do that?

Also, did you get the chance to fix the report in the sample you uploaded? (General guidlines on how to achieve it would be great if you don't have the time)

 

[New.Dimension6267]

@DBinPhilly, did you delete your post for some reason? I got an email that said you posted the following

Here is an updated copy of the database I originally sent you, with a main switchboard to run things.

Look at the VBA code behind the buttons that drive the two reports I created to learn a way to use queries within a procedure.

but when I came to see read the post I couldn't find it.

 

[DBinPhilly]

sorry - I remembered something I wanted to do slightly differently.

again, look at the VBA code behind the report buttons to see how to execute queries inside of visual basic.

 

[New.Dimension6267]

Is there a way to setup the report so that Original Budget, Committed Contracts... are columns and the $ amount of the entry shows under the approrpiate column for each entry? (see attached example)