Need Help with Design / Schema

[DJDJDJDJ]

Hello,
I am looking for recommendation on designing a simple database.
I currently track quantities for some licenses using excel. The way my excel is setup is that there are 10 products (columns) and about 70 locations (rows). Every month (once a month), I take last month's worksheet, copy it, and update it with current quantities.
This works OK except that all my data is now in individual worksheets. It isn't very easily to perform any analysis.
I am thinking about create a simple access database for this. However, I like the easy of modifying last month's data to create new month's rather than having to type all of it all over again every month.
Typically, products and locations remain the same. Very rarely (4-5 times a year), I may add a product or location to track.

I would like the data entry method to remain similar to what I have in a spreadsheet where all of the products are listed in a column and all of the sites are listed in rows along with license quantity. I can see the whole month in a single shot. I do not want to enter quantities for various products for one site and them move to the next site or vice versa.
How would you design the tables and front end for this?
Thank you.

[DJDJDJDJ]

Here is a sample of what my spreadsheet currently looks like. This sample shows only 20 locations and 2 products. In reality, there are around 70 locations and 6-8 products.

[CJ_London]

Bear in mind that Access is not a glorified Excel. The way it stores and uses data is completely different. Many newbies make the mistake of thinking they can do things the same way as they did in Excel and get into a terrible bind.

One of the main issues is what you describe - products across the top and locations down the side. This is not a natural way of inputting data in Access, although you can display the data in this way.

For your benefit, read up on 'normalisation'.

To get your data structure right, you need a minimum of three tables with these fields as a minimum

tblProducts
ProductPK autonumber
ProductName text

tblLocations
LocationPK autonumber
LocationName text

tblLicences
LicencePK autonumber
ProductFK long (links to ProductPK)
LocationFK long (links to LocationPK)
Quantity integer
LicenceDate Date

The main table you will be updating each month is tblLicences - the other two tables will only change if you add new items

With regards input in the way you want, it can be done and I am sure is not beyond your capabilities, I have an example somewhere which I will upload when I can find it, but in my opinion, it would be easier for you at this stage to keep your excel as an input form and save the results to these tables using relatively straightforward vba

[CJ_London]

now seen your spreadsheet - you would only need to import the current column for each product - the previous quantity will already have been imported and the variance is just a calculation

[DJDJDJDJ]

Ajax,

Thank you very much for your reply.

Yes, I do agree with you and understand the difference aspect between excel and Access. I have been using access for over a decade now. However, all my databases that I have created over the years have either used single record form or a continuous form. For this one, I am not sure what method to use and how to bring the last month's data and apply it to the form fields. Input form is the most puzzling thing for me.

We have had this excel process for close to 2 years now. Every time we need some data comparisons, you have to go through all these worksheet and perform a lot of manual work as location names may have moved around on the worksheet and products may have been added etc.

I can work out normalization as well as I have done that with other access databases and SQL servers.

It would be great to see your example that will allow me to enter data in Excel, then click on a macro button or trigger a procedure that takes the information from the current worksheet and dumps it into access tables.

Thanks.

[CJ_London]

It would be great to see your example that will allow me to enter data in Excel

I don't have an example of this, I have an example of inputting directly into Access as you originally described - however having now seen your spreadsheet, I think it may not be appropriate - you have blank columns and 'redundant' data in the previous month and variance columns which makes for a very wide form. given the max width of a form is 560mm and you have circa 60 columns, each column would only be about 10mm wide which is not enough for your purposes.

With regards importing from excel into access there are plenty of examples out there I don't have one per se- probably the easiest is to create a linked table to your excel worksheet and create some import queries - either one for each product or one built in a vba loop to step through the columns.

either way, your column heading is in row 1 and your query would need to ignore row 2 because it is not a value for importing.

Your query would be something like to import Product1

INSERT INTO tblLicences (ProductFK, LocationFK, Quantity, LicenseDate)
SELECT (SELECT TOP 1 ProductPK FROM tblProducts WHERE ProductName='Product1'), (SELECT TOP 1 LocationPK FROM tblLocations WHERE LocationName=exlTbl.Site), Product1, Date()
FROM exlTbl
WHERE isnumeric(Product1)

Note, you will need to modify your spreadsheet to put 'Site' in row 1 and I can't see a date on the spreadsheet so I've just used the function for today - Date(), you may want to input this as a parameter or reference a value on a form.

Your locations table could use the #1,2,3 etc as the locationPK if this is what it means, then you could just refer to this column, but again you would need to put a column header in the top row.

Finally, before running this query, you will need to ensure that you have fully populated tblProducts and tblLocations - you could use a query similar to the above for locations but would need to manually input the products

[DJDJDJDJ]

Hi Ajax,

I still wouldn't mind looking at the example you were talking about.

I use these columns in excel because it makes it easy to see everything. However, in Access I can simply populate the new record fields with previous data and then modify them for the current month. I can then easily create the variance data etc. in a report as needed. This way I will not have any redundant data in the tables themselves.

Spreadsheet does not show any dates because it is for one particular month. There is one for each month.

1,2,3 column is simply a counter. Site names are used as identifiers and I could use them as PK.

Since I do not want to hard code the product or site names in the query, I am thinking I will have to iterate through the product table as well as site table and then create some type of a ad-hoc matrix grid form with all the fields required. Am I on the correct thinking train?

Thanks.

[CJ_London]

couldn't find my reference copy but this is where I got it from

http://www.baldyweb.com/ChrisOSamples.htm

it's about halfway down

[rpeare]

Simple example just for funsies.

DJDJDJDJ.zip

didn't do a whole ton of error checking or but the basic principle is there for entering a matrix into a normalized structure.

[DJDJDJDJ]

couldn't find my reference copy but this is where I got it from

http://www.baldyweb.com/ChrisOSamples.htm

it's about halfway down

Thank you.

[DJDJDJDJ]

Thank you. I'll check it out. Much appreciated.