Code Needed - dodgy explanation! (1 Viewer)

Hello folks
I would be grateful for any suggestions to solve the below problem. It's a fairly straightforward problem, but incredibly difficult to explain straightforwardly. I will try though.

As part of a stock control database, there is a facility for parents to purchase school uniforms and the process is thus: Each item is stored in [OrderTbl] and the facility is there to return items ordered erroneously back to stock.

However, occasionally an item is returned as ‘damaged’ and this has to be removed from [OrderTbl] and appended to [DamagedReturnsArchive].

OrderTbl:
[OrderID] Autonumber
[StudentID] Customer Id number
[Dateentered] Date of purchase
[Timeentered] Time of Purchase
[Numentered] Number of the Item purchased
[ItemClassLevel3ID] (Unique ItemID)
(Identical named fields appear in [DamagedReturnsArchive])

This is currently done by running two queries

1. Append Query "AppendDamagedItemstoArchiveTble"
2. Delete Query "RemoveDamagedItemsFromOrderTbl"

A previously unforeseen problem has now occurred to me. On a rare occasion a customer may purchase more than one of any item, for example ‘2 short sleeved t-shirts XXL’ but might be unhappy with one of them and wishes to return it.

At this time the above purchase is stored in the order table with the ItemID but with [NumEntered]=2. When the return process above is activated the complete order of 2 items is archived rather than only the single returned item.

What I’m trying to achieve is this:

On click
If [OrderTble]![Numentered]=1 then Append the record to [DamagedReturnsArchive] then delete record from [OrderTble]
If [OrderTble]![Numentered]>1
Msg “There are [number?] identical items in this order. How many do you wish to return?”
Response [user input number] or [All]
If the [User Input Number] =< [OrderTble]![Numentered] Then append to [DamagedReturnsArchive] and update [DamagedReturnsArchive]![numentered] to [User Input Number]. Then subtract that number from [OrderTble]![Numentered]

Simply put, I am trying to return at least one item from an order containing more than one identical item to an archive table. The actual number of items to be returned in this case will be determined by the user, then the new balance (Numentered-Number selected) updated in the original table.

Thankfully a scenario like that above is extremely rare but as Murphy’s Law states, if it can happen, it inevitably will happen. I can make the adjustments manually by editing the two tables but I cannot expect the database user to do that.

I realised as I was typing this that there may be some flaws in my logic. I apologise for this but I have very little knowledge of code.

I'd be very grateful for any suggestions made

Rob

Yes, this is straightforward and the solution is just as straightforward---fix your tables.

Action queries (APPEND, UPDATE, DELETE) are usually hacks around poor table structures. This is true in your case. Data shouldn't be moved around in a database, it should be related and designated proprely. You don't have a seperate table for stock in different statuses, instead you mark the stock with the approprate status in the Stock table.

Your stock table is the only one you need for all your stock--even the damaged stuff. This can be handled in 2 ways--a simple yes/no box to designate if it is damaged, or most likely a [StockStatus] field that has multiple values (In Stock, In Use, Damaged, etc.). That's how your database should work, not with a bunch of action queries moving stuff all around.

Can you post a screenshot of your relationship tool? So we can see your table structure and relationships?

You should have a table for "Orders".
Your "Orders" table would have a child that is "LineItems".
LineItems has one line for each type of item ordered along with a quantity.

In your database you could then either add a LineItem that removes the returned item from its order and add a record to your archive OR you could update the existing LineItem and add to your archive.

The Order itself should always be a parent record even if you only have one LineItem. You would not archive the Order but you could update LineItems to show returns of different types.

Please see attached

The Stock Table is [ItemClassLevel3ID]

Yeah, I see a lot of issues. My overriding advice is to read up on normalization (https://en.wikipedia.org/wiki/Database_normalization). You might be even able to find a template database for this sort of system online somewhere. Here's a list of issues I see:

1. OrderTbl needs to be 2 tables. You are using this like an Order table (storing who and when) and an OrderDetail table (storing the specific what and how many). This is incorrect, you need both those tables for an ordering system you can't just cram all that data into 1 table for this system to work like you want.

2. Redundant data/Circular relationships. You shouldn't store data in multiple places, nor should there be more than one way to trace a path between/among tables. You have 3 ClassifyLevel tables, all related to each other (although not formally in the screenshot). Then OrderTbl is directly linked to all 3 as well. There is no need for that. OrderTbl should only hold ItemClassLevel3ID value. With just that value it can connect to ClassifyLevel3 which can connect to ClassifyLevel2 wich can connect to ClassifyLevel1. You've over related your tables.

3. Storing calculated values. There is no need for ItemBalanceTbl. A simple query on OrderTbl can be used to obtain the same data. You shouldn't store balances, you should determine them by adding up all the credits/debits to inventory.

4. Invalid primary key. OrderTbl probably shouldn't be using Dateentered/Timeentered fields as the primary key. It should have its own autonumber primary key. Additionally, if you are actually using the data in these 2 fields for other purposes, other than making the record unique, then the data they contain should be in just 1 field. A Date/Time field can hold both date and time, put the data together in 1 field and life is simpler.

The real key to this is the OrderDetails table. This new table will tell you what specific items where ordered by who and then can be used to determine the balance of each item. That eliminates all those action queries that move data.

Again, I'd search for an existing system you can customize and use because this has been solved. But if you look into the items I deteailed above, you can change your system to get it working as well.

plog said:

Yeah, I see a lot of issues. My overriding advice is to read up on normalization (https://en.wikipedia.org/wiki/Database_normalization). You might be even able to find a template database for this sort of system online somewhere. Here's a list of issues I see:

1. OrderTbl needs to be 2 tables. You are using this like an Order table (storing who and when) and an OrderDetail table (storing the specific what and how many). This is incorrect, you need both those tables for an ordering system you can't just cram all that data into 1 table for this system to work like you want.

2. Redundant data/Circular relationships. You shouldn't store data in multiple places, nor should there be more than one way to trace a path between/among tables. You have 3 ClassifyLevel tables, all related to each other (although not formally in the screenshot). Then OrderTbl is directly linked to all 3 as well. There is no need for that. OrderTbl should only hold ItemClassLevel3ID value. With just that value it can connect to ClassifyLevel3 which can connect to ClassifyLevel2 wich can connect to ClassifyLevel1. You've over related your tables.

3. Storing calculated values. There is no need for ItemBalanceTbl. A simple query on OrderTbl can be used to obtain the same data. You shouldn't store balances, you should determine them by adding up all the credits/debits to inventory.

4. Invalid primary key. OrderTbl probably shouldn't be using Dateentered/Timeentered fields as the primary key. It should have its own autonumber primary key. Additionally, if you are actually using the data in these 2 fields for other purposes, other than making the record unique, then the data they contain should be in just 1 field. A Date/Time field can hold both date and time, put the data together in 1 field and life is simpler.

The real key to this is the OrderDetails table. This new table will tell you what specific items where ordered by who and then can be used to determine the balance of each item. That eliminates all those action queries that move data.

Again, I'd search for an existing system you can customize and use because this has been solved. But if you look into the items I deteailed above, you can change your system to get it working as well.

Click to expand...

Many thanks for your insight Plog. I'll certainly look into your recommendations, however, with the exception of the possible future problem, the system is working very well. Perhaps more problems will become evident in the future though, so it's definitely worth looking at restructuring.

Thanks again

Rob