Why does "Duplicates Okay" break my Database?

[josekreif]

SOLVED
Background
Guys, I inherited the development of a database, the person who built it unfortunately passed away. We have to make changes to a items table because, right now everything ships in one box, changes will allow some items to be shipped in multiple boxes with each box having the appropriate weight and dimensions.

Our items table keeps track of the weight and dimensions. And we have a UPS shipment query that pulls the item from the table based on a orders list and sends them to UPS for label printing.



I have found that if you duplicate the item's ID and add separate data for weight and dimensions, the query will pick out the item for each time the ID is listed and exports the information 100% perfectly from what I can see.

PROBLEM
Now our queries cant be changed or deleted. It'll say "recordset cannot be updated", or "Data is Read-only" I tried using "Dynaset (Inconsistent Updates)", and lets just say something like that can get you fired. It allows you to edit/delete, but it deletes information from tables that you don't want to.

deleting and updating records in the query should only makes changes to our Orders table, and that's what we want. Not deleting things from Vendors table and other tables aswell.

[ranman256]

Is the db stored on a server?
is the server folder read only?
is the database set to read only?
is the table set to read only?
is the forms set to read only?

[josekreif]

Is the db stored on a server?
is the server folder read only?
is the database set to read only?
is the table set to read only?
is the forms set to read only?

NO
NO
NO
NO
and
NO

This is a "Duplicates Okay" problem. If I set "No Duplicates" everything goes back to working

[ranman256]

You said,
queries cant be changed or deleted, and "recordset cannot be updated", or "Data is Read-only"
how is this possible if nothing is readonly?

[josekreif]

You said,
queries cant be changed or deleted, and "recordset cannot be updated", or "Data is Read-only"
how is this possible if nothing is readonly?

I have no idea. The way its set up, Access must hate allowing duplicates of item_num.

[josekreif]

I create a new table entirely and moved over all the dimension and weight information. The items table doesn't need that anyways. The items table can keep it's primary key and no duplicates now.

The new table will have a foreign key to the Items table. It will allow duplication and it's own weight. So far with minor testing, the queries pull the information I want, Also deleting and updating orders looks good.

I'm moving this to solved

[kagoodwin13]

Seems like you might have figured it out, but it looks like you needed one higher level of ID.

In table OSMulti, you need these changes:
ItemID (higher level of ID to delineate between same MasterID and different heights/widths/lengths/weights)
MasterID
Break out in separate table from boxes

Box_ID and Box_Num -- help me understand what you are trying to do. Do you want a unique ID for each box in the sea of every box ever (which I think you need)? Do you also need a running count for each order (box 1 of 3, box 2 of 3, box 3 of 3, etc.)? If so, "box 1 of 3" would have to come from a calculation in a query.

tBox
-----
Box_ID
Order_Number
Item_ID

qBox_Num
-------
Order_Number
Box_ID
Box_Num (calculated by using a solution similar to OVER PARTITION - https://stackoverflow.com/questions/...y-in-ms-access)

Once you make those changes, I think you'll have a lot smoother database operation.