Need help running a query

Claymationator

Well-known Member
Joined
Sep 26, 2006
Messages
705
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.
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

dsdavids

New Member
Joined
Oct 5, 2006
Messages
13
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

Well-known Member
Joined
Sep 26, 2006
Messages
705
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

Well-known Member
Joined
Feb 20, 2002
Messages
1,796
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" :eek:

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

Well-known Member
Joined
Sep 26, 2006
Messages
705

ADVERTISEMENT

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

Well-known Member
Joined
Feb 20, 2002
Messages
1,796
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

Board Regular
Joined
Aug 9, 2006
Messages
217

ADVERTISEMENT

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

Well-known Member
Joined
Sep 26, 2006
Messages
705
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

Board Regular
Joined
Aug 9, 2006
Messages
217
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

Well-known Member
Joined
Sep 26, 2006
Messages
705
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!
 

Forum statistics

Threads
1,136,349
Messages
5,675,243
Members
419,556
Latest member
rdecker12

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top