Database Help

[Token]

Guys, I'm in a rite feckin dilemma. I'm pulling my hair out.

I agreed to do a Database in Access for a company that I work for, I use to be able to do a semi-decent database that could do reports, queries, enter data etc. That was back when I done Computing AS level, god help me because I can't remember hardly anything, let alone working out the structure of a good database. I really shouldn't of agreed to do this, if anyone could help me out I'd be so, so grateful.

[monkeyseemonkeydo]

Never done a database in my life... Best I can do is this or this? Top hits from Google

[Token]

Thanks for the reply Dave, I think it's slightly more complicated than the tutorials I've been reading. It's not that I don't know my way around Access as the tutorials seem to teach you, it's just I don't have the first clue where to start, what tables should be related in this scenario. How would I go about bringing up certain data when I create a query.

This is making me go mental.

There must be somebody who's doing Computing at college at the mo? Please I'd be so grateful.

[Krisboats]

Its been a while since i did an access database, but you want to start with making the tables, or maybe i should say you need to start planning them. Get a piece of paper and write down the things your going to need stored in the database. Firstly you'll need to split the main things up into different groups, eventually making a different table for each group. Things like:

*Customer details

*Order details

*Product details

*Shipping details

Make sure to try and reduce the amount of data needed. Don't create fields for things like the colour of peoples cars if its for a grocerie store as its just clutter.

Inside each table you'll need to put in all the details you need, for example with the customer details table you'll need things like customer name, address (split into a few boxes address 1, address 2 etc), phone number, fax number and so on. You'll also need to set up any input masks in the details of each field so for things like the customers date of birth you'll need to put in a numerical only input mask thats assigned in a format like DD/MM/YYYY.

Plan out your tables on a sheet of paper and work out how they're going to be joined. You'll be joining them through use of relationships so you'll need to make an identifying field for each table. Something like a customer ID number, an order number and a product number will do the job just fine. This way you can link the order details table to the customer details table through the customer number and the order details table to the product details table through the product number. I'm slightly unsure on this but i think in the order details table you need to include a field for both the customer number and the product number so you can join them up.

You also need to work out the kind of relationship, it can be one to one or one to many. Your customer can make many orders but you order can't have many customers so its one customer to many orders. Likewise the order can have many products but the products can only be assigned to one order at a time without making it overly complex. Like in this picture, the infinity symbol is to represent the "many" bit of the one to many relationship.

Taken from here

Thats basically what you need to do to get started, if you want help with the next bit i'll keep my eye on the thread for you.

[Token]

Ahhh your a absolute legend mate, I'll try an get started. The main complicated thing I'm trying to work around is this:

Oil is collected on a weekly basis from different sites, some sites wont need their oil collected on certain weeks, but what must be do able is to query for example 2 weeks ahead (maximum of 4 weeks ahead) and see which sites need collecting. Also needs to be able to print of forms for specific sites to say 'I've collected this much from you' etc. Needs to see how much oil has been collected previously from individual sites, total it an everything. Really think I've got myself in some deep shit here as I cant for the life of me remember anything other than making a simple table and a very simple form with a few backwards or forwards buttons.

[Krisboats]

Ahhh your a absolute legend mate, I'll try an get started. The main complicated thing I'm trying to work around is this:

Oil is collected on a weekly basis from different sites, some sites wont need their oil collected on certain weeks, but what must be do able is to query for example 2 weeks ahead (maximum of 4 weeks ahead) and see which sites need collecting. Also needs to be able to print of forms for specific sites to say 'I've collected this much from you' etc. Needs to see how much oil has been collected previously from individual sites, total it an everything. Really think I've got myself in some deep shit here as I cant for the life of me remember anything other than making a simple table and a very simple form with a few backwards or forwards buttons.

You could have that bit set up as two seperate fields, one being "oil collection needed" and one being "oil collection scheduled" if you want you can either do both of those as tickboxes or have one as a tick box and the "collection scheduled" as a date in the dd/mm/yyyy format... or you could even have a seperate "schedule date" field. If someones going to be entering in the details of places that need it collecting in advance it should be quite simple to build a query that lists all the sites whose scheduled date is longer than [insert number here] weeks away.

Having a print button is pretty easy too Just a simple clickable button with a macro bound to it.

[Token]

Okay thanks for your help mate, but I've decided I'm not gona bother doing this for him. I can basically understand the structure it needs to be, but have no clue of how to implement most of the rules that'll govern what can an cant happen for example (show: 'week 3' only).

Shouldn't of agreed to do it in the first place, but oh well shit happens.