 You don't need to be an 'investor' to invest in Singletrack: 6 days left: 95% of target - Find out more
I'm self teaching MS access (it's the only software package I have access to).
I want to set a database up of errrmm, Beer stock say, because I've been given a stock control thing with 20 odd seperate excel spreadsheets pretty much all saying the same thing and it's screaming out for a database..
I want to be able to know from the 80 or so different beers I have which batch is in date at any time, and when i'm down to a coupla weeks before it needs to be checked for freshness so I can re-check it.
I also need to be able to keep records for each batch of each type of beer (as the brewed by details etc are different), but I need to be able to say to the barman, use beer type A with it automatically updating which batch of A to use.
Another problem is that each batch can have completely new details once checked for freshness.
I think I need a table for the beer types and specs and a table for the details of each batch of beer. I've found with one table that I'm repeating a lot of stuff for the different batches of each beer and struggling to manually ue the identifier number for type of beer but it being able to descriminate to the in date batch.
Am i thinking along the right lines with 2 tables?
Am i thinking along the right lines with 2 tables?
Depends how much you want to normalise the data, but my gut feeling is that you are going to need more than 2 tables.
You need to do a bit of database design.
Have a shufty here as a starter for ten.
http://www.tekstenuitleg.net/en/articles/software/database-design-tutorial/intro.html
Boyce-Codd is your friend/nemesis depending on how you look at things. Have [i]fun[/i] 
Fun... I know that the data should be on a database, but I'm still tempted at the moment to !bang a lot of excel spreadsheets together.
you could start by modelling it in Excel if you're familiar with that. Will give you an idea of where you need to add additional tables to allow you to normalise the data.
Ideally you need to set up master data tables so that you never repeat the data, you just reference it - eg a table to hold the different beers and any information specific to them then a different one to hold each batch and that information.
Maybe then a table to hold the freshness checks which are referenced to the batch.
Sounds like three tables so far: beer types, batches, and details per batch (as clubber said)
But, if you haven't done so already, you might want to get yourself a basic book on Access e.g. Access for Dummies. You will get through it in no time and if you have never used Access before it will make a huge difference to how easy it is to do.
Also, if you haven't done so already, there is training on Access 2010 here
http://office.microsoft.com/en-us/access-help/CH010372755.aspx
Have fun and ask away
Right - I think i can see that - do I need a fourth for how many bottles of beer for each type and batch, and when to re-order new stuff - although that is standard for type so no I don't.
I presume I then set up a query that will tell me that batch A, freshness check 4 is the current batch to use and needs to be checked again in 6 weeks.
I'm not sure you need one for how many bottles of each beer and type - that sounds like something you either calculate OR is part of the info for each batch. So if you have a table of bottle types and volume for each type and you know the total volume of each batch and type of bottle then you can calculate the number of bottles you need.
and yes, you need to make a query to calculate that. It often helps to work out [u]all[/u] of the queries that you want to do in advance and how to calculate them as that often tells you what you need in the tables and what tables you need
good luck