How to create a form based on Summary Query

[jt196]

I'm trying to create an editable form of fulfilled orders on my system and am running into problems with creating a form based on this query. The field that I need to update (invoice number) is read-only. I DO understand the reasons for this:

The query takes information from an orders table and a transactions table which have a one to many relationship. I don't want to use any of the transaction fields, but I do need to use a query based on the transactions in an order to filter the order numbers displayed on the form. This summary will tell me all of the fulfilled orders (ie when Quantity Ordered - Quantity Delivered = 0).

I want to create a form based on the above Query but can't seem to do it. I want the final fields of the form to contain: CustomerID, OrderID, PO_Number, InvoiceNumber - all of which are on the orders table, and none of which are on the transactions list

I understand that a query with a 'group by' property is read only, but is there any way to - for example - filter the form results based on this query, or use VBA to get around this.

Sorry if my explanations here aren't so clear, but I'm fairly new to the access terminology, hence the occasional 'whatsitcalled' usage. Thanks in advance!

Any ideas, workarounds?

[NeoPa]

I think your own suggestion seems to be the best idea. You can use the complex, non-updatable, GROUP BY query to filter a simpler form bound directly to the table.

Another suggestion, if no aggregation is required, but only reducing the output to a single record for each grouped item, is to use the DISTINCT predicate. Thay may be more fiddly, depending on exactly what you're trying to do.

[jt196]

Hmm not sure if if I've explained myself correctly here. For an order to qualify as fulfilled, there needs to be a minimum of two items on the transaction table.

1 - the order coming in (and the quantity ordered)
2 - the delivery going out (and an equal quantity going out).

So I have one query that takes a summary of all of the same items on an order (order or delivery) and totals up the difference. I have a query that only includes items which have a difference of zero (meaning they are fulfilled).

I want to base a form on the orders that are on this query - but am having a hard time because it is based on a query that uses summing - even though the fields I'm using are not actually included in the summary.

Hope this clears my question up!

JT

[NeoPa]

Well, the second suggestion is almost certainly not going to be of any use to you, but can you tell me why you believe the first doesn't appear to be of use.

[jt196]

Well, the second suggestion is almost certainly not going to be of any use to you, but can you tell me why you believe the first doesn't appear to be of use.

Hi NeoPa - well I probably should have said - I've only been a user of Access for a few weeks.

I've done the group by query and that's working fine, but I'm stuck at getting the form to be filtered by it. If I use the query as a data source, the records aren't updateable. How exactly do I use the query to filter the records in the form?

[NeoPa]

In your code you open the form using :

DoCmd.OpenForm(FormName, FilterName, ...)

Check in the help (Press F1 with the cursor on the OpenForm bit) for the specifications for the FilterName querydef. This would be the non-updatable GROUP BY query. The RecordSource of the form would then be a simpler reference to the table itself.

Disclaimer:
I've never used this method myself as I've never had a similar requirement, but I've noticed it there before, and I believe it should allow you to do what you want.

Let us know how you get on and Welcome to Bytes!

[jt196]

In your code you open the form using :

DoCmd.OpenForm(FormName, FilterName, ...)

Check in the help (Press F1 with the cursor on the OpenForm bit) for the specifications for the FilterName querydef. This would be the non-updatable GROUP BY query. The RecordSource of the form would then be a simpler reference to the table itself.

Disclaimer:
I've never used this method myself as I've never had a similar requirement, but I've noticed it there before, and I believe it should allow you to do what you want.

Let us know how you get on and Welcome to Bytes!

OK so I've tried this filter and it seems to just pull up all of the records rather than filtering out the ones on the query.

Is it possible I need to place a WHERE Condition in the next space, ie where [OrderID] = [QOrdersNotInvoi ced].[OrderID]. I've tried that and it just seems to come give me a box asking for the '[QOrdersNotInvoi ced].[OrderID]' number.

Any hints?

[NeoPa]

If your filtering is as simple as that then certainly. Don't even worry about a WHERE clause. Simply pass a filter in the WhereCondition parameter. I understood from your earlier posts though, that it wasn't that simple. In fact, that it needed to match the results of your GROUP BY query.

If it can be specified in the WhereCondition parameter then clearly this is a much simpler issue.

[jt196]

So the QOrdersNotInvoi ced is the query with the GroupBy results in and that's the one that isn't editable. That query contains results from tables 'Orders' and 'Transactions' - summarising the results of transactions per order.

If use the command:

DoCmd.OpenForm FOrdersNotInvoiced, , QOrdersNotInvoiced

All that is presented to me is a list of ALL of the orders on the Orders table.

[NeoPa]

I've no idea why you have used two commas (,) between your parameters. In post #6 I give both the name of the parameter and the position. As you have put the parameter in the wrong position and without a name it is clear that it will not work. I must admit I haven't any experience of getting it to work myself, but from the Help system I suggested something that should work. Why don't you try it again with either the parameter in the second position, or with the parameter named. Either should ensure it is used as you need it to be.

[jt196]

NeoPa - was just following the instructions on here:

http://www.datawright.com.au/access_resources/access_docmd.openform_arguments.htm

The openform command is thus:

DoCmd.OpenForm FormName, View, FilterName, WhereCondition, DataMode, WindowMode, OpenArgs

So the empty space between the commas was just a default value for 'View'.

[leach613]

I don't understand why you would want to update that kind of info. When I need to see this kind of info I usually just make a report. What is there to update?

Evy

[jt196]

Hey there - no problems it's all sorted. I've made a form that uses a combo box to choose the information.

As for the need, well the system works like this:

INCOMING ORDER --> Items on TOrders + Items on TTransactions (including QuantityOrdered )

PART OR ALL ORDER GETS SHIPPED OUT --> New Delivery Note + New Items on TTransactions (the new transactions items have a delivery note ID and an order ID attached to them - and a number in the QuantityDeliver ed field)

When an order is fulfilled, the totals columns for all items against an order - will be zero and therefore qualify to be on this QOrdersNotInvoi ced query. So each item on the order may have multiple deliveries and therefore multiple entries on the order. That means that I needed to take a sum of all of these items as part of a query so I know what's ready to be invoiced.

At the end of it all, I wanted a form that would give me quick access to all of the fulfilled orders that hadn't been invoiced - that way I could input them into Sage and go back into Access and quickly give each order an Invoice number.

This is an interim solution until I can figure out how to export these orders into Sage once a week.

It may seem all a bit long-winded, but I wanted to move the office tasks away from the factory tasks and keep them separate. This way, once a week, I sit down and do all the week's invoices without them coming up and bugging me for delivery notes (because I'll have moved away from the area by then and be working remotely), to keep track of all the outstanding orders and finally, for the factory can have a work schedule of incomplete orders.

Hope that all makes sense - if you can see of an amazingly way to manage the partial order fulfillment process then I'd be happy to hear it. I'm a complete beginner to access and have only been working with it (albeit feverishly) for the last couple of weeks.

[NeoPa]

jt196: NeoPa - was just following the instructions on here:

I beg your pardon. I checked but misread the order - both in my earlier post and when I rechecked it after your post. Your extra comma (,) is absolutely right, and the names are not necessary as long as the order is correct.

Sorry for the confusion.

[NeoPa]

jt196: Hey there - no problems it's all sorted. I've made a form that uses a combo box to choose the information.

I'm very pleased to hear it :)

I'm sorry my directions weren't much help to you in the end, but impressed that you managed to resolve it in spite of that.

Good for you!