Simple Query Not Editable

[Dave D]

Can anyone tell me why I can't edit the qtyShipped field when I view this query? I've reviewed the "Why is my query read-only?" post from http://allenbrowne.com/ser-61.html, but maybe I'm too stupid to figure this one out by myself.

Thanks in advance for any help.

SELECT tblVendorInvoiceParts.invId,
       tblVendorInvoiceParts.cRecordId,
       tblVendorInvoiceParts.pRecordId,
       tblVendorInvoiceParts.qtyShipped,
       tblVendorBidPrices.vUnitPrice
FROM tblVendorInvoiceParts
     INNER JOIN tblVendorBidPrices
             ON (tblVendorInvoiceParts.cRecordId = tblVendorBidPrices.cRecordId)
            AND (tblVendorInvoiceParts.pRecordId = tblVendorBidPrices.pRecordId);

[JoeM]

What kind of relationship do you have between your two tables?
One-to-one? One-to-many? Many-to-many?

[Dave D]

Please see the attached image.

[JoeM]

I don't think that really answers the question. It really depends on your data, especially since you are only joining on 2 of the 3 fields that make up the Primary Key.

If you run this query, does it return any records?

SELECT 
    tblVendorInvoiceParts.cRecordID, 
    tblVendorInvoiceParts.pRecordID, 
    Count(tblVendorInvoiceParts.invID) AS CountOfinvID, 
    Count(tblVendorBidPrices.vBidId) AS CountOfvBidId
FROM 
    tblVendorInvoiceParts 
INNER JOIN 
    tblVendorBidPrices 
ON 
    (tblVendorInvoiceParts.pRecordID = tblVendorBidPrices.cRecordId) 
    AND (tblVendorInvoiceParts.cRecordID = tblVendorBidPrices.pRecordId)
GROUP BY 
    tblVendorInvoiceParts.cRecordID, 
    tblVendorInvoiceParts.pRecordID
HAVING 
    (((Count(tblVendorInvoiceParts.invID))>1)) 
    OR (((Count(tblVendorBidPrices.vBidId))>1));

[Dave D]

Sorry, your query does not return any records.

My query does return the records I need but I can't edit the data. I'm just befuddled why this is the case.

[JoeM]

Sorry, your query does not return any records.

That's actually a good thing. My query only returns records from one-to-many or many-to-many relationships.

Any chance you can upload a copy of your database for analysis? You can remove any sensitive data and populate with dummy data, if need be. I won't be able to look at the database until tonight (corporate policy doesn't allow me to download files off the internet from my current location), but I can take a look at it tonight.

[Dave D]

The database is proprietary so, even if I scrubbed the data, I have to keep it (the db) in-house.

[JoeM]

How about if you take a copy of it, delete out everything but those two tables, your query, and some sample data?
Or just recreate a small version of it, create the two tables, your query, and some sample data. You needn't even need all the fields in each table, just the Primary keys.

Basically, you just need to recreate the situation you with have within the structure you have.
The other option is to really work through the link on Allen Browne's website on your own. There is a good chance you are in one of those situations.

I just tried created your structure here on my computer, making some basic assumptions on how you have things set up, and I see the issue you are having. I can get it to work either. I think it has something to do with the Multi-Field Join and/or Primary Key. I will keep playing around with it and researching and see what I can come up with.

Are any of the Primary Key fields in either table Autonumbers?
How is each one Indexed (which ones allow duplicates and which do not)?

[Dave D]

Here is the breakdown, where (fk) is foreign key.

tblVendorInvoiceParts
invId - long integer (fk)
cRecordId - long integer (fk)
pRecordId - long integer (fk)

tblVendorBidPrices
vBidId - long integer (fk)
pRecordId - long integer (fk)
cRecordId - long integer (fk)

[JoeM]

But are any of them Autonumbers?
And what is the Index Property of each?
- No
- Yes (Duplicates OK)
- Yes (No Duplicates)

Just want to make sure I am setting them up exactly as you have set them up.

[June7]

I do my best to avoid compound keys and multiple field linking in queries. However, your joins are not between pk and fk but between two pairs of fk's. Seldom see joins between fk fields and that won't work for linking form/subform for data entry. What table are these tables dependent on? Where are the related pk fields?

Why would you want to do edits on these tables in a query that joins them? They do not even have a dependency relationship (pk to fk). If this is the RecordSource of a form, normally a form can do data entry/edit for only one table.

If you want to provide db for analysis, follow instructions at bottom of my post.

[Dave D]

All (fk) fields are: Yes (Duplicates OK)

June, what you say about pk to fk is true, but I (think) I need these as is.

[June7]

Duplicates of each fk are allowed in each table. If there are duplicate pairs of pRecordId and cRecordId (and I expect there are because there is a 3rd field involved in the compound primary key for each table) in both tables then the query will not be editable. See reason 8 of Allen's list. Data structure is flawed.

[Dave D]

June7, you are correct in all of your assumptions. It looks like I need to find another way to skin this cat.

Thanks to all who tried to help me. Since this is an 'unsolvable' problem based on the data structure, I'll mark this thread as solved.

[JoeM]

Duplicates of each fk are allowed in each table. If there are duplicate pairs of pRecordId and cRecordId (and I expect there are because there is a 3rd field involved in the compound primary key for each table) in both tables then the query will not be editable.

June7, you are correct in all of your assumptions.

That is what I was trying to check for with the code I posted in post #4, but I see I had an error in that I was joining on the wrong fields. It should've been:

SELECT 
    tblVendorInvoiceParts.cRecordID, 
    tblVendorInvoiceParts.pRecordID, 
    Count(tblVendorInvoiceParts.invID) AS CountOfinvID, 
    Count(tblVendorBidPrices.vBidId) AS CountOfvBidId
FROM 
    tblVendorInvoiceParts 
INNER JOIN 
    tblVendorBidPrices 
ON 
    (tblVendorInvoiceParts.pRecordID = tblVendorBidPrices.pRecordId) 
    AND (tblVendorInvoiceParts.cRecordID = tblVendorBidPrices.cRecordId)
GROUP BY 
    tblVendorInvoiceParts.cRecordID, 
    tblVendorInvoiceParts.pRecordID
HAVING 
    (((Count(tblVendorInvoiceParts.invID))>1)) 
    OR (((Count(tblVendorBidPrices.vBidId))>1));

That one would probably have returned records...