Hi Guys, thanks for a geat forum. I have read myself bug-eyed and learned heaps already, but I need some specific pointers.

I'm just starting to develop an Access database application based on some Excel sheets that we have been using for years. What I have within Excel is many customers set up and each time I do a job for them I 'save as' and copy the file with a new date and file name to become next time's job.

What we have within each job is maybe a couple of hundred stock lines, and we are using the spreadsheets to calculate stock movements / usage.

What this gives me is the old job archived to refer back to and the new job ready to go. My excel structure is a seperate folder for each customer, up to maybe 60 seperate customers, and a history for each customer that can be recalled as I need to.

My question, the first of a gazillion :) , is this:

If I want to set up multiple customers, do I write a table for each customer and name it uniquely? I can see that this would mean I had a whole lot of tables and queries, is that a problem in itself?

Any help or advice appreciated.

Steve

My question, the first of a gazillion :) , is this:

If I want to set up multiple customers, do I write a table for each customer and name it uniquely? I can see that this would mean I had a whole lot of tables and queries, is that a problem in itself?


No no and .... NO !!!! :eek:

You dont go and copy your faulty excel kind of way of doing things into a database. Abandon everything you think you know about how to do this in an Excel kind of way *ASAP*

*no offense intended*
But you should really pick up a book or search online for Database design (Normalization) before you start something like this.
Dont just go pumping semi random questions onto forums hoping to ultimatly get something "thrown together" in access that may be half way of what you need and want and somewhere something semi usefull.

You will only end up producing something to prove to yourself and other what a crap product Access is if you dont do your homework first. Get basic knowledge on how databases work and PRE-DESIGN your new database prior to starting ANYTHING.
Access may look convenient to start something in quick, but if you dont know what you are doing... you can make a right mess of things.

******************

If you are still reading... let me try and address your question....
NO NO NO NO NO NO you do not make a table per customer!!!!
You make one customer table with a PRIMARY KEY (if you know what that is) and relate all your sales and stock movement in a seperate table using a FOREIGN KEY.

No offense taken, well not much, but I did search online and guess what I found?

I can see that Access and Excel are very different and am busy trying to understand those differences. I can also see that it will be a long project and I'm not in any rush, we already have a solution but I can see the flaws in it and want to improve it by having a robust database / application.

I did try and design what I want, but it's difficcult to do if you don't know how to make it happen.

I can get the calculations I need, that seems pretty straightforward, but the storing and pushing around of data is all new to me so I thought asking questions was the way to go.

Thanks for your, erm, help.

Start with what you think your table structure should look like, and post that here. We'll let you know if you need to make changes. Having that correct from the begining will make the next steps a lot easier.

Ok, at the risk of throwing myself to the wolves, this is where I have got up to.

I thought it best to take this one step at a time, and my logic was to get the reporting and data storage side working before I started trying to relate multiple customers to jobs.

The reports that I can generate from this now are quite close to what I want in terms of the consumption figures from the 'products' table.

The question really, sorry if it's 'semi-random', is what comes next? In terms of generating and storing reports for multiple jobs, setting aside the multiple customer aspect for the moment?

What should the structure be? I asked the question about multiple tables on the assumption that it was wrong, but of course if a table per job is wrong then I need to push one lot of data out in order to use that table again for another job?

Again, many thanks for help.

I can see that Access and Excel are very different and am busy trying to understand those differences. I can also see that it will be a long project and I'm not in any rush, we already have a solution but I can see the flaws in it and want to improve it by having a robust database / application. In general terms you store manipulate and display data in Excel, all in the same place. In Access you store data in tables, manipulate it in queries, display it in forms and print it in reports.

As a result, Access tables look very different from Excel spreadsheets. Data relating to an object can be held in many different tables but the use of a common ID (called a key value) joins it all together.

In general terms you store manipulate and display data in Excel, all in the same place. In Access you store data in tables, manipulate it in queries, display it in forms and print it in reports.

As a result, Access tables look very different from Excel spreadsheets. Data relating to an object can be held in many different tables but the use of a common ID (called a key value) joins it all together.

