Filter by form help

[kgriff]

Hi All. I'm new here, so I hope you don't mind if I barge right in.
I've searched but can't really find what I'm after.

Access 2007.
I'm making a simple order database. I have two primary tables. For simplicity sake, here are the main fields

Table 1 - Orders
>Order # (Primary Key)
>Supplier
>Ordered By
>Order Date

Table 2 - Order Line Items
>Order #


>Item Number
>Manufacturer
>Model #
>Description
>Quantity
>Cost each

The tables are linked in a one to many relationship on the Order # field.

I have a form that displays data from the Orders table with a subform (displayed as a datasheet) that displays data from Order Line Items.

I want to be able to apply a filter by form on either the main form or subform. So far, here's what happens:
Filter By Form on main form works fine.
Filter By Form on subform does not work. It appears as though the subform is filtered independent of the main form. Is there a way around this?

For instance I have two suppliers: ABC and XYZ
I have 5 orders from each supplier. From supplier XYZ I ordered an item with the description "Washer". I want to be able to filter by form, on the subform, for "Washer". As of now, if I happen to have supplier XYZ displayed on the main form, this filter works. If ABC is displayed, the filter doesn't work.
Since these are linked tables, it seems like this should work, but it doesn't.

By the way, I'm using the built in Filter By Form function. Do I have to build my own filter by form to make this work? If so, is there a relatively easy way to build a filter by form? The only way I've ever figured out to build a filter by form is to use a rather lengthy query containing all fields to be filtered with criteria for each field to cover null or not null values. Also, I've never built this type of filter by form for linked tables, so I don't even know if it would work.

[Access_Blaster]

Hi kgriff,

You have a primary key in table 1, where is the Primary key for table two?
Order # in the second table is a Foreign key.

Table 1 - Orders
>Order # (Primary Key)
>Supplier
>Ordered By
>Order Date


Table 2 - Order Line Items
>Order #
>Item Number
>Manufacturer
>Model #
>Description
>Quantity
>Cost each


You may want to look closer at your data model. Here is a example of a orders data model. http://www.databaseanswers.org/data_...ders/index.htm



Richard

[kgriff]

Richard,
Thanks for the example reference. I've browsed around that website some and it looks like there's a lot of good information there.

Even if I add a primary key in Table 2, Order # in Table 2 will still be a foreign key. Because there will be duplicates I cannot make Order # the primary. I would have to add another field to Table 2 to be the primary key but it will not be linked to Table 1.
For instance, I could add a primary key to Table 2 called Order_Line, but it would not be linked to Table 1, since Table 1 has only 1 record for each order, whereas Table 2 can have many records for each order.

Using the data model you linked as an example, what I'm trying to accomplish would be:
Form with record source Customer_Orders
Sub form with record source Customer_Orders_Products
Filter by form so that I could filter for any data in any field. i.e. I may want to filter Customer_ID on the main form so that I can see all orders by a particular customer. I may want to filter by Product_ID on the sub form so I can see all orders a particular product, and who placed the orders.

From what I've seen so far, the first filter, for Customer_ID, would work, but the sub form filter for Product_ID does not work.

Or am I missing something really obvious?

Kevin

[Access_Blaster]

Kevin,

Each table you set up should have a primary key. Each child table has a link key which is the Foreign key. The parent table should be the ONE side and the child table is on the many side. Keep looking at the example it will become more clear.


Richard

[kgriff]

Not to appear dense, but I'm still not seeing it. I understand the database structure, but the query by form still doesn't work.
I've recreated the structure shown in the example.
In table view, I open the Customers table. I can expand each linked table and see the data contained in each. However, when I use the built in Access query by form function on any of the child tables, it does not work correctly.

For instance, say I have 4 customers with customer_id 1, 2, 3, 4. (Call them John, Jack, Jim, and Joe)
Each customer has two orders.
Each order has two line items.
Each order is shown as a record in Customer_Orders.
Each line item is shown as a separate record in Customer_Orders_Products.

I have expanded the table view in order to see the records in Customer_Orders_Products. I want to filter by form in Customer_Orders_Products for Product_ID=1.
The records returned from the filter are only those records with Product_ID=1 in the specific "tree" that has the focus when I turn on the filter by form function. (I have expanded John, both orders, Jack, both orders, Jim, both orders, and Joe and both orders. I click into the Customer_Orders_Products in the tree under John, customer_id 1. When I perform the filter by form the returned records are only those where customer_id=1 AND Product_ID=1)
What I want to be able to do is filter for Product_ID=1 and return ALL records with product_ID=1, regardless of the data in parent forms. In other words, I want to know, in total, how many of product_ID=1 have been ordered.

The reason I'm wanting to use filter by form is that I may want to filter by more than one value.

Again, perhaps I'm asking stupid questions, but I've never before used filter by form in this manner, so I'm not real sure what I'm doing, or even if it will work.

Edit: I just did some additional fussing with filter by form. I appears that whatever filter by form I apply on child tables, it only returns records from the first record in the parent table. I can make this work with an advanced filter, but this will not work for my application. I'm trying to make this database so anyone can use it, and you have to have at least a basic understanding of queries to use the advanced filter.

[Access_Blaster]

Hi Kevin,

are you using a form and a combo box for your QBF ? It kinda sounds like your working directly in the tables.

[Access_Blaster]

Please post your new table(s) design and I will see if I can help with your QBF.

Richard

[kgriff]

I've tried the query by form function both on a form with subform, and on the table.

I've attached, I think, a stripped down partial database. Understand that it is not complete, and the table structure is not great. The tables in question are Orders and Order_Line. Order is the parent, Order_Line is the child.
There are three records in Orders, and six records in Order_Line. Each record in Orders has two child records in Order_Line, with duplicates.
On the form Orders I want to be able to filter by form on either the main form or the sub form. If I filter field Category for value "Widget1" for example, I should get two records returned, each child to a different parent.

In some additional experimenting just now, though, what I get is two records returned that match the search criteria, but all parent records are returned, when I should only get two of the parent records.

I hope I'm making myself clear. What I'm after is basically a function where someone can search to find all orders that meet specific criteria.

Thanks for all your help so far.

[kgriff]

After a bit of messing around I found the solution. Works real slick, but takes a while to do. If anyone is interested let me know and I will post the database.