Need help running a query

[Claymationator]

I looked through the forum but wasn't able to find anything for the question that I have...

I have a database that I created that contains customer information for multiple orders. The table that I have has the customer information (Name, address, phone, etc) and then information for up to 5 orders (date, time, comments, what was ordered, quantity, etc).

The form that I have created to enter in the information has a total of 6 tabs (one for customer info, and one tab for each of the 5 orders). I am trying to run a query that will return information for all of the 5 orders at once. Some that I have tried is to find out what brand of product was ordered within a certain week, how many orders were placed overall within a certain week, among others. When I do this I can get Access to return the query if I do it for only one of the orders, but when I try and apply the same criteria to the other orders at the same time it returns nothing.

Can anyone help me with this? If you need more information please let me know and I can go into more detail.

 

[dsdavids]

If I am understanding you, you have the order info in the same table as customer info. In order to get what you want with that setup you'd have to bend over backwards, maybe add the table five times to the query and use a bunch of "or" this that and the next order criteria. I'm not saying to go there as I wouldn't even try it myself.
What you need to do is to seperate the orders in their own table with a foreign key to link them. Then there will be as many orders as you like for as many customers as you have and the form you want would be a snap.

 

[Claymationator]

That makes sense, I think that I will have to give that a try. I have the table already created so I think that I will go in and separate it into the different tables so that I can do the query.

Thanks for the post. I appreciate it. If I have questions I am sure that you will hear from me again.

 

[giacomo]

What happens when a customer wishes to place a sixth order? will you turn them away saying "sorry but my database can only handle 5 orders"

The reason you're having trouble is because of your database design. You need a normalized table structure. Please take a look at the links in my signature, there is a lot of good information about why you should normalize and how to get there.

hth,
Giacomo

 

[Claymationator]

Thanks for the post. I guess that my only excuse is that I am pretty new to Access and am teaching myself how to use it...so I am in that learning curve.

I am in the process of re-creating the database so that it will be easier to use as we acquire more customers. Is there a limit to how many tables can be added in Access? From what I understand with the posts to my first request, it is easier and better to have a new table for each order.

 

[giacomo]

Hey Claymationator,

This is a common issue we see here. If you read some of those links about database normalization in my signature I think you'll see that a table for each order is not the way to go. You need an order table and each order will be a row in that table.

hth,
Giacomo

 

[MyBoo]

NO! You're last statement is not true!

You should keep LIKE data in seperate tables and set relatioships to the different tables so that data in one table is "linked" to associated data in another table(s).

Your Customer table is a good example. You SHOULDN'T have multiple entries (Same UNIQUE Customer) in this table, but you want to keep a record of every customer you have. Your Oders table should be linked to your Customer table via some unique key... such as a Customer#(primary key). The Orders table (one table) would contain ALL orders received from ALL customers. If you are tracking shipping, you should have an additional table for that. The Order# would be your primary Key for the Orders table and could be used to link the Shipping table (foreign key).

LIKE data is that which is meaningful to the record entered. ie... you wouldn't put a shipping invoice# in a table that contains Customer name, address, etc.

 

[Claymationator]

OK, so let me make sure that I understand this correctly before I go in and do something that I will just have to re-do later on.

What I am going to want to do is create a table for customer information, one for order information, one for shipping information etc. Then make sure that my primary key (customer #) is in all of the tables so that I can link them to create the form, queries, etc.

Am I correct in that statement? I just want to make sure that I am setting it up correctly in the first place because I don't want to have to go back and change things again.

If that is correct is it possible to just use the autonumber primary key that Access automatically assigns as the customer #. That is the way that I currently have it set up, so if that will work I will just leave it like that.

 

[MyBoo]

OK, so let me make sure that I understand this correctly before I go in and do something that I will just have to re-do later on.

What I am going to want to do is create a table for customer information, one for order information, one for shipping information etc. Then make sure that my primary key (customer #) is in all of the tables so that I can link them to create the form, queries, etc.

Am I correct in that statement? I just want to make sure that I am setting it up correctly in the first place because I don't want to have to go back and change things again.

If that is correct is it possible to just use the autonumber primary key that Access automatically assigns as the customer #. That is the way that I currently have it set up, so if that will work I will just leave it like that.

Based on the info you've provided, yes... you should have two/three tables. You can use the Autonumber for the primary key, but if you have something like a Customer#, I would use that instead. You will find that the autonumber becomes more of a pain than anything else.

For the Customer table, this would be the primary key. You would store this SAME field as the foreign key (do not set as a key field when creating the table) in the Shipping table.... this is how you set up the required one-to-many relationships between your tables. Set a primary key for the Shipping table also.

Use Tools/Relationships to define one-to-many relationship between Customer and Shipping (drag primary key from Customer table and drop on foreign key in Shipping table). If you create a Receiving table, you would do the same for it.

You should now be able to enter a Customer ONCE yet, enter hundreds of shipping requests for that one Customer.

You may find you want to edit your query also... right click on join line... select Join Properties and select option 2.

 

[Claymationator]

I appreciate all of the help with this.

I have gone in and edited the tables so that I now have one for the customer information, and one for the actual order information. The queries seem to be working great.

The next question that I have is I created a form for the customer info with a subform that contains the order information. When I go out of the design view to view it, the subform stays in a table format, instead of the design that I created. Is there a way to have the subform appear like I had set it up, or will it always appear in a table format for the data entry?

Any help is appreciated. Thanks!