after update issue

[MaleNurse325]

Guys, Im almost embarrased to ask this but it has bugged me for over an hour.

On a record I have Item1 to Item8 and corespinding Quantity figures. so Qty1 to Qty8
All I am trying to do is, if for instance the field Item2 is not null then AfterUpdate Qty2 with the value 1 (that is going to be the default value but I want to be able to edit that fugure if required)

Im having a brain melt here

Cheers guys

 

[theDBguy]

Hi. If you're saying you have eight columns for Qty, then you may have a bad design. Can you show us your table structure?

 

[MaleNurse325]

Im not very experinced atthis and 'self taught' so ...

 

[theDBguy]

Im not very experinced atthis and 'self taught' so ...
View attachment 81072

Yeah, sorry, definitely a "bad" structure. Try searching for "normalization" to see how database tables should be designed. If you need help, please post a description of the business process you're trying to model. Cheers!

 

[plog]

Database normalization (https://en.m.wikipedia.org/wiki/Database_normalization) is the process of structuring your tables and fields. I see two glaring issues with what you've done:

1. Improper field types. It seems odd that a field named iten1Qty is a Short Text field. Generally, quantities are numbers. If you ever plan on doing math on these fields ( e.g summing them up, finding an average, etc) You're gonna want these as numbers.

2. Numerated field names. When you feel the need to prefix\suffix field names with numbers, it's time for a new table. That means all your itemX and itemXQty values should go into a new table with this structure.

TblItems
item_ID, autonumee, primary key
ID_Consigmment, number, foreign key to existing table
item_Name, Short Text, holds value currently in all itemX fields
item_Qty, number, holds value currently in all itemXQty fields

That's it, that table with just 4 fields can hold unlimited item data for a record in your existing table. For example, if you had 6 items for a consignment you would enter 6 records into tblItems, not data into 6 fields of the other table.

Again, read up on normalization.

 

[MaleNurse325]

Thanks Plog

 

[jdraw]

There are several articles in the Database Planning and Design link in my signature that may be helpful to you. I recommend you work through 1 or 2 of the tutorials from RogersAccessLibrary mention in the link. You will experience the design process, including Normalization, is you work through the tutorials.