If I was just trying to manipulate and store one set of data I would be pretty much finished by now I think, I've found the queries and forms logical and the calculations work as I expect them to; it's great once you get started.

My trouble is in understanding how to deal with multiple sets of data.

I thought it best to take this one step at a time, and my logic was to get the reporting and data storage side working before I started trying to relate multiple customers to jobs.
Taking things one step at a time is important, but this way you are starting with the last step and walking backwards!

FIRST Design your base then work up from there.... You dont see contruction workers making the penthouse first, then erecting a SkyScraper below it do you??

Making reports should be the penthouse, getting your data in and relating it, making sure your data entry works etc... that is your base...


The reports that I can generate from this now are quite close to what I want in terms of the consumption figures from the 'products' table.

The question really, sorry if it's 'semi-random', is what comes next? In terms of generating and storing reports for multiple jobs, setting aside the multiple customer aspect for the moment?

What should the structure be? I asked the question about multiple tables on the assumption that it was wrong, but of course if a table per job is wrong then I need to push one lot of data out in order to use that table again for another job?


No no no, forget your Excel logic all together... Access and databases are a whole new thing... Dont work with your Excel logic...

My trouble is in understanding how to deal with multiple sets of data.
This is your main problem (with all due respect)...

OK small attempt at Database 101....

You are regarding "one customer + his related stuff" as "one set of data"... That is excel thinking.... Not database.

In a database you are thinking in terms of Data catagories if you will, entities they are called. Each entity gets its own Primary Key (PK) and is via that PK related to other Entities, this relation in another table would be called a Foreign Key (FK).

I.e.
You would have an entity (table) tblCustomer
This table customer then holds all customer data, such as Name, addres, PhoneNumber, email, CustomerID (PK), etc. BUT nothing about Orders or products...

You also need products, tblProduct
Containing stuff about products, their Name, ProductID (PK), Weight, Price, VATRate, etc.

Now a third entity would be orders, tblOrder
In an order you need to know who ordered what....
OrderNR (PK), CustomerID (FK), ProductID (FK), ProductsOrdered, Discount, etc.

Now using a query you can link (in database terms: Join) your tblOrders with tblCustomer and tblProduct to actually find out who ordered what.
Notice that in tblCustomer it will contain ALL your customers. In tblOrder it will contain ALL orders etc. No seperate stuff for one customers orders or anything... all "the same stuff" stuffed together. This is called a "relational model"

Notice:
- I have used prefixes, any table should have a prefix "tbl" in front of it to denote it is a table. Now access wont reinforce this but it will help you in the long run. I.e. You can have a table, query, form and report all called customer... But if you prefix them tblCustomer, qryCustomer etc... atleast if you are doing something in coding you know what is going on by just looking at the name.

- I have not used any spaces in any names i.e. PhoneNumber could easily be "Phone Number". In databases you dont use spaces or any special characters )_(*&^&#37;$#@!~_=-+\/?><., etc.... Sure you use them on reports and forms, anything visible to the user, but NOT in the database itself!!!

I hope all that is a bit more clear than mud...

Ok, setting aside my Excel logic and trying to understand databases, I will try and explain what I'm doing.

I have many customers, my project is to report on product movements through their businesses. Their products, will be often times unique to them, either in name or cost/retail price. I'm not moving the same product through different customers, I'm moving completely different products through completely separate customers.



I hear what you are saying about one table with all the products in it, but that table could then very easily run into tens of thousands of lines, 60 x customers with 300 x product lines = 18'000 products. Is 18'000 rows a big table or nothing to worry about?

18.000.000.000 would be something to worry about, 18.000 is nothing. I have tables in Access going into the millions.
Again 18.000, may seem a lot from an excel Point of View with the limit at 65.000 that is roughly 30&#37; allready used.
The one thing you are concerned about with Access is total file size. Your access MDB cannot go over 2 gigabites or it will stop working. There are ways around that... one of which is to "compact" your DB on a regular basis, you can find this compact when in the main database screen in the menu: Tools>Database Utilities>Compact.

Also if you have customers selling the same products you can alter your table structure.
tblCustomer>tblProducts>tblPrices

The prices would then depend on 2 FKs, that of the customer and product. I doubt tho that this will be a good thing, but certainly worth considering if Prices indeed are dependant upon both Customer and product. This all comes down to Normalization (which I mentioned earlier) and how you do this.

Normalization is the process where you first list all your "needed information" and devide that up into "data sets" i.e. CustomerName into Customer and ProductName into Product.

I looked up 'normalization' when you first posted, so again am trying to understand terms that are new to me.

If a product table of that size is not an issue then I can see that one x product table would work; that's one issue cleared up.

As far as the actual DB size goes, I don't think I will ever get close to 2gb so that should not be an issue.

If you're not going to normalize you might as well leave it in Excel. That's what it's for. Manipulating flat data.

If you're going to do it in Access, you should probably heed Mailman's advice as he's been doing this for quite a while.

What I'm saying is, one product table is probably the wrong approach (I'm not really familiar with the thread). I yield to Mailman's knowledge of your needs.

