Accees beginer and demand from many companies

[Vrbic00]

Hello,
I'm new here and in Access world also I've worked long time in excel but I'd like move on. I saw few introduction videos and came through few pages about beginning of Access. I've tried some basic functions as importing, work with tables, forms etc... I believe that to try create something is the best how to learn something but when I tried it I still don't know many. Especially how to do easy things from excel as a maximum on line or show only "nonduplicit" lines etc.My quest is to manage a demand. I have demands from 10 companies. Every demand has columns: ID num of product, name, amount, prize, expiration, note and would like to sort it in several ways:
1) Put all together and from other database add some column about general properties of product and extra column which says a ranking by a prize (products can be duplicit so if 10 companies ask for same product add "1" to company with best prize, "2" in second best prize atc., if only one company ask, add "1")
2) Make a list of all demanded products without duplicities and with highest prize and add the lowest expiration (doesn't matter on company or prize).


Could anybody advise me how to proceed? I will learn from it and I hope that next issues handle by myself.

Thank you all.

[CJ_London]

Main thing to be aware of is that access is not excel. Access has three parts, data (tables), data manipulation (queries) and presentation (forms and reports). Excel basically has one which encompasses all three.

Because of this Excel tends to show data 'short and wide' - which is the equivalent of a query and/or form or report. Whereas access stores data 'tall and thin' in tables. Excel shows calculations as part of its data, access uses queries or forms/reports.

So the first thing to learn is to unlearn the excel way and learn the access way with regards data storage - this way is called 'normalisation' and in essence the objective is to store data only once. For example in access you would store customer details in a customer table, one record per customer. In excel, a customers name will be repeated many times in a worksheet. And that customer table may actually have further related tables for things like multiple addresses and multiple contact details. And they in turn might have other tables to store multiple methods of contact - landline, email, mobile, website etc. All depends on what you are wanting to store. Using the last example, you might have separate fields in the contact table so don't need a contact method table. But you only need to have one instance of another method of contact, or multiple landline options and you will need the contact method table, otherwise your data is potentially compromised (you wouldn't be able to auto dial etc). If required, you would use queries/forms or reports to create a 'view' similar to what you see in excel.

You also need to learn about relationships - typically 'one to many' and 'many to many'. Relationships define how tables relate to each other. For example an order from a company is a one to many relationship between the company record and the order (one company can have many orders, but an order can only have one company). However the products ordered are in a many to many relationship with the order - one order can have many products and one product can have many orders. For this you need a separate table to join the two.

The point is, if you don't get your tables and relationships defined correctly, everything following becomes a nightmare to manage.

So to answer your questions, they are all possible, but without the correct design will become difficult or impossible to do. Sit down with a piece of paper and sketch out what tables you need, what needs to go into each table and how they are related to each other. It is not difficult, but only you can do it since you know your business.

Whilst doing this, think about eh following, they may also have an 'input' into your table design:
1. where the data comes from and how it is distributed - how is data brought in (import from other systems, manually input etc), how is data sent out (users use access to generate their own reports, emails are sent periodically/on request with a .pdf or excel attachment)
2. what business rules apply - can components change (financial years, tax rates, holiday periods etc) which require tables to manage or is it fixed in stone so can be hard coded
3. the user experience - how quickly and easily can the get to where they need to be to do whatever it is they need to do and how easily/quickly can they do it

[Micron]

All good advice, to which I would add the following as HIGHLY recommended reading. It will save you headaches in the long run.
http://www.utteraccess.com/wiki/index.php/Autonumbers
http://access.mvps.org/access/general/gen0025.htm
http://allenbrowne.com/AppIssueBadWord.html
http://access.mvps.org/access/general/gen0012.htm
http://www.access-programmers.co.uk/...d.php?t=225837
and reference sites to bookmark
http://www.fmsinc.com/free/newtips/primarykey.asp (note web page nav pane on left)
http://www.techonthenet.com/access/functions/
http://allenbrowne.com/tips.html

[Vrbic00]

Thank you both of you. I'm going to start