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
> Trying To Make An Update Query To Add A Calculated Date, Access 2016    
 
   
bsm2th
post Oct 19 2019, 08:32 PM
Post#1



Posts: 6
Joined: 16-July 18



I have a database that has a table called Visits. It has the fields id, visit_month, visit_year, monthly, and new_date. new_date is for the first visit this fiscal year. There is a separate record for each id, visit_month and visit_year.

I need a query to find the latest month since July with a value of -1 in monthly, then use that visit_month and visit_year to create a date and put it into new_date for the rest of the year. This needed to populate a new field in an existing database. After created, the database will maintain it. I've tried a few ways. Having trouble with having one query use info from another and with adding dateserial to an update query. Just tried nested queries and got the same troubles. Also couldn't use max with an update query. Please help!!!

Attached File(s)
Attached File  visits_before.jpg ( 256.36K )Number of downloads: 5
Attached File  visits_after.jpg ( 264.97K )Number of downloads: 5
 
Go to the top of the page
 
GroverParkGeorge
post Oct 19 2019, 09:59 PM
Post#2


UA Admin
Posts: 36,043
Joined: 20-June 02
From: Newcastle, WA


Well, as you may have heard or read, we seldom recommend or encourage storing CALCULATED values like this.

So, for starters, I'd probably suggest you don't need to even do this. The same query you'd write to identify the value needed for the calculated value can be used anytime you want to DISPLAY that value in a form or report. Plus you don't risk having the calculated value get out of synch with reality.

So, based on your description of what you want, a query can be created that returns the date without the risk of storing it.




--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
theDBguy
post Oct 19 2019, 10:08 PM
Post#3


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


Hi. Welcome to UA! welcome2UA.gif

In addition to what George said about not storing calculated values, it is also seldom necessary to create "placeholder" records. I get the feeling this is what you're talking about when you said this:
QUOTE
...then use that visit_month and visit_year to create a date and put it into new_date for the rest of the year.
So, it sounds to me like you want to generate a bunch of records (future dates) to fill the year.

--------------------
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
 
bsm2th
post Oct 20 2019, 12:02 PM
Post#4



Posts: 6
Joined: 16-July 18



What this program does is record attendance at a foodbank I volunteer at. The visits table is for (you guessed it..) visits. It has a record for each month/year and each person. The regional foodbank that we get supplies from needs to know when a person comes for the first time this fiscal year. For that reason I was putting their start date into each record for this fiscal year. That way, reports could still be run years later and still get correct results. I can't use a calculated value for this, as that date will change each year. The only calculation I'm doing is max to find the first month this fiscal year that has the monthly field at -1, and using that month number to set the newdate field.

Thanks
Bob
Go to the top of the page
 
GroverParkGeorge
post Oct 20 2019, 12:38 PM
Post#5


UA Admin
Posts: 36,043
Joined: 20-June 02
From: Newcastle, WA


I see.

But as others have pointed out, this looks like the long way around the block for an Access Relational Database Application.

You probably want to record Transactions as they happen, not potential future transactions -- which frankly may or may not even occur. If a household visits for food in July, and again in September, but not in August, wouldn't you want to record the actual date in July and the actual date in September? And it would be potentially misleading to have a record for August, based on the projection created back in July, would it not?

I think the intent is great, and I'm sure you'll get the same results from a properly designed Access Relational Database Application, with a lot less head-ache.

In your Visits table, therefore, I think you want to record the actual date when a household visits you, whether that's the 1st of the month or the 30th. After all, people live their lives on an individual tempo and they may or may not get in on any given day of the month or of the week. What your staff needs to do, therefore, is enter the actual date of each visit. You can use basic Access date logic to do things like query for the Min() of those dates to find the month in which that household first visited your foodbank.

SQL
SELECT HouseHoldID, Format(Min([ActualVisitDate]), "mm/yyyy") AS FirstMonthVisited
FROM Visits GROUP BY HouseholdID


And that never changes, never has to be stored anywhere. Any time you need that report, you run that same query. It can be parameterized with a HouseholdID to limit results to a single Household, and so on.

Or, if you want to show a list of ALL visits ever made by that Household:

SQL
SELECT HouseHoldID, ActualVisitDate
FROM Visits ORDER BY HouseHoldID, ActualVisitDate


And again, that can be expanded on and parameterized if need be to limit results to a single household, or perhaps to a specific time period.

SQL
SELECT HouseHoldID, ActualVisitDate
FROM Visits WHERE ActualVisitDate Between #1/1/2019# and #12/31/2019#
ORDER BY HouseHoldID, ActualVisitDate


Change out the date range for the first and last days of your fiscal year, if need be.

In other words, when a task seems to call for a wad of fancy coding and or SQL, and some extra calculations and updates to records, it's probably not the most effective approach.

I see here a simple table that stores only necessary data points, and needs nothing more than the addition of one new record each time the household appears at your door.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
bsm2th
post Oct 21 2019, 06:50 PM
Post#6



Posts: 6
Joined: 16-July 18



That should help. Thanks.
Go to the top of the page
 
dmhzx
post Oct 22 2019, 03:39 AM
Post#7



Posts: 7,112
Joined: 22-December 10
From: England


What is the business case here.
Forget the tables for now, what is it you'er after?

It sounds like you're attempting some sort of forecasting exercise , in setting up when you expect someone to turn up based on when they've turned up in the past.

Note that GroverParkGeorge used the word "SELDOM", and I'm wondering if this might be one of those occasions where it is appropriate.


Whereas in general storing calculated fields is not advised, sometimes doing so represent the best approach based on a number of considerations: Not always just Access ones.

One of these is running time, others include complexity, supportability and the operational requirement.

In this case it looks to me as though you are recording when they were expected to turn up and when they did actually turn up, (hence the minus 1??) , so storing a record for the future could be what you actually need.

But I am struggling with the actual forecast side.
If a person starts coming to the food bank in July, and they then visit in September, and October, what would you want to see as their first anticipated visit next year?

So are we effectively talking about something akin to an appointment system, where we record appointments and actual attendance. , or is it just something you need for a quick forecast to your supplier?

Hope that hasn't muddied the water.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    18th November 2019 - 04:35 AM