Your basic design causes your difficulty. Table design should be normalized.
But no problem is unsolvable

If you wanted a list of all Cust – Prod, assuming that each product field has a quantity field would look like this

Select Cust, Prod1 as Prod, Qyt1 as Qty from myfile where Prod1 <> ”
Union
Select Cust, Prod2 as Prod, Qty2 as Qty from myfile where Prod2 <> ”
Union
Select Cust, Prod3 as Prod, Qty3 as Qty from myfile where Prod3 <> ”
Union
Select Cust, Prod4 as Prod, Qty4 as Qty from myfile where Prod4 <> ”

To get a total by product would look like this
Select Prod, Sum(Qty) from
(
Select Cust, Prod1 as Prod, Qyt1 as Qty from myfile where Prod1 <> ”
Union
Select Cust, Prod2 as Prod, Qty2 as Qty from myfile where Prod2 <> ”
Union
Select Cust, Prod3 as Prod, Qty3 as Qty from myfile where Prod3 <> ”
Union
Select Cust, Prod4 as Prod, Qty4 as Qty from myfile where Prod4 <> ”
)
Group by Prod

—-
Phil

Thank you Phil I appreciate the feedback. I tried to break up the table into related many tables but then nothing seems to work.

As stated by Phil, your design needs to be corrected. That is really important.

Of course, your queries and applications will need changes after changing the desing, but doing so will save you from many problems in the future.

If you need help in redesigning your tables, let us know, and post your current design.

Thank you for the help offer I can sure use it. This is my first design just FYI. The fields in the database table are as follows: Profile_date, DepartmentName, Distributor(yes or no), Bill to Name, Bill to Address, Bill to City, Bill to State, Bill to City, Bill to Postal Code, Billing Phone, Billing Fax, Billing Email. Contact Name, Ship to Taxable/non taxable(yes or no), Ship to Name, Ship to Address, Ship to City, Ship to State, Ship to Postal Code, Customer Industry (chemical, mining etc), Customer Sub industry, Category, Customer Assay, Credit Amount, Payment Terms, Pricing Mechanism (market, X-1, CMai), Price Change Notice Days, Pricing Calculation formula (market, X-1 etc not real formulas), Contract Start Date, Contract End Date, COA Requirements (yes or no), Parameter Specs(which is a memo field),Product Name, Product Name 2, Product Name 3, Product Name 4, Shipping mode (truck, rail etc) IncoTerms ( acronyms like CPJ, ECW, ETC), Price, Freight, Special Instructions (memo field) Bill of Lading (memo field), Sales Persons First Name these are all the fields I have in one table currently. I had them in separate tables but then could not search or query or anything. I had a billing table, a shipping table, a customer table, and an orders table. The Billing name can either be the same or different from the shipping name. A customer can order one product or many so the owner wants four fields to they can see four products at once, which I thought was not a good way to go. That makes it difficult to query if not impossible, I tried using accept multiple values in one field but that made matters worse. The Parameter Specs, BIll of Lading are memo fields describing items. The Price Calculation Formual field is not a real formula, just a number selections to tell sales people what formula to use. The sales people only work with the actual formulas in Excel, not in Access. When I had tables for each group or what I thought they should be I had Primary keys in the main tables and linked the others via foreign keys but nothing worked. I could not see why. My Orders Table was linked to BIlling via a foreign key called OrderNumber. I linked the Customers Table to the OrderItems table and then the Order Item table to the Orders table via a foreign key called Order Items. The fields showed one to many relationships and they looked like they were going in the right directions but again nothing worked. So now we have everything in one table and it seems to work somewhat but still not a good design. Any suggestions are more than welcome. Hope this message isn’t too long.

No problem with the message being too long, but it would have been nice to have it properly formatted, specially the fields list.

Having everything in one table is a bad idea and a bad design, even if it is the only way you have been able to make things work.

The most obvious correction would be to create an order table, and and order-detail table, having a relation between customers and orders, and between orders and their detail.

I would seriously recommend fixing the design, and then working to make the forms work (or creating new forms if needed) with that design.

Thank you Carlos for the help. So I should make the Orders table like a bridge between the Order Details and Customers? Now once I create that the relation between Customers and Orders that should be a one to many ie. one customer to many orders and order items and the orders table also a one to many relationship?

Carlos,
Sorry I forgot to ask what would be the best way to handle the Products fields? We created four product fields Product Name, Prod Name 2, Prod Name 3 and Prod Name 4. The reason they wanted that was because the data entry people were able to add the multiple products on an order if the customer ordered more than one item. However that makes building the query difficult to retrieve the data. I was thinking Products should be one field and add them in the one field. Not quite sure what the best method to handle that would be.

“Not quite sure what the best method to handle that would be.”

If a customer orders 4 products, you would have 4 rows (not columns) in the order details table.

Since obviously you don’t have much knowledge on database design, I would seriously recommend reading some basic tutorial (which you could easily find with the help of Google) before attempting to continue with this project.

my apologies. I didn’t see the below answer when I entered by answer. I didn’t mean to duplicate an answer.