Penny,
I think it's time to learn VBA or at least implement someone else's code. A non-code solution might consist of:
- Creating a normalizing union query of your primary key field and a date which could result in 1 to 7 rows being created for each current record
- Placing these normalized records in order
- Create a ranking query that numbers the dates in order for each of the primary key values
- Create a crosstab query of the ranked results with the dates in appropriate order
- Pulling the date diffs from the appropriate columns
Or someone could write a fairly simple function which you could implement.
Regards,
Duane Hookom
From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of Penny Price misspen1@gmail.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Sent: Wednesday, February 7, 2018 8:01 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros]
Sent: Wednesday, February 7, 2018 8:01 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros]
Hi Everyone,
After a couple of years away from my old Access database my employer once again has me creating a new one. I have a very simple db that is not normalized and is not relational (since I need any changes to be made to all queries, forms, etc.). I'm working in Access 2016, which is a big deal for us since we had Access '99 well into the mid 2000's! My one hang-up at the moment is a query I'm creating to show response time among several variable dates. I have seven different dates (Date1, Date2, Date3,…). Date1 will always have a value and at least one of the other dates will also have a value in this particular query. I do not want to include any rows that only have Date1 without any of the other dates having a value.. However, there could be up to three dates for each individual row: Date1, Date3, Date5 or Date1, Date2, Date6 for example. For each row of the query I need to calculate the number of days between Date1 and each of the variable dates: Days Till A: -DateDiff("d",[Date2],[Date1]) etc.. This part works fine, but I also need to find the DateDiff in each row for Date1 and the next earliest date; and then in a separate calculation, the difference between Date1 and the latest date.
The dates on individual rows could look something like this (but is not limited to):
Date1, Date2, Date3
Date1, Date3
Date1, Date4
Date1, Date5
Date1, Date2, Date6
The only constant for each row is Date1. Date1 is always the earliest date, but Date7 is not necessarily later than Date2, Date3,… Any ideas how I can accomplish this since I have never learned to write VBA? (Something for my retirement!)
As always, thanks in advance to all of you truly helpful people!
Penny
__._,_.___
Posted by: Duane Hookom <duanehookom@hotmail.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (2) |
Have you tried the highest rated email app?
With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.
.
__,_._,___
Tidak ada komentar:
Posting Komentar