A Starter's Doubt

[Aeon.Divine]

Hi there,

I've just started working in a office, and i've been requested to design a database in access in order to register any work that comes up. What i need to build is a database that includes the following tables:

- Customers
- Suppliers
- Purchase Orders

But being just improvising here, i can catch the grip of it very easily. I'm not exactly sure how am i supossed to use the tools at my reach.

- ¿ To input data to the tables, are forms the way to go ?

- ¿ what can i do, so that information about customers and suppliers are auto-completed in the "purchase order form" if i have them previously registered in the corresponding tables ?

- ¿ What can i do, so that if the information entered to the "purchase order form" is new to the "customer" and "supplier" tables, the data is registered as well for further entries ?

Im not quite sured of being handling the structure in the proper manner, so i posted this here, I hope you are able to give me a little insight on how making this work out. I would apreciate tutorials as well if what i am missing is some reading. Thanks in Advance.

 

[Mike375]

¿ To input data to the tables, are forms the way to go ?

Yes, really the only way to go

- ¿ what can i do, so that information about customers and suppliers are auto-completed in the "purchase order form" if i have them previously registered in the corresponding tables ?

That question has a whole range of answers

- ¿ What can i do, so that if the information entered to the "purchase order form" is new to the "customer" and "supplier" tables, the data is registered as well for further entries ?

Same deal.

You need to buy a book or two on Access. There is also Help on the Access toolbar but I think that has better value when you have read some books so as to get an idea of what to look for in help.

 

[ColinEssex]

Look and use most of the Northwind database on the Access CD. It has most of what you want and will save you hours of time.

Col

 

[Rabbie]

It can also be worth Googling for web-based Access Tutorials.

 

[gemma-the-husky]

the tables you quote are

- Customers
- Suppliers
- Purchase Orders

i am not sure how these relate to one another

a purchase order is issued on a supplier

so how does the customer relate to this situation. Most customer orders will relate to provision of services or materials, in very few cases will they lead immediately to a corresponding purchase order. so you need to think carefully about the design of the tables

----------
the first thing you want to do is forget about forms, presentation , interface etc and think about the data - decide every scrap of information you need to handle, and how these bits of data are related - this is called normalization and is the SINGLE most important thing in database design. Most importantly consider the rare exceptions - if you normally do x, but sometimes (howeever rarely) do y, then your data structure MUST allow for y. Otherwise you will complete your database, present it, and someone will immediately say "what about ... ? " - and fixing stuff down the line is 10 times harder than getting it right first time

[EDIT - and the other thing is they wont tell you things you ought ot know! the users will take things for granted, and assume you know it - so you need to understand the business processes and practices better than they do - or waste time developing an agreed spec of what you are doing, and get agreement before starting any work - and finally there is a lot of serendipity involved in access - you can do things with forms etc, in a variety of ways, and because it is a MS office application, you can in a lot of cases cut and paste, etc - your users will find ways to do things you never dreamed of - I know mine have!]

once you have designed an accurate data structure, you will then find it a lot easier to manage your data, and design your interface and application

 

[Rabbie]

Also make sure you understand the importance of Data Normalisation. Getting that right will help you when they want to add extra functionality. If the data isnt normalised you will have so many problems.

 

[Aeon.Divine]

Thanks for the insight, im think i'm a little more into it now, i've started editing over NorthWind as Col suggested and its working pretty good. Is there any good online tutorial about inputing data in a form using comboboxes to input preexisting parameters already stored in other tables ?

 

[Rabbie]

Thanks for the insight, im think i'm a little more into it now, i've started editing over NorthWind as Col suggested and its working pretty good. Is there any good online tutorial about inputing data in a form using comboboxes to input preexisting parameters already stored in other tables ?

Try looking through the sample databases on this forum. You may find something similar to what you want to do

 

[boblarson]

Thanks for the insight, im think i'm a little more into it now, i've started editing over NorthWind as Col suggested and its working pretty good. Is there any good online tutorial about inputing data in a form using comboboxes to input preexisting parameters already stored in other tables ?

If you want some good tutorials go here:

http://www.functionx.com/

They have GOOD Access tutorials, as well as a lot more, and they are all free.

 

[gemma-the-husky]

Thanks for the insight, im think i'm a little more into it now, i've started editing over NorthWind as Col suggested and its working pretty good. Is there any good online tutorial about inputing data in a form using comboboxes to input preexisting parameters already stored in other tables ?

seriously this is a curious question

all a combo box does is help guide you to the correct data

in your case, you have a purchase order, drawn from supplierA - now generally, supplierA will have a reference id value say supplierno 1234. so in your order table you want to store the vlaue 1234 NOT the actual name of SupplierA {note that you could store the name, but thats not the best way to do it} Now all a combo box does is ALLOW you to pick the name of SupplierA, but to ACTUALLY record the value 1234.

The way you phrase the question seems to mean that you dont quite understand this - sorry if Ive got this wrong, and it does all make sense to you

 

[Aeon.Divine]

Nah, its perfectly ok, you were right, i wasnt quite understanding it the right way. The Purchase order im trying to develop has information both on who am i purchasing supplies and for whom am i purchasing them. Thats why i need both client and supplier tables to relate with the purchase order form.

But maybe i should beggin relating only the client table to the purchase order table form.

What i need to understand to get this working is how do i save the user the pain of rewriting all the client's ( Who needs me to purchase some stuff to my supplier. ) information. I need my form to autoload the clients information into itself when the client's name is entered manualy.

I supposed there was some way to configure a combobox so that selecting the users name there i could trigger the other fields autoload. But it seems i was wrong, all i can do is to save the id value into another field. Maybe theres a simple VB code that could help me, or some way to use macros, but this should be easier, theres no tool that helps me to configure the form so it can autoload the other tables data ?

 

[gemma-the-husky]

you possibly still dont need to link the purchase order to the client table/sales account. This seems wrong in principle.

what you really need is to have a sales order linked to the sales account, and then link the purchase order to the sales order (if thats the way it works) - mind you not EVERY purchase order will necessarily be directly linked to a sales order, will it. presumably some purchase orders are just issued without having a client. This is obviously a fundamental part of your business model, and you will understand it better than anyone else.

Its just that in mormalization terms that even though you raise a purchase order in response to a sales order, linking the two may be awkward - what if a purchase order is made in response to several sales orders - or alterntively, what if a single sales order generates multiple purchase orders.

As I say, you really need to carefully consider the data, and get it to a reasonable state before you start coding/designing forms etc

 

[Aeon.Divine]

I get it, i should consider carefully any secondary possibility so that my model needs no secondary modifications, but even after doing so, my doubt remains, how can i do so that one form will autocomplete loading data out of a secondary table when i input a field that matches a registered client ?

 

[gemma-the-husky]

it depends what you are trying to display really -

if tableA and tableB are realted in some way, then that relationship should be stored in the depenent table.ie if you have a supplier table, and a supplier order table, you store the supplier reference IN the supplier order table (ie the foreign key)

so in terms of a form, if you display a form based on the supplier order table, the only thing you need to know about the supplier is the foreign key. Now everything we know about the supplier is uniquely based on that foreign key (eg supplier name. contact, address, phone number)

so this data can be looked up once the supplier has been selected - so is this what you mean about getting the details to autocomplete?, or are you thinking of something else

 

[Aeon.Divine]

Nah, that was exactly what i was thinking.

---

Well, this post was never completed, still you helped me out a lot. Ill add to your rep.