It probably wouldn't hurt to read a good book on relational database theory.

If you're not going to normalize you might as well leave it in Excel. That's what it's for. Manipulating flat data.

If you're going to do it in Access, you should probably heed Mailman's advice as he's been doing this for quite a while.

What I'm saying is, one product table is probably the wrong approach (I'm not really familiar with the thread). I yield to Mailman's knowledge of your needs.

It probably wouldn't hurt to read a good book on relational database theory.

I fully intend to take all advice offered, so normalization will happen as soon as I understand it well enough.

The advice I have so far is for one product table, but I'm sat down here with a piece of paper and a sketch of what I think it should look like and can't for the life of me understand how that would work.

I do take the point about reading a book but, gee, those 'techy' books always seem to be written in Greek to me. I found the tutorials on the microsoft website very helpful and they did get me started, short and easy to follow lessons seem to work best for me.

Normalization is basicaly going from a list of columns to seperate datasets where you do not (or as less as possible) duplicate data.

I think I gave you an example of more or less how normalization works. It is tough to tell it over the forum how to do it... And probably there are better how-to's out there if you google a little bit.

Sorry to sound archaic, Steve, but I would recommend that you shouldn't be trying to design your system at all just yet.

Learn to drive a car - a small compact one first, probably, then move on to a four-wheel drive, then maybe a small truck - then you might be ready to cross the Sahara in a Range Rover. So make a strenuous effort to forget about what you want to do eventually. Learn to build a small, simple, downright pathetic little application in Access, then build on it, add to it, get comfortable with it before you leap into the abyss that is designing and building a 'real' Access application.

Here's my first rule for starting the design process: tables first, then queries. Only when you've got them sorted are you allowed to start thinking about forms and reports.

And no-one's mentioned sample applications - they are SO useful when you're learning. I'll bet Northwind should take the credit for more expertise than most books!

Here's my first rule for starting the design process: tables first, then queries. Only when you've got them sorted are you allowed to start thinking about forms and reports.

Al of which sounds a lot like Post #8 (http://www.access-programmers.co.uk/forums/showpost.php?p=726448&postcount=8) I made in this thread...

You see, I thought that this was where I was at.

I have thought it through, sketched out what I am trying to achieve and not duplicated any bits of information.

I have the tables that I believe I need; customers, jobs (sites), products, categories and sizes. I think that possibly the size table is surplus to requirements but I do need that distinction, because products come in more than one size.

So I have the tables and no duplication, or I think I do.

I have queries running from those tables and can make reports from those queries. I have forms entering data into those tables.

I don't doubt that there are things that I'm missing and I can see the need to learn, that's why I'm here asking what appear to be pretty dumb questions.

Well if you have your design and build it.... Then what is your problem at this point? Or can we consider this problem Resolved?

Well if you have your design and build it.... Then what is your problem at this point? Or can we consider this problem Resolved?

Oh geez, where did I go wrong here? Did I ask the wrong question in the wrong place or what? Why so negative? Aren't forums supposed to be about sharing knowledge and solving problems?

I thought I had done the right thing and been humble enough to parade my ignorance in the hope that people who clearly know a lot more than me would help.

