HELP! Microsoft Access 2003 guru's please?

Question

I wish to create an inventory where there are a number of buildings each with many rooms, each room containing 'assets' for example light fittings, these may be of different types or sizes in each room. For example, a standard fluorescent fitting or Category 2 or.....the list of possiblities is long. The next field in that record would then have 'Length of tube, next number of tubes etc. My problem is not defining what I need to record, but enabling the data to a master file, such that I can query the file for reports etc. I would like to have a 'Front Form' to be able to select from a list, the type etc, then...next record.....

Thanks, mik, I've done that...Centre record table and individual 'feeder' tables, my real problem is the 'getting the data to the main table. for example, there are different types of fittings in different quantities per room. From a table of 'fitting type', feed main table, add quantities. For a different fitting type I would need a new record on main, then select my second type. Its the getting of this data to the main table thats my problem.

Thanks, mik, I've done that...Centre record table and individual 'feeder' tables, my real problem is the 'getting the data to the main table. for example, there are different types of fittings in different quantities per room. From a table of 'fitting type', feed main table, add quantities. For a different fitting type I would need a new record on main, then select my second type. Its the getting of this data to the main table thats my problem.

RDBMS no problem, third normal form etc.... its the 'translation' to Access where I fall down. Must beg, borrow or steal book!

Answer 1

you should have everything in seperate tables, for example light fittings and in that table you can put details like what tubes they take and how many etc.
it may take you several tables to list all your assets, then you just need a table for your rooms and you can put things like room location and size etc.
finally you need a table to go into the middle like a junction which can draw its information from the other tables and from that you can do forms and querys once the relationships are done.

i hope this helps most people get it wrong by trying to put to much info in one table.

Answer 2

You need a book, yes: "Access 2 Power Programming". Long out of print, but still relevant and available used at Amazon.com for as little as 50 cents. The author makes nothing except the satisfaction of knowing that you learned something today.

That said, in Access you have no need to "get the data into the master table". There is no data to move if you have a sound table design. Your problem lies with constructing the resulting queries, not the data.

Answer 3

I would suggest your time is better spent right now to learn about relational databases and database structures. The first step in the design of a good database is to understand the relationships in various levels of data, to understand what types of data you are dealing with, to know your users needs (e.g. what reports ultimately have to be produced), and to decide how you will maintain data integrity. I agree with the previous respondent - you first need to understand the reasons to use a relational database.

If you don't want to spend the time or money on a course, then get a good book and teach yourself by first practicing on things that are less difficult than an inventory system. Another good option is open the "Northwind.mdb" sample database and study it's design. This will teach you a lot about database structure if you take the time to disect it. The help features in Access also go a long way to explaining things, but it will take time.

Regards.

Answer 4

You are missing the reason to use a relational database. No joiner table or master file that gets its information from other tables is used.

The tables relate directly to each other through common fields and relationships you set up. Queries are run on multiple tables directly and reports can come from those queries.

Read the manual or take a course it is way too long to explain here how to set up the whole thing.

Answer 5

Once you learn to set up a database properly it can run a whole company. However, I suggest that to get it right (because if you don't at the outset, you'll have a lot of wasted time) you enrol on a database course to learn all about relationships, normalisation, entities and attributes etc.

Answer 6

Please see my answer on your further worded different published question. YA is very inconsistant at exhibiting all the published questions and would take distinctive searches to discover reproduction questions.