DISTINCT does not allow New Records (1 Viewer)

Hi All:

Using Access 203.

I have a form and a subform tied up by the PurchseOrder field. The subform shows the items included in the PO but for any PO with more than ONE item, it shows the record as time times as there are item in the PO.

In other words, if I have one PO with 1 item, it shows one time but if I have 5 items in the PO, it shows it as 5 records all having the same PO.

I solved it using the DISTINCT in the query but it now prevents the addition of new records!?

I remove it and it allows additions but with the problem described above.

This is the Form's query:

SELECT DISTINCT Orders.OrderID, Orders.CustomerID, Orders.EmployeeID, Orders.OrderDate, Orders.PurchaseOrderNumber, Orders.ReceivedDate, Orders.ShippingMethodID, Orders.FreightCharge, Orders.SalesTaxRate, Orders.PaymentReceived, Orders.Comment, Orders.PaymentMethod, [Order Details].[ItemReceived?] FROM Orders INNER JOIN [Order Details] ON Orders.OrderID=[Order Details].OrderID WHERE ((([Order Details].[ItemReceived?])=0)) ORDER BY Orders.OrderDate DESC WITH OWNERACCESS OPTION;


this is the SUBFORM's query:

SELECT qryOrderDetailsAll.ProductID, qryOrderDetailsAll.Quantity, qryOrderDetailsAll.[ItemReceived?], qryOrderDetailsAll.PurchaseOrderNumber FROM qryOrderDetailsAll ORDER BY qryOrderDetailsAll.[ItemReceived?] DESC;

Any ideas where I messed up?

thanks

Remove the Order Details table from the query used for the parent form.

If you only want to list orders that have outstanding items (I guess this is what the criteria on ItemRecieved is for) then either create a query to first list just the order number of those orders and use that to filter down your master purchase order table, rather than join it, or use a WHERE statement like:

WHERE OrderID IN (SELECT OrderID FROM [Order Details] WHERE [ItemReceived?] = 0)

Let Access handle the relationship between the parent form and subform (which it should do nicely if your relationships are set up correctly).

Hope this helps.

Tim

Once you do a SELECT DISTINCT, the query can no longer be updated. It's the same way you can't update aggregate (group by) queries because you're looking at a subset of the data, and Access doesn't know which record to update.

I didn't write these out like this (forget where I got this checklist), but here's something you may want to copy/paste for future reference:

Why is my query read-only?

If you cannot edit the data in a query, this list may help you identify why it is not updatable:

* It has a GROUP BY clause. A Totals query is always read-only.
* It has a TRANSFORM clause. A Crosstab query is always read-only.
* It uses First(), Sum(), Max(), Count(), etc. in the SELECT clause. Queries that aggregate records are read-only.
* It contains a DISTINCT predicate. Set Unique Values to No in the query's Properties.
* It involves a UNION. Union queries are always read-only.
* It has a subquery in the SELECT clause. Uncheck the Show box under your subquery, or use a domain aggregation function instead.
* It uses JOINs of different directions on multiple tables in the FROM clause. Remove some tables.
* The fields in a JOIN are not indexed correctly: there is no primary key or unique index on the JOINed fields.
* The query's Recordset Type property is Snapshot. Set Recordset Type to "Dynaset" in the query's Properties.
* The query is based on another query that is read-only (stacked query.)
* Your permissions are read-only (Access security.)
* The database is opened read-only, or the file attributes are read-only, or the database is on read-only media (e.g. CD-ROM, network drive without write privileges.)

Click to expand...

thanks to both.

Pretty handy list to have.

thanks again.