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
> Normalisation Help Please, Access 2016    
 
   
firlandsfarm
post Oct 11 2018, 10:38 AM
Post#1



Posts: 300
Joined: 28-April 02
From: Heathfield, England


I was (and still am, hence this post/question) trying to find the Maximum of 2 fields (B and C) across a table (it's needed for a formula of (A+max(B,C))/2) ... for simplicity sake let's say it's table of daily share prices B records the Open price and C is the Close price. Access doesn't have a Max function across fields. I know I can do it using iif's but that's not the reason for posting this topic. I have looked around the Internet. This problem is discussed but each time it draws "poor normalisation" comments.

The only way I can see to restructure the data is to split the data into 2 tables, Table1 for the Share ID and Date and Table2 for Columns B and C values and link the Table1 share/date record to the Table2 price records with 2 records per share/date. I can then group the Table2 records, extract the Max value for each group and then link back to Table1 for the required dataset. This seems a bit messy and long winded to me! None of the discussions actually expanded on HOW the normalisation was lacking just that it was. Is my revised structure what they would have been thinking of or is there something else?

--------------------
Never doubt the courage of the French - they discovered that snails are edible!
Go to the top of the page
 
nuclear_nick
post Oct 11 2018, 10:48 AM
Post#2



Posts: 1,619
Joined: 5-February 06
From: Ohio, USA


You're still thinking of across, not down.

CODE
tblSharePrice
----------------------
SharePriceDate
SharePriceAmount


The 'min' SharePriceDate is the 'Opening' price, the 'max' SharePriceDate is the 'Closing Price'. Both prices are in the 'SharePriceAmount' field, which you should be able to use your formula on.

Savvy?

--------------------
"Nuclear" Nick
____________
The top three reasons to hide code; 1) It's not your own. 2) It's your own, but it's so crappy you don't want anyone to see it. 3) The comments in your code would get you in a lot of trouble if ever made public.
Go to the top of the page
 
firlandsfarm
post Oct 11 2018, 03:42 PM
Post#3



Posts: 300
Joined: 28-April 02
From: Heathfield, England


No savvy Nick because I thought I was certainly thinking down! I said one price per record and a different record for start and end ("2 records per share/date"). That's down, yes!

Also I don't understand Code, sorry frown.gif

I think we are talking the same but maybe misunderstanding you as well. frown.gif

Anyway it still seems a lot more work than one record ... creating 2 tables, linking them, creating 3 records when 1 should do. Not very efficient. But from your comment I think that's what people will say is needed, if that's so I welcome 1 record and iif's. smile.gif

--------------------
Never doubt the courage of the French - they discovered that snails are edible!
Go to the top of the page
 
Jeff B.
post Oct 11 2018, 05:44 PM
Post#4


UtterAccess VIP
Posts: 10,140
Joined: 30-April 10
From: Pacific NorthWet


?The 'min' share price is the opening price? Did you check out the US markets the last couple days <g>?

--------------------
Regards

Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page
 
firlandsfarm
post Oct 12 2018, 07:12 AM
Post#5



Posts: 300
Joined: 28-April 02
From: Heathfield, England


Agreed Jeff smile.gif Min and Max are rarely interchangeable with Open and Close!

--------------------
Never doubt the courage of the French - they discovered that snails are edible!
Go to the top of the page
 
nuclear_nick
post Oct 12 2018, 07:56 AM
Post#6



Posts: 1,619
Joined: 5-February 06
From: Ohio, USA


Sorry to cause a little confusion... I use the 'code' tags because it then uses a mono-spaced font.

So with that.

QUOTE
table of daily share prices B records the Open price and C is the Close price.


To me that seemed like you thought of recording the prices like this...

tblSharePrice
---------------
ShareID (fk)
SharePriceDate
OpeningPrice
ClosingPrice

And only having one record per date, and if you look at the 'datasheet' view...

CODE
ID  Share Price Date         Opening        Closing
1   10/1/2018                     10.25           11.50
1  10/2/2018                     11.50           9.25


Whereas I propose something a little different.

tblSharePrice
---------------
ShareID
SharePriceDateTime
SharePrice

So that your data would then look like...

CODE
ID  Date/Time                   Price
1    10/1/2018 9:00 AM     10.25
1    10/1/2018 10:00 AM   10.50
1    10/1/2018 11:00 AM   9.45
1    10/1/2018 12:00 AM   10.00


First, this allows for collecting data throughout the day, not just the two points of open and close. To find what the open price is, look for the min time of the same day, and the Price would be the open, while the max of the time for the day would be the closing price.

That's the difference between across... A list of 'price fields' going across the top ('Opening Price', 'Midday Price', 'Closing Price') and down... Date/Time and Price. Recording the price hourly? My table handles that. Recording the price once a day? My table handles that. Recording the price every minute? Large data set there, but my table handles that.

Is that a little better?


--------------------
"Nuclear" Nick
____________
The top three reasons to hide code; 1) It's not your own. 2) It's your own, but it's so crappy you don't want anyone to see it. 3) The comments in your code would get you in a lot of trouble if ever made public.
Go to the top of the page
 
firlandsfarm
post Oct 12 2018, 08:57 AM
Post#7



Posts: 300
Joined: 28-April 02
From: Heathfield, England


Nick, sorry, but we have been in total agreement with each other from the start (that's what I thought I said in my reply to you). I'm sorry if after two explanations I still wasn't making myself clear but what you are proposing is exactly what I asked should be the structure in the 2nd para of my original post ... "2 records per share/date" i.e. one record for the Open Price and one record for the Close Price. I have no interest in inbetween times. But I still think one record across the table with a single iif to find the maximum is the more efficient ... normalise, provided it's not stupid!

--------------------
Never doubt the courage of the French - they discovered that snails are edible!
Go to the top of the page
 
nuclear_nick
post Oct 12 2018, 09:19 AM
Post#8



Posts: 1,619
Joined: 5-February 06
From: Ohio, USA


I get that a lot... people speaking English and I'm explaining in 'Nick-ese'.

fundrink.gif

At least I hope I helped out on the normalization issue.

--------------------
"Nuclear" Nick
____________
The top three reasons to hide code; 1) It's not your own. 2) It's your own, but it's so crappy you don't want anyone to see it. 3) The comments in your code would get you in a lot of trouble if ever made public.
Go to the top of the page
 
projecttoday
post Oct 12 2018, 11:04 AM
Post#9


UtterAccess VIP
Posts: 10,166
Joined: 10-February 04
From: South Charleston, WV


I think Jeff was being, what's the word, facetious?

--------------------
Robert Crouser
Go to the top of the page
 
Jeff B.
post Oct 13 2018, 07:34 AM
Post#10


UtterAccess VIP
Posts: 10,140
Joined: 30-April 10
From: Pacific NorthWet


<Robert>

?!Moi?!

--------------------
Regards

Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page
 
projecttoday
post Oct 13 2018, 07:45 AM
Post#11


UtterAccess VIP
Posts: 10,166
Joined: 10-February 04
From: South Charleston, WV


Oui, monsieur ... TOI!

--------------------
Robert Crouser
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    19th October 2018 - 08:58 PM