New to Access (1 Viewer)

Buster

Registered User.
Local time
Today, 07:46
Joined
Jan 22, 2019
Messages
16
Hi All. My name is Buster and for my sins I have had to learn access basics rapidly as we are moving databases from the creaking old Paradox to access.I have done all the basic stuff, created necessary tables, relationships and data entry forms and still using Crystal Reports as it looks easier and has great functionality than access reports (I think).

I am now getting to some little problems that I can't seem to get round. The one right now is that I want to calculate age from date of birth on a form. This I can do and have done. However I now want to convert this calculated value into a fixed integer and enter it as a field in the form's associated table. The idea being that this will show the age of the person when they were captured into the system, so it will remain a fixed value in the table. Any ideas?.
 

isladogs

MVP / VIP
Local time
Today, 14:46
Joined
Jan 14, 2017
Messages
18,186
Welcome to AWF. I remember Paradox though not fondly....

What you want sounds a strange idea but its easy enough.
In your Form_Current event add code similar to this

Code:
If Nz(Dlookup("AgeOnAdmission","TableName","PersonID=" & Me.PersonID),0)=0 Then
'add update SQL to populate that field in your table
End If

Substitute your own field, table, control names
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 09:46
Joined
Feb 28, 2001
Messages
26,999
As a former Paradox user, I have to say I absolutely LOVED Paradox for DOS. But when they converted it to Paradox for Windows, they screwed the pooch.

If you were an EVENT programmer with Paradox, you will find a lot more events are available in Access, giving you finer control over sequences of events. The other difference is that they didn't use BASIC as the basis of their event code.

On the other hand, Normalization pre-dates both Access AND Paradox, so you (or the original developers) should have seen some of the rules on normalization. They are external to both environments and therefore would NOT have changed.

This might also be a good time, since you are converting things, to at least check on whether the tables actually WERE normalized originally. But of course, that depends on your mandate from the boss as to how much time you can spend on improvements vs. how much you can spend on straight conversions.
 

Buster

Registered User.
Local time
Today, 07:46
Joined
Jan 22, 2019
Messages
16
I am now in my 10th day of learning and using Access - on a very steep learning curve. There are many fine aspects to Access but there are some unfriendly or somewhat obscure user interfaces. The one that I find strangely annoying is the inability to define a calculation for an input form field and have the calculated result update the associated table field. There was a very good data entry app in its day (now regrettably obsolete) made by Borden called Object Vision that allowed you to define the calculation in the field and the result would automatically be updated to the DB. I think Access has copied many of their ideas. However from what little I have seen it seems that one has to delve into Visual Basic to carry out such a request. Haven't got to VB yet! Obviously I can't do the calculation on my table through a query update as i have upward of 8 000 records in the table and it seems crazy to recalculate the whole table having entered one new record.

Any simple ideas for a newbie.
 

isladogs

MVP / VIP
Local time
Today, 14:46
Joined
Jan 14, 2017
Messages
18,186
There are many ways of doing calculations in Access. For example, you can use
1. the calculated field datatype
2. An update query filtered to the specific record you want to update

However, in the vast majority of situations, there is no need to store a calculated value. Just use an expression in a query to calculate the value as and when it's required. That way its always up to date.

The exception to this is where you need a snapshot of calculated data at a specific time e.g. Gross wages based on an hourly rate or annual salary, both of which may change over time
 

Buster

Registered User.
Local time
Today, 07:46
Joined
Jan 22, 2019
Messages
16
Thanks for the advice. I would be happy to use the calculated field datatype, however it is limited on the functions one can use and my calculation requirements are not supported.

Your second option is interesting but how do you limit the query calculation to apply only to a new record being added to the table via a form? Advice would be appreciated.
 

Buster

Registered User.
Local time
Today, 07:46
Joined
Jan 22, 2019
Messages
16
By the way I still think it would be far more sensible and user friendly to handle all the calculations at the form input side and update the table with the final calculated values. Seems like an oversight to me and I cannot see this causing any normalisation problems.
 

isladogs

MVP / VIP
Local time
Today, 14:46
Joined
Jan 14, 2017
Messages
18,186
Calculated fields are also not recommended if you are ever likely to upsize to SQL Server or other external databases as the datatype isn't supported

Filtered update queries are very simple - you just add a WHERE clause

Code:
UPDATE table_name
SET field1 = value1, field2 = value2, ...
WHERE condition;
For example:

