Re: [MS_AccessPros]

 

Hi Penny,

I attempted to upload a sample file to our group site Files->1_Samples but it appears the file didn't appear. The sample file has a function and query that shows how to use it to get he date difference between the next and last dates.

Give me a shout if you want the file.

Regards,

Duane

---

From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Sent: Wednesday, February 7, 2018 9:28 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros]

 

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]

 

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 (3)

---

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.

---

Visit Your Group

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___