Open a form from a query

[RDM]

Hello there, first post here.

Recently I inherited our mailout database at work. Not knowing anything about access, and none of my colleagues knowing anything about it, has caused me no end of headaches. But I've figured a fair bit out for myself, and am now trying to streamline my processes.

My database is used primarily for printing envelopes for a mailout catalogue. It consists of a single table, holding the following information;

date added - title(mr,mrs) - name - surname - company - address 1(unit/level) - address 2(street address) - suburb - state - post code - phone number - date of last purchase - last purchase $ - qty of purchases - total purchases $.

The secondary function is recording the dollar value and date of what people on the list above are spending.

This data is entered by hand, Initially I started off using Control + F, and lots of tab keys, and a calculator. Frustrated by the inefficiency of this, I began investigating a little and discovered queries.

This has now evolved into a query, where I display enough information to verify the customer's identity so I can input my sales data faster. I've done this by displaying only relevant table information (name, surname, and street address is enough), using EXPR1: [LAST_PURCHASE]+[DOLLAR] instead of my calculator and by using [surname:] and [state:] in my design box, so I have far fewer records to flick through.

I've also greatly improved efficency by learning the keyboard shortcuts for opening, closing a window.

I've also created a form for entering new customers, what I cannot figure out how to do is if no results exist for [surname:] and [state:] open my new customer form.

If anyone can help me with this I'd be most appreciative. Also if anyone has any ideas as to how I could improve my database overall it'd be great!

 

[Sum Guy]

Generally speak (but not absolute) in this situation you would have your customers in one table and your sales in another.

When you wish to enter a sale you would use a combo or list box (the most popular method) to enter the customer info and then you would enter the sale info.

While there is nothing wrong with the method you are describing the database is going to become very large and unwieldy very quickly if you keep your customer info and your sales info in the same table.

If you split the sales and the customers into different tables you won't have any trouble entering new customers as the only thing in the customer table is the customer info (no sales info).

 

[RDM]

in that case, how do you align customer table record #1, to sales table record #1 ?

 

[capsula4]

in that case, how do you align customer table record #1, to sales table record #1 ?

First you have to create two tables in which they share a field (they have the same name)

If you have "one costumer-one sale" relationship, then the shared field should be the primary key field of both tables.

If you have "one costumer-many sales" relationship, then the shared field should be the primary key of costumers tables and a common field in sales.

Here is a sample of 1-many relationship:

To create this relationship, you have to find this option in access main toolbar: "Tools > Relationships" (im translatin it from spanish, not sure how it's in English).

Hope this helps to you.

 

[chris davies]

As the person above states, you ned two tables one for the customer and one for the sales, you need a primary key field called something like customeID (or something similar, it doesn't matter what it's called, it just hasd to be unique) for the sustomer table, set the primary key field to autonumber, that will take care of the uniqueness. In the sales table you need a foreign key, this key should be in exactly the same format as the customerID field in the customer table. You will also need a primary key for your sales table ( salesID or similar as above).

When you open the relationbships window, you need to click once on the customerID field of the customer table and drag to the customerID on the sales table. this will create a one to many relationship between the two tables. As a result, anyone of you cutomers can have multiple sales.

Sorry if this isn't crystal clear, but if you can't quite grasp this concept then you should look up what is known as "NORMALISATION" which is the theory and practice of setting up a database so that there are no duplication or redundant data within the database, otherwise your database will suffer badly or even worse get corrupted.

Do a search here for normalisation and see how you get on, any more problems and post back for help.