I realise that you must get plenty of guys coming through here who think they know it all and really know nothing, but I've not been like that and have taken all the 'buy a book' nonsense as best I can but WTF?

I didnt mean any offense at all... I was just honestly wondering...

Now that you have (to your satisfaction) redesigned and rebuild your stuff.... Do you have any problems left that need answering?

I haven't re-designed anything, nothing's changed so I still have the same un-resolved problem.

Maybe I have failed to properly ask the original question? If so, my apologies and I will try again.

I have multiple customers. What I do is calculate product usage through their business and then report that usage in various ways. We don't just do this once though, we do it once a month, so I need to be able to refer back to the data from last time.

I either need a table per customer (to my way of thinking) to store their products and to make a new table for each new customer, or I need to push that data out and store it in a format that Access can retrieve when I go back to that same customer.

If I push the data out, I suspect that a CSV file is the answer along with a data folder for each customer. My (no doubt faulty Excel based) logic tells me that a table-per-customer setup would work, but I really don't know and that's what I'm doing here asking questions.

If this were a simple product storage / usage problem it would not be a big deal, but the multiple customer aspect is the bit I don't get.

How do you calculate the Product usage?? And that data is allways within your database right?? Or is that data overwriten in the DB thus not available next month?

How do you calculate the Product usage?? And that data is allways within your database right?? Or is that data overwriten in the DB thus not available next month?

The Product Usage comes from a query on the Products table, and that data is always there yes. I've not progressed from there, but if I were to do the job again next monthe then it would just be overwritten yes.

There is nothing being moved out of the database at the moment, only reports.

What is this like sales or something?

Let me ask you this question:
If I report today for your figures over June will it be different than that what it was on July 1st?

Yes and yes. It's sales and it changes all the time. We are just taking a 'snapshot' and reporting the consumption for a given period.

What will happen is that we take last times data, move the closing stock figure into the opening stock field, add purchases and closing stock figures again and then run our reports.

Yes and yes.
OK now we are getting somewhere (sorry for beeing a pain)

So we have starting stock, Sales and Purchases and end stock.... and you want to keep this to compare the next months sales and purchases against?
Right?

Why overwrite tho? Usually you keep this kind of data inside your database and only calculate your end stock for checking purposes to fight theft and such. Why move end balance to starting balance each month?

I think in this case you should make a table that would contain these figures along with a date stamp for when they were generate :confused:

OK now we are getting somewhere (sorry for beeing a pain)

So we have starting stock, Sales and Purchases and end stock.... and you want to keep this to compare the next months sales and purchases against?
Right?

Why overwrite tho? Usually you keep this kind of data inside your database and only calculate your end stock for checking purposes to fight theft and such. Why move end balance to starting balance each month?

I think in this case you should make a table that would contain these figures along with a date stamp for when they were generate :confused:

Yes, we want to be able to compare and report month to month.

We need to keep it, not overwrite it, I'm just not able to do that. The history is important for running totals and overall yields etc. So we need to be able to recall it.

I see immediately that there is a solution there in writing a table for the history which would contain the totals needed for running reports, so that's a brilliant answer for that part of it.

The end balance to starting balance is because we do this every month, so last month's closing stock becomes this month's opening stock. We then add new purchases, count a new closing stock and run our reports again.

The complexity comes from needing to do this many times for many customers.

Well doing it many times for one customer or many times for many customers doesnt matter in a database.

As long as you in your history table add the CustomerID you can always split the history per customer.

Brilliant! So if I have a 'JobID' table that can relate to customers that JobID table will re-load the data from any job I choose?

More or less depending on your datamodel... but you still need that history table tho...

That's great, I will get busy and test that this evening and see if I can make it work. I'm a million miles away from being live with this, so it's all learning and testing at the moment.

Genuine thanks for your input.

One more question. I'm currently using Access 2003, would it be smart to upgrade to 2007 now or is there no real benefit? I'm a bit concerned about compatibility, though it's not a big deal just now.

I am still "stuck" in 2002, 2000 and for some customers even 97 :(

2007 is nothing more but a pipe dream for me at the moment, so I cannot tell you which would be better.

Well, I'm guessing that if it's good enough for you then it will be plenty good enough for me. Thanks