Code:
UPDATE tblStudents 
SET tblStudents.YearGroup = Forms!frmStudents.txtYearGroup,
tblStudents.TutorGroup = Forms!frmStudents.txtTutorGroup
WHERE tblStudents.StudentID = Forms!frmStudents.txtStudentID;

Or if the query is run whilst the form is open:
Code:
UPDATE tblStudents 
SET tblStudents.YearGroup = Me.txtYearGroup,
tblStudents.TutorGroup = Me.txtTutorGroup
WHERE tblStudents.StudentID = Me.txtStudentID;

If you are using a SQL statement in code then you need delimiters e.g.
Code:
CurrentDB.Execute "UPDATE tblStudents" & _ 
" SET tblStudents.YearGroup = " & Forms!frmStudents.txtYearGroup & "," & _ 
" tblStudents.TutorGroup = '" & Forms!frmStudents.txtTutorGroup & "'" & _ 
" WHERE tblStudents.StudentID =" & Forms!frmStudents.txtStudentID & ";"

You may find this link very useful: https://www.w3schools.com/sql/sql_update.asp

I'm not quite sure what you think is missing from Access.
In case it helps, attached is a very simple calculator (at 'form input side')
 

Attachments

  • Calculator.zip
    24 KB · Views: 71

pbaldy

Wino Moderator
Staff member
Local time
Today, 07:46
Joined
Aug 30, 2003
Messages
36,118
Welcome aboard. FYI, I moved this thread out of the introductions forum.
 

Frothingslosh

Premier Pale Stale Ale
Local time
Today, 10:46
Joined
Oct 17, 2012
Messages
3,276
I have to agree with the others - unless there is a documented business need for calculated data to be saved for some reason, you should avoid doing so since it just wastes space.

In the example you gave, you have the DOB and you have the date they were entered into the system. With that, you can calculate and display the age any time you need it without having to waste space storing it.

For things like gross wages paid and and net prices after discounts, it makes sense. For determining age at sign-up, not so much.

As an example, where I work, we store the amount charged for a medical claim as well as the amount authorized (paid). If an adjustment has been made (could be a mis-billing, a legal settlement, whatever), then the adjusted amount is included as well. Some of my apps also include the difference after an adjustment, even though that difference isn't saved anywhere. It's done that way because the calculations involved take fractions of a millisecond (so basically zero time), whereas our storage is decidedly finite.
 
Last edited:

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 09:46
Joined
Feb 28, 2001
Messages
26,999
I'm going to jump in on this bandwagon. Storing a calculation in a field is something you would do from an EXCEL environment. But the problem is that the computation is a waste of space in a record that has a definite, if generous, size limit.

Doing the computation in a form for display purposes makes sense. Doing it for data entry purposes is not so good. For EXCEL apps, you might not have a choice to due the lack of general versatility of EXCEL vs. ACCESS. But if you have everything stored that you need to make a computation, you can make it when you need to see it. This is done for reports by using queries and for forms by using .Rowsource expressions.
 

isladogs

MVP / VIP
Local time
Today, 14:46
Joined
Jan 14, 2017
Messages
18,186
As an FYI, SQL Server now has computed columns

Thanks Paul. I'm still using SSMS 2014 so wasn't aware of that.
I do hope SQL Server still doesn't accept attachment & multivalued fields!!

Anyway, does that mean calculated fields in Access can now be upsized to the equivalent in SS? The article makes no mention of that.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 07:46
Joined
Aug 30, 2003
Messages
36,118
I haven't tried to upsize so I don't know. Fact is, not using a calculated field is so ingrained I haven't used the computed column yet. :eek:
 

isladogs

MVP / VIP
Local time
Today, 14:46
Joined
Jan 14, 2017
Messages
18,186
TBH I really hope it can't be upsized as that takes away one good reason for not using calculated fields.
I don't think I've ever used them in my own apps
 

Frothingslosh

Premier Pale Stale Ale
Local time
Today, 10:46
Joined
Oct 17, 2012
Messages
3,276

pbaldy

Wino Moderator
Staff member
Local time
Today, 07:46
Joined
Aug 30, 2003
Messages
36,118
I've thought about it for things like concatenating first and last names, but never actually done it. Old dog, new trick. :p
 

Buster

Registered User.
Local time
Today, 07:46
Joined
Jan 22, 2019
Messages
16
Thanks for all your input. Need to get my head around a different 'philosophical' approach and put my learning cap back on.
 

Users who are viewing this thread

Top Bottom