Access Filtering

45 pts.
Tags:
Access 2007
columns
Design
filter
formulas
Search
SORT
totals functions
I have a database which has multiple Product fields ( Prod Name, Prod Name 1, Prod Name 2, Prod Name 4). It is set up this way because one customer can order more than one product up to about four. The products are listed by number and the users can enter a product number into each of the fields, so it is possible to have one product number in any of the fields with different orders. EX. A customer can order 1088 from Prod Name 1 another customer can order the same product but it might be in Prod Name 4 depending on the order they entered it. Is it possible to make Access produce a report that can grab all of the same product numbers form each of the different fields into one report and to a cumulative total for the number ordered at the same time? I tried to figure out a way to do it but so far no luck. Any help is greatly appreciated. Thank you in advance.

Software/Hardware used:
Access 2007

Answer Wiki

Thanks. We'll let you know when a new response is added.

Build a query that does a ‘union’ on those fields. in the report add a group by the product name (in example it would p1name) and on the footer add a total for that item.
i.e. =Sum()
You could always a where clause if you want to create the report on 1 product.

An example of union is this:
SELECT p1name,p1qty
FROM Table1
union
SELECT p2name,p2qty
FROM Table1
union
SELECT p3name,p3qty
FROM Table1
UNION SELECT p4name,p4qty
FROM Table1
ORDER BY p1name;

Discuss This Question: 9  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • philpl1jb
    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
    49,550 pointsBadges:
    report
  • Mwpv1
    Thank you Phil I appreciate the feedback. I tried to break up the table into related many tables but then nothing seems to work.
    45 pointsBadges:
    report
  • carlosdl
    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.
    68,495 pointsBadges:
    report
  • Mwpv1
    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.
    45 pointsBadges:
    report
  • carlosdl
    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.
    68,495 pointsBadges:
    report
  • Mwpv1
    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?
    45 pointsBadges:
    report
  • Mwpv1
    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.
    45 pointsBadges:
    report
  • carlosdl
    "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.
    68,495 pointsBadges:
    report
  • orangehat
    my apologies. I didn't see the below answer when I entered by answer. I didn't mean to duplicate an answer.
    1,525 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following