after update issue (1 Viewer)

MaleNurse325

Registered User.
Local time
Today, 16:18
Joined
Jan 11, 2016
Messages
67
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

I’m here to help
Staff member
Local time
Today, 16:18
Joined
Oct 29, 2018
Messages
21,358
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

Registered User.
Local time
Today, 16:18
Joined
Jan 11, 2016
Messages
67
Im not very experinced atthis and 'self taught' so ...
1587132400307.png
 

theDBguy

I’m here to help
Staff member
Local time
Today, 16:18
Joined
Oct 29, 2018
Messages
21,358
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

Banishment Pending
Local time
Today, 18:18
Joined
May 11, 2011
Messages
11,613
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.
 

jdraw

Super Moderator
Staff member
Local time
Today, 19:18
Joined
Jan 23, 2006
Messages
15,364
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.
 

Users who are viewing this thread

Top Bottom