DB construction help

[Andy_d]

Hello internet.

I have recently started doing a db at school, however my teacher is the worst one possibly in the history of creation and has essentially just said 'google it'. now id quite like a good grade, so if anyone could point out some good websites, or guides, or give any advice that would be wonderful.

Here is what id like to build (if possible). It based of a summer job i had sorting phones for recycling, they had a system like this.

Sheets :
Master
Passed
Dispatched
Failed

There are a bunch of feilds to go in. The master will contain all of them, and then each other sheet will have some of the generic, and then a couple of feilds found only in that sheet and the master.

What id like to be able to do with the db is to be able to enter data onto the master sheet and then have access take that data and it copy to the appropriate sheet. the db would also need to be able to have the master edited so some thing could move from passed to dispatched without going and having to fiddle with those individual sheets.

As a secondary thing id like to have a form to input data onto the master, and have a preset report that could pull up the data (lists of phones) and summarise it, by giving only the quantities of each phone (64 iphones, 32 samsungs ect)



if anyone can help with this, i would be deeply gratified.
Andy_d

[timmy]

this template might help you:
http://office.microsoft.com/en-us/te...001018467.aspx

It maybe a bit advanced for what you need, but you can simplify it. Essentially your teacher is teaching you good practice, i spend a good amount of my workday on google / forums .

If that template isnt what you need, search around for one that is, ive found templates great as im only a beginner with access.

[Andy_d]

Humm, im not sure i understand access well enough to use these. can you point me in the direction of a free guide, like a for dummies on the internet??

[orange]

Andy-d,

You might want to read the Entity Relationship Diagramming link at this site.

http://www.rogersaccesslibrary.com/forum/topic238.html

It will help with getting oriented.

[timmy]

I've always seen templates as a perfect starting point, there are plenty of books you can read if you don't benefit from practical learning tho. Amazon.com is a good place for books, i'm sure your aware of a few internet shopping sites, just take a look on there.

I'm sure someone else could help you better with books, i personally hate learning by reading.

[Andy_d]

Timmy, Im sure once i had a vague clue as to what im doing then the templates will be a great help, but i just dont at the moment know my arse from my elbow when it comes to access.
That looks great Orange, i have a good read round of this tonight and over the weekend. And tbh i know i could buy a book online, but id really rather not cough up 15 quid simply cause my teacher can teach :/.
Thanks guys

[orange]

Yes I hear you. Here are some other great online resources

Great all round data base /programming/ standards/examples
http://www.accessmvp.com/strive4peace/

Online free video tutorials
http://www.datapigtechnologies.com/AccessMain.htm

[Andy_d]

Right, from what I've been reading I have managed to come up with this so far.

Phone info
ID *
Type
Model
IMIE Warranty
Status

Passed
ID*
Status
Stored

Failed
ID*
Status
Reason
Disposal Date
Stored

Dispatched
ID*
Status
Gordon Order
Date
Location
Employee


I have set the ID as the primary key in each table, and set up a one to one relationship between Phone Info and the others. Note the Passed, Failed and Dispatched Tables are not linked by any relationships.
The only attributtes that have a unique value are the ID's, the IMEI (the standard indentifying code for mobile phones, like a barcode), and the Gordon Order (a reciept of request that authorises a phone to be dispatched).

This is where I get unstuck. I want to be able to have the DB update information automatically based on the value that is entered in Status (it would be Passed, Failed or Dispatched). So for example, I input the Type, Model, IMIE, Warranty and Status, with the ID being a unique autonumber. The database would then look at the Value for status, and would copy accross the ID and the status into the appropriate table (passed would copy into Passed Table, failed into Failed Table, dispatched into Dispatched Table).

Can anyone advise on how to do this, or where some advanced reading may be found that could teach me how to manage this.

Also, have consolidated Status, Type and Model into seperate tables according to normalisation, but that seems to me now to be overly complex, and so I havent included it above.

Thanks
Andy_d

[orange]

Good stuff Andy,

Here are a couple more items worth reading and key to getting things organized.

Read this page regarding Business Rules/Facts that your "system/database" must deal with.
http://www.databaseanswers.com/data_...sets/facts.htm

Here's a list of design ideas. Good reference and check list.
http://www.databaseanswers.org/approach2db_design.htm

A personal note.

Using the materials so far, create a model of the things involved in your "business".
Pencil and paper is OK for this.
Define each of the Things (Entities) and the Attributes that describe them.

A 1 or 2 liner is sufficient and will become documentation for Tables later. It will always be a good reference whenever (if ever) you have to adjust/expand anything.

Create some test data that is representative of your "business".

Work your test data against the model. I call this "Trying to Stump the model".
Before you get too deeply involved in the database software or complex Forms etc, work the test data against the model. Adjust the model if things don't quite work.
Or adjust the test data if it proves to be "wrong" in terms of your business.

Once you know your model matches your intended business, proceed with database and associated details.

[Andy_d]

Right, I've added in some data, but I cant seem to find a way of getting the tables to link to each other, which seems to me to be a vital part of the whole thing.
I wasnt sure is this was due to a lack of unique data, as the ID's seem to be unique only in their individual table, so the IMIE's were added to each table, and set as the primary keys. Would I need to set the pirmary key as a multi-cell selection? Or set P/F/D tables with foriegn keys?

And orange, that first webpage ddint make any sense to me :/

[orange]

Can you attach (zip) a copy of your mdb?

Business rules/facts didn't make sense? Well they will by the time you're
finished.

[Andy_d]

Humm, I cant seem to see how to do an attachment.

But what ive got now, from experimenting with form is a rather clumsly way of filling in data that almost matches my requirements. I have set up a series of forms that allow me to add the data that I need into the different tables, but some of the values are having to be repeated, and im not sure why. It doesnt seem to just the the primary key feilds that dont ned doubling up, but I cant figure out the process for ensuring none of the do, as a basic link in the relationships screen doesnt seem to solve the problem.

Also, I cant seem to get access to create ID numbers like i want it to, as in a unique number for each submission via the forms, rather then one subbmission being given two numbers, one in phone info and another in P/F/D.

[orange]

Andy,

Under the reply box are 2 buttons

1. Submit .......... 2. Go Advanced

You need to Go Advanced, then below the box is another button

Manage Attachments <<< ---That's the one to add the attachment.

Just make a zip from your mdb and attach it. I'll take a look and advise.

Note. This stuff isn't trivial, but with a little practice and some fundamentals
it can be tamed.

[Andy_d]

Ahh, I see it now. *facepalm* Thats almost depressingly simple....

Right, good news. I think I, along with some help from a guy three seats down, have figured a way of using cascade delete and update, forms and queries to establish a basic working model.
Input data via form.
then when time comes to change it, use queries to find the unique data (IMIE). Delete that data and cascade delete will remove the rest of the data relating to that number in both a P/F/D table and the phone info table.
then go back, use another form to re input the data so that it is now correct.

This process does work, but theres still some stuff id like to try. First off, I'd like to be able to simplify the forms. at the moment they are a little bit clumsy with some info needing to be entered twice. Also if some of the feilds could be automatically filled, or have only a few options for what could go in that would be nice (specifically when it comes to type (standard/feature/smart) and status (Passed/failed/dispatched). That would make data entry far far easier. In fact, would it be possible to have many many forms, each specifically tailored to match as many feilds as possible? one for standard passed, one for standard failed, standard dispatched ect??
Also, I think its gonna be very important to be able to seach for a specific IMIE number on the query, so how possible it that??

[Andy_d]

Hey orange, timmy, I just wanted to say thanks for the help. ive got the Db working now, just a few nicities that im trying to nail down now.

So, cheers. Pint on me if we ever meet irl

Andy_d