UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Help W/ Calculated Field VBA, Access 2010    
 
   
horseprofiler
post Feb 21 2018, 02:13 PM
Post#1



Posts: 17
Joined: 1-October 11



Greetings!

I have no experience writing VBA code but I can follow the logic. I found a VBA code that calculates a single field in a query (see pic). Because I have 10 fields I would need to create 10 separate functions: nPP1SURF, nPP2SURF, nPP3SURF ... ending with nPP0SURF

Can I accomplish this with a "single function"?

Thank you for everyone's help in advance!
This post has been edited by horseprofiler: Feb 21 2018, 02:14 PM
Attached File(s)
Attached File  Capture.PNG ( 19.69K )Number of downloads: 12
 
Go to the top of the page
 
theDBguy
post Feb 21 2018, 02:17 PM
Post#2


Access Wiki and Forums Moderator
Posts: 72,394
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Welcome to UtterAccess!
welcome2UA.gif

Yes, one function can accomplish many things. But more importantly, why do you have multiple fields with similar names? Do you know if your table structure is normalized? If it's not, it might be worth the time investment to fix your table structure first.

Just my 2 cents...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
horseprofiler
post Feb 21 2018, 02:31 PM
Post#3



Posts: 17
Joined: 1-October 11



Thank you for your prompt response!

The subject matter is horse racing and the 10 separate fields represent the horses last 10 races. In the attached pic you can see horses run at different tracks in their last 10 starts. The track surface, distance etc also are different.

I import a comma delimited file that contains all the information of the horses last 10 starts.

Thanks!
Attached File(s)
Attached File  TRACKS.PNG ( 47.63K )Number of downloads: 9
 
Go to the top of the page
 
GroverParkGeorge
post Feb 21 2018, 03:13 PM
Post#4


UA Admin
Posts: 32,787
Joined: 20-June 02
From: Newcastle, WA


When we see a table like that, the image of a spreadsheet pops into our heads, not a relational table in a relational database.

So, before you waste more time on this query, let's see about fixing the table.

Here are some useful links that will help you grasp the problem, and the solution.

Newcomers Reading List

Roger Carlson Blog on Repeating Columns

--------------------
Go to the top of the page
 
horseprofiler
post Feb 21 2018, 03:39 PM
Post#5



Posts: 17
Joined: 1-October 11



Because there are 255 fields I found that a series of 10 queries joined together with a single union query is most efficient.

Thanks!
This post has been edited by horseprofiler: Feb 21 2018, 03:39 PM
Attached File(s)
Attached File  PPLINE.PNG ( 27.85K )Number of downloads: 1
 
Go to the top of the page
 
horseprofiler
post Feb 21 2018, 03:56 PM
Post#6



Posts: 17
Joined: 1-October 11



There are a number of important calculations I can perform with the repeated columns. Utilizing a "maximum function" I can easily determine what was the horses best speed figure for the last 10 starts.

If you are familiar with horse racing, having this data displayed with repeated columns is very useful. The data will eventually be converted to proper columns and rows. If your not familiar with horse racing, I can understand your concerns.

Thanks!
Attached File(s)
Attached File  CSF.PNG ( 58.94K )Number of downloads: 3
 
Go to the top of the page
 
GroverParkGeorge
post Feb 21 2018, 05:05 PM
Post#7


UA Admin
Posts: 32,787
Joined: 20-June 02
From: Newcastle, WA


I apologize for being obstinate, and even a bit abrasive, but Access is a relational database, not a spreadsheet.

This is something that most people even are not aware of, let alone take into account. However, designing your Access database around Excel-style spreadsheets is not an appropriate approach and this problem is just one good example of why it is not.

You can get away with certain things for a while, but the very fact that you are here now, asking for help in coping with the downside of having ten repeated columns, should be a clue that maybe there's a better way.

Please invest some time in learning how to work with relational data in a relational database. You'll benefit down the road, I am quite sure.

--------------------
Go to the top of the page
 
horseprofiler
post Feb 21 2018, 05:29 PM
Post#8



Posts: 17
Joined: 1-October 11



Thanks for your input!

The comma delimited files I purchase are structured this way. Once I import them, the table with repeated columns is created. Why am I being criticized for designing a table I didn't create!
Go to the top of the page
 
zaxbat
post Feb 21 2018, 05:29 PM
Post#9



Posts: 932
Joined: 26-January 06
From: .....the wiregrass (either you know or you don't)


True that! A whole lot of peeps run into limitations in Excel and then try to move to Access. I applaud that. Access is probably where they need to be. But, it is apples and oranges. Relational databases are so much more powerful than spreadsheets and do not work the same. It seems that Excel, now, attempts to emulate relational databases by allowing multiple tables on one worksheet. Makes sense, but if you need a go-cart---buy a go-cart; alternatively, if you need an SUV---buy an SUV (just make sure to read the owners manual.....and don't try to treat the SUV like its go-cart).

--------------------
Kindest regards, and Cheers!
ZAX

A picture is worth a thousand words and a zipped DB is worth a thousand pictures.
Oh, and....please don't disappear into the Twilight Zone.... Holler back with your results!
Go to the top of the page
 
horseprofiler
post Feb 21 2018, 05:40 PM
Post#10



Posts: 17
Joined: 1-October 11



SERENITY NOW!!

SERENITY NOW!!

Any Seinfeld fans around?
Go to the top of the page
 
theDBguy
post Feb 22 2018, 12:31 PM
Post#11


Access Wiki and Forums Moderator
Posts: 72,394
Joined: 19-June 07
From: SunnySandyEggo


Hi,

So going back to your question, what exactly were you trying to accomplish with the function so we can see how we can modify it to use multiple fields?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
tina t
post Feb 22 2018, 12:45 PM
Post#12



Posts: 5,416
Joined: 11-November 10
From: SoCal, USA


what i'm wondering - and i'm not being snotty, this is a serious question - is why you are choosing to work in Access instead of Excel, when the data you're working with comes to you in a spreadsheet format? are there specific tasks or data analysis that you can't perform in Excel, and hope to accomplish in Access? as a longtime developer and staunch fan of Access, i can say with confidence that Access is not always the best tool for a given job - it depends on the data, and the job.

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
GroverParkGeorge
post Feb 22 2018, 12:46 PM
Post#13


UA Admin
Posts: 32,787
Joined: 20-June 02
From: Newcastle, WA


Ah, I see.

You didn't bother to mention that before.

So, the trick is to normalize them once you get them.

And that is a two step process.

1) Import the raw excel DATA into a temporary table in Access.

2) Run a series of append queries to move that new data into the properly normalized relational tables.

It's worth doing.

--------------------
Go to the top of the page
 
horseprofiler
post Feb 23 2018, 10:28 PM
Post#14



Posts: 17
Joined: 1-October 11



SUCCESS!!

The good people at "stackoverflow" provided me the perfect solution using the "Select Case" methodology. I posted the question and pics and in less than 30 min "Lee Mac" provided me with the code. He didn't criticize me at all, and best of all he's a Seinfeld fan!!

Thanks for your input!
Go to the top of the page
 
theDBguy
post Feb 24 2018, 11:51 AM
Post#15


Access Wiki and Forums Moderator
Posts: 72,394
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Congratulations! Glad to hear you got it sorted out. You might consider posting the solution or a link to it in case it can help others with the same problem.

Good luck with your project.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
GroverParkGeorge
post Feb 24 2018, 12:59 PM
Post#16


UA Admin
Posts: 32,787
Joined: 20-June 02
From: Newcastle, WA


I apologize for making you feel like you were being criticized.

The point is that you CAN do better, and should.

Your tables are not set up properly.

What you have now is a workaround that will need on-going future maintenance.

That is not a criticism of you; that applies to this particular database.

Sometimes, though, that's adequate.

Continued success with your project.
This post has been edited by GroverParkGeorge: Feb 24 2018, 01:51 PM

--------------------
Go to the top of the page
 
horseprofiler
post Feb 24 2018, 02:30 PM
Post#17



Posts: 17
Joined: 1-October 11



I do appreciate everyone's helpful advice!

The racing files I purchase are structured with repeating columns, I have no choice but to "Play the hand I was dealt!" I've always normalized the table with a union query (pictured prior) via 10 queries, one for each column.

Your concerns have actually challenged me to re-examine how I manage my DB. I'm happy to inform you I wrote my first VBA Function yesterday and it was a success!

Thanks to all!

Option Compare Database
Option Explicit

Function UpdateSURF(ByVal mySURF As Integer) As String
Select Case mySURF
Case Is = 1: UpdateSURF = "D"
Case Is = 2: UpdateSURF = "T"
Case Is = 3: UpdateSURF = "W"
Case Is = 4: UpdateSURF = "A"
Case Else: UpdateSURF = "x"
End Select

' Expr1: UpdateSURF([nPP1SUR])
' Expr2: UpdateSURF([nPP2SUR])
' etc

End Function
This post has been edited by horseprofiler: Feb 24 2018, 02:31 PM
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    19th June 2018 - 05:20 PM