Need help with designing table/query/form/report for project!

[mfearer94]

Hi there everyone. So basically, I'm responsible for creating a database for my company that acts as a quoting system where the database stores information for each part number and then quotes can be created based upon a lot of different criteria. The system we currently have is on excel and hasn't been updated in around 15 years (which means lots of fun for me...). There are a bunch of different tabs with different sections such as summary, labor, company information, quote, and other tabs for specific manufacturing processes. Right now I have a "Part Table" and a "Company Table" with many forms and queries for entering BASIC data for both the parts and customers that go along with the parts. Now I need to develop a system that is similar to the excel format, but much neater and up to date. My initial thought was to have all of the information in the "Part Table" and call upon and edit that information through various forms which would be in a navigation form for easy navigation, obviously ha. That seems very cumbersome and messy so then I thought about having separate tables for each "tab" and calling upon the specific information I need for each form through a query using data from both the "Part Table" and specific "tab" table. This seems neater to me, but I feel as if I will run into a lot of problems. What are you guys thinking about this? Thank you very much for reading this rant and any ideas will help.
P.S. I'm not very good at SQL and I have about 1 years limited experience with access teaching myself so I'm not that good, but I can handle myself. Thanks again!

[mfearer94]

Oh and here is an example of one of the tabs. I want to make a form that looks like this, but how would I ever create enough cells for all the inputs without spending hours creating EVERY SINGLE COLUMN in a table? Thank again.

[ssanfu]

Welcome to the forum.

You've got to stop thinking about spreadsheets and start thinking in terms of relational databases: two completely and totally different animals.
Search the web for "Normalization". Crystal has a site on Access basics: http://www.accessmvp.com/Strive4Peace/
Here is another site: http://www.deeptraining.com/litwin/d...aseDesign.aspx

Also, learn about "split databases FE & BE". The BE (back end) holds the tables - the FE (front end) holds everything else: queries, forms, reports, code.

Naming convention: find one you like and stick with it. Read these two sites:
The Ten Commandments of Access
http://access.mvps.org/access/tencommandments.htm

The Evils of Lookup Fields in Tables
http://access.mvps.org/access/lookupfields.htm

After reading (a lot) about normalization, use pencil and paper (I use whiteboards) to write down what fields/data need to be in which table. Forget for now the queries, forms and reports. You have to have a good table structure; otherwise you will struggle with the forms and reports (in my experience).

When you think you have a good structure, post back with more info on what you are trying to accomplish and what you think your table structures should be. There are lots of posters that will make suggestions......

Again, forget about "how you do it in Excel".

It sounds like you might be heading towards an Order/order details type of structure. There are templates available that you can look at or modify.
I don't like the forms and reports from Microsoft models, but you can look at these examples:
Desktop services template
http://office.microsoft.com/en-us/templates/results.aspx?qu=access&av=zac140&queryid=5186de32-cbe5-4aba-8ee6-71c3ae10bbfb#pg:2|ai:TC102489834|

Desktop Product inventory database
http://office.microsoft.com/en-us/templates/results.aspx?qu=access&av=zac140&queryid=5186de32-cbe5-4aba-8ee6-71c3ae10bbfb#pg:2|ai:TC102483123|


The "Database Answers" site has models to look at:
Industry-specific Data Models in 50 categories
http://databaseanswers.org/data_models/

All Data Models
http://databaseanswers.org/data_mode...all_models.htm



You have a major project on your hands...