Very new to Microsoft Access 2000

[sandie1253]

I wonder if anyone can help me? I need to creat a database in Access. I took a class called Access 2000, Level 1 a couple of days ago. I went away feeling that I had a handle on it but when I got to work I found that I still knew nothing about creating what I need. I have lots of knowledge of Access terminology now though. What I need a database for is this...I manage an intercompany storeroom. My only customers are people who work at this company. I dont take any money from them, I just write up what they take on a requisition type form and then remove items they took from my stock from inventory cards. As I deduct each item, I write the unit price and then total of each item on the requisition and then do a grand total. Then I put a department account number on each requisition and then send the totaled requisitions to our Corporate office. They take care of it from that point on. So basically, I have my items in stock, I have my "customers", and then I have my suppliers, the companies that I buy my stock from. I just dont know where to start with this. Can you help me figure out how to make tables link together or whatever it is I need to do to make Access work for me. Thanks.

 

[Darrylles]

Hi Sandie,

You've basically answered your own question of where to start.

You have Customers, Suppliers and Stock.

Seems to me that at the very least - you need 3 tables: tblCustomer, tblSupplier and tblStock.

Do a little research into 'normalization' before starting this though.
E.g. both Customers and Suppliers will have a 'Town' field. A few customers will be in the same Town as other Customers and also other Suppliers - why store the same value (that can be mispelled) - hundreds of times?
You don't, you create a tblTown table where the town is stored once and 'referred' to by both tblCustomer and tblSupplier.

This is the starting point, 3 tables. I could go deeper, but this isn't the place to learn ALL about database analysis/design/development, application design/development etc. etc. etc. in one sitting.

Regards,

Darrylle

"Never argue with an idiot, he'll bring you down to his level - then beat you with experience." darrylles@totalise.co.uk

 

[olgas74]

Hi,Sandie
Try these book's: "Step By Step, Microsoft Access 2000" this will get you started, and Roger Jennings "Special Edition Using Access 2002". They were very helpfull to me!
Olgas74

 

[sandie1253]

thanks to both of you. I already purchase the step by step access 2000 book, I'm still baffled tho. I guess I know the concept of the databases. I just need to get more knowledge on how to create it. I am taking a Level 2 class next week, maybe that will help me more.

 

[sandie1253]

define "town" fields please.

 

[Darrylles]

Hi Sandie,

Both Customers and Suppliers MAY have addresses in your database (maybe not, I was just using a logical example).

In that case, each and every Customer and Supplier will be located in a Village, Town or City.

May be better to call the field 'City' - then you'd store Village name or Town name or City name in the 'City' field.

Regards,

Darrylle "Never argue with an idiot, he'll bring you down to his level - then beat you with experience." darrylles@totalise.co.uk

 

[lespaul]

As everyone has mentioned, the 3 tables will be a good start. Once you have determined the information needed for these base tables, you will need to have tables that store how they interact. You will probably need an 'CustomerOrders' that tracks what each customer requistioned and a SupplierOrders that tracks what you have ordered from the supplier.

Do a search for 'Database Normalization' for more information on how to design a database. The biggest thing about normalization is the idea of storing data in ONE AND ONLY ONE PLACE. That way it only has to be updated in one place.

The best example I can think of is the dog trainer database.
You have a dog and you know it's breed, age, sex, and all the tricks it can do: sit up, beg, roll over. So you could have ONE table to store all this information, but it makes more sense to have a table of all possible dog tricks:

TrickTable
TrickID
TrickDescription

DogTable
DogID
Name
Sex
Breed
DOB

Then have a table that combines the two:
DogTricks
DogID
TrickID

Then if you need to know the name of all the tricks the dog can do you would write an SQL that joins the two tables on the TrickID and you can get the TrickDescription:

SELECT Name, TrickDescription from DogTable INNER JOIN TRICKTABLE on DOGTABLE.TRICKID = TRICKTABLE.TRICKID
WHERE DOGID = 1

the results would return
Rover Rollover
Rover Fetch
Rover Play Dead

This way if you wanted to change the trick description you wouldn't have to go into each dog's record and find Beg to replace it with Sit Up. This also standardizes the types of tricks. If anyone could enter tricks directly into the dog record who is to say that they would use the same terminology? So if you wanted to do a search for all dogs who could play dead, you would have to know what every user enters for 'Play Dead' and search for all variations.

Hope this makes sense and helps in some way.

Leslie
landrews@metrocourt.state.nm.us

There are 10 types of people in the world -
those who understand binary
and
those who don't!