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
> Duplicate Date Fields, Access 2013    
 
   
jokeme71
post Aug 27 2019, 01:43 PM
Post#1



Posts: 930
Joined: 7-June 02
From: Maryland


I know it goes against rules of normalization to have fields that are duplicated but I have a table that is really big and it takes a long time to run a query against. This issue occurs because the date field is in a strange format (short text format) that must be kept intact in the table. Because of this I have a function that converts this date to a true date that I can use for my date reliant queries. I am sure that because this function is being used against each record that I am taking a performance hit that is causing my queries to take forever. To mitigate this I am contemplating creating a new field that is a true date field; populating this field based on the original value when the records are appended to the table. Then using that new field for my queries. Is this the right way to go about this or is there a better way?
Go to the top of the page
 
theDBguy
post Aug 27 2019, 02:05 PM
Post#2


Access Wiki and Forums Moderator
Posts: 76,396
Joined: 19-June 07
From: SunnySandyEggo


Hi. Just checking, are we talking about an Access table? You could give it a test and see if it makes any difference.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
jokeme71
post Aug 27 2019, 02:09 PM
Post#3



Posts: 930
Joined: 7-June 02
From: Maryland


Yes, it is single access table. No relationships or foreign keys.
Go to the top of the page
 
theDBguy
post Aug 27 2019, 02:15 PM
Post#4


Access Wiki and Forums Moderator
Posts: 76,396
Joined: 19-June 07
From: SunnySandyEggo


Hi. Thanks for the clarification. Not sure why the dates are text in the first place but I guess you could either do it like you said and add a calculated field or do it the opposite way and convert the field into a date field and only calculate the text value when you need it.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
jokeme71
post Aug 27 2019, 02:24 PM
Post#5



Posts: 930
Joined: 7-June 02
From: Maryland


I had no choice but to import them into a short text format. Certainly not my choice. Otherwise the field values would not import into the table. Not sure why the producers of the data chose to export their data the way they did. That is a real headscratcher to me.
Go to the top of the page
 
tina t
post Aug 27 2019, 02:26 PM
Post#6



Posts: 6,122
Joined: 11-November 10
From: SoCal, USA


QUOTE
Otherwise the field values would not import into the table.

hmm, that's a bit surprising. Access usually handles text data pretty well, in an Import specification. are you importing the data from a text file of some kind? or something else?

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
jokeme71
post Aug 27 2019, 02:31 PM
Post#7



Posts: 930
Joined: 7-June 02
From: Maryland


CSV file. The format of the date even has extra spaces between the date and time.
Go to the top of the page
 
dale.fye
post Aug 27 2019, 02:31 PM
Post#8



Posts: 161
Joined: 28-March 18
From: Virginia


another option would be to have a date conversion table which you update with new values from the text field and have a true date field as the second field in that table. If you make the field that contains the text date the PK, then you should be able to join it to the other table and have it still be updateable.

Just an out-of-the box idea, but it would significantly improve the speed of your date related queries.

--------------------
Dale Fye
Microsoft Access MVP 2013-2016
Developing Solutions, LLC
Go to the top of the page
 
tina t
post Aug 27 2019, 07:38 PM
Post#9



Posts: 6,122
Joined: 11-November 10
From: SoCal, USA


QUOTE
CSV file. The format of the date even has extra spaces between the date and time.

hmm, doesn't sound good. but if you want to upload a small .csv file, after first replacing any proprietary data in the file with similar dummy data, and zipping the file (i think that is necessary), then i'll take a look at it - from an Import spec-building angle, just in case there's something we can fix or mickey-mouse.

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    19th October 2019 - 09:43 AM