ACCESS FEED

Selasa, 03 Juli 2018

Re: [MS_AccessPros] Query: Make EndDateTime of a record the StartDateTime of next record

Here is the SQL that should calculate the end times.

SELECT Orders.Product, Orders.Description, Orders.Order, Orders.Qty, [Qty]*[Minutes] AS TotalMinutes, (SELECT MIN(ProductionDate) FROM Orders)+(SELECT Sum(P.Minutes * O.Qty) FROM Orders O INNER JOIN Products P ON O.Product = P.Product WHERE O.Order <=Orders.Order)/(24*60) AS EndDate

FROM Orders INNER JOIN Products ON Orders.Product = Products.Product

ORDER BY Orders.Order;

I hope you can modify the EndDate expression to get the start times. I think it might be just a duplication and change the <= to < but I didn't try.

I also expect your data has more than a single product and dates which won't be accounted for with this expression. You may need to add more criteria in the subquery to accommodate more dates and products.

Duane

From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of lrheimpel@gmail.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Sent: Monday, July 2, 2018 9:25 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Query: Make EndDateTime of a record the StartDateTime of next record

Duane,

Here is a simplified version to make it easier to see.

Tables are linked by column product. Minutes are calculated by multiplying Product Table minutes of product times order qty.

Date format I use is mm/dd/yyyy if that makes a difference.

Current Output
Product	Description	Order	Qty	ProductionDate	Minutes	StartDateTime	EndDateTime
P01	Photo Album	1	5	7/3/2018	120	7/3/2018 6:00	7/3/18 8:00
P01	Photo Album	2	15	7/3/2018	145	7/3/2018 6:00	7/3/18 8:25
P01	Photo Album	3	10	7/3/2018	36	7/3/2018 6:00	7/3/18 6:36

Desired Output
Product	Description	Order	Qty	ProductionDate	Minutes	StartDateTime	EndDateTime
P01	Photo Album	1	5	7/3/2018	120	7/3/2018 6:00	7/3/18 8:00
P01	Photo Album	2	15	7/3/2018	360	7/3/2018 8:00	7/3/18 14:00
P01	Photo Album	3	10	7/3/2018	240	7/3/2018 14:00	7/3/18 18:00

Table Products
Product	Description	Minutes
P01	Photo Album	24

Table Orders
Product	Description	Order	Qty	ProductionDate
P01	Photo Album	1	5	7/3/2018
P01	Photo Album	2	15	7/3/2018
P01	Photo Album	3	10	7/3/2018

__._,_.___

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

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

New Members 1

• Privacy • Unsubscribe • Terms of Use

__,_._,___

Senin, 02 Juli 2018

RE: [MS_AccessPros] sending multiple attachments through Lotusmaill

Thanks Bill. I just got this, but I realized I just needed to use an array.

Private Sub PrepAndSendEmail()
On Error GoTo err_out
Dim db As DAO.Database
Dim rsTo As DAO.Recordset
Dim rsAtt As DAO.Recordset
Dim strsql As String
Set db = CurrentDb()
' get the email list
Set rsTo = db.OpenRecordset("EmailList")
Dim strTo As String
While Not rsTo.EOF
strTo = rsTo(0).Value & "; " & strTo
rsTo.MoveNext
Wend

' get the list of attachments to send
Set rsAtt = db.OpenRecordset("pdfFiles")
rsAtt.MoveLast
Dim i As Integer ' for the item in the attachment array
i = rsAtt.RecordCount
ReDim stratt(i) As String
rsAtt.MoveFirst
i = 0

While Not rsAtt.EOF
stratt(i) = rsAtt(0).Value
i = i + 1
rsAtt.MoveNext
Wend
' stratt = stratt & "'"
' stratt = Left(stratt, Len(stratt) - 3)
' stratt = "array(" & stratt & ")"

If EmailSent("PlannerReports@beaerospace.com", strTo, "Planner reporting test only", "Soon to be implemented", stratt) Then
Me.txtStatus = "Emails have been sent"
Else
Me.txtStatus = "Emails were not sent"
GoTo err_out
End If

'If EmailSent("test@beaerospace.com", strTo, "test", "test", "k:\Production Control\Program Folder\PlannerRptsFromDB\NEXGEN PS PO-6-27.pdf") Then

GoTo exit_out
err_out:
MsgBox ("Error in PrepAndSendEmail: " & Err.Number & vbCrLf & Err.Description)
exit_out:
Set rsTo = Nothing
Set rsAtt = Nothing
Set db = Nothing
End Sub

Respectfully,
[RCEmailSigLogo]
Liz Ravenwood
Data Technologies
Interior Systems
Rockwell Collins
1851 So. Pantano Rd, Tucson, AZ 85710 USA
(520) 239-4808
Liz_ravenwood@beaerospace.com<mailto:Liz_ravenwood@beaerospace.com>
rockwellcollins.com<http://www.rockwellcollins.com/>

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Monday, July 2, 2018 9:17 AM
To: Liz Ravenwood liz_ravenwood@beaerospace.com [MS_Access_Professionals]; Home
Subject: Re: [MS_AccessPros] sending multiple attachments through Lotusmaill

Pop. I see you are using Lotus notes. But maybe you can edit your code after seeing mine

Regards,
Bill Mosca

------ Original message------
From: Liz Ravenwood liz_ravenwood@beaerospace.com<mailto:%20liz_ravenwood@beaerospace.com> [MS_Access_Professionals]
Date: Mon, Jul 2, 2018 8:13 AM
To: MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals@yahoogroups.com>;
Cc:
Subject:[MS_AccessPros! ] sending multiple attachments through Lotusmaill

Friends, I need to email multiple attachments, and the code runs but fails to send anything. I can send one attachment only.

Anyone know how to do this?

Here's the function:

Public Function EmailSent(strFrom As String, strTo As String, strSub As String, strBody As String, Optional strAttach As Variant) As Boolean
On Error GoTo err_out

Dim L As LotusMail.Utility, Output As String
Set L = New LotusMail.Utility
' FROM, TO, Subject, Body, Attachment
Output = L.Se<http://L.Se>ndMail(strFrom, strTo, strSub, strBody, strAttach)
Set L = Nothing
EmailSent = True

GoTo exit_out
err_out:
EmailSent = False
exit_out:
'bye
End Function

And here is the calling code:

Private Sub PrepAndSendEmail()
On Error GoTo err_out
Dim db As DAO.Database
Dim rsTo As DAO.Re<http://DAO.Re>cordset
Dim rsAtt As DAO.Re<http://DAO.Re>cordset
Dim strsql As String
Set db = CurrentDb()
Set rsTo = db.OpenRecordset("EmailList")
Dim strTo As String
While Not rsTo.EOF
strTo = rsTo(0).Value & "; " & strTo
rsTo.Mo<http://rsTo.Mo>veNext
Wend
Set rsAtt = db.OpenRecordset("pdfFiles")
Dim strAtt As String
strAtt = ""
While Not rsAtt.EOF
strAtt = "'" & rsAtt(0).Value & "', " & strAtt
rsAtt.Mo<http://rsAtt.Mo>veNext
Wend
strAtt = strAtt & "'"
strAtt = Left(strAtt, Len(strAtt) - 3)
strAtt = "array(" & strAtt & ")"

If EmailSent("PlannerReports@beaerospace.com<mailto:>", strTo, "Planner reporting test only", "Soon to be implemented", strAtt) Then
Me.txtStatus = "Emails have been sent"
Else
Me.txtStatus = "Emails were not sent"
GoTo err_out
End If

'If EmailSent("test@beaerospace.com<mailto:>", strTo, "test", "test", "k:\Production Control\Program Folder\PlannerRptsFromDB\NEXGEN PS PO-6-27.pdf") Then

GoTo exit_out
err_out:
MsgBox ("Error in PrepAndSendEmail: " & Err.Nu<http://Err.Nu>mber & vbCrLf & Err.De<http://Err.De>scription)
exit_out:
Set rsTo = Nothing
Set rsAtt = Nothing
Set db = Nothing
End Sub

Respectfully,
[RCEmailSigLogo]
Liz Ravenwood
Data Technologies
Interior Systems
Rockwell Collins
1851 So. Pantano Rd, Tucson, AZ 85710 USA
(520) 239-4808<tel:(520)%20239-4808>
Liz_ravenwood@beaerospace.com<mailto:Liz_ravenwood@beaerospace.com><mailto:Liz_ravenwood@beaerospace.com></mailto<mailto::Liz_ravenwood@beaerospace.com>
rockwellcollins.com<http://rockwellcollins.com><http://www.rockwellcollins.com/>

This email (and all attachments) is for the sole use of the intended recipient(s) and may contain privileged and/or proprietary information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message.

[Non-text portions of this message have been removed]

This email (and all attachments) is for the sole use of the intended recipient(s) and may contain privileged and/or proprietary information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message.

[Non-text portions of this message have been removed]

__._,_.___

Posted by: Liz Ravenwood <Liz_Ravenwood@beaerospace.com>

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (4)

Have you tried the highest rated email app?

Visit Your Group

New Members 1

• Privacy • Unsubscribe • Terms of Use

__,_._,___

Re: [MS_AccessPros] sending multiple attachments through Lotusmaill

Pop. I see you are using Lotus notes. But maybe you can edit your code after seeing mine

Regards,

Bill Mosca

------ Original message------

From: Liz Ravenwood liz_ravenwood@beaerospace.com [MS_Access_Professionals]

Date: Mon, Jul 2, 2018 8:13 AM

To: MS_Access_Professionals@yahoogroups.com;

Cc:

Subject:[MS_AccessPros] sending multiple attachments through Lotusmaill

Friends, I need to email multiple attachments, and the code runs but fails to send anything. I can send one attachment only.

Anyone know how to do this?

Here's the function:

Public Function EmailSent(strFrom As String, strTo As String, strSub As String, strBody As String, Optional strAttach As Variant) As Boolean
On Error GoTo err_out

Dim L As LotusMail.Utility, Output As String
Set L = New LotusMail.Utility
' FROM, TO, Subject, Body, Attachment
Output = L.SendMail(strFrom, strTo, strSub, strBody, strAttach)
Set L = Nothing
EmailSent = True

GoTo exit_out
err_out:
EmailSent = False
exit_out:
'bye
End Function

And here is the calling code:

Private Sub PrepAndSendEmail()
On Error GoTo err_out
Dim db As DAO.Database
Dim rsTo As DAO.Recordset
Dim rsAtt As DAO.Recordset
Dim strsql As String
Set db = CurrentDb()
Set rsTo = db.OpenRecordset("EmailList")
Dim strTo As String
While Not rsTo.EOF
strTo = rsTo(0).Value & "; " & strTo
rsTo.MoveNext
Wend
Set rsAtt = db.OpenRecordset("pdfFiles")
Dim strAtt As String
strAtt = ""
While Not rsAtt.EOF
strAtt = "'" & rsAtt(0).Value & "', " & strAtt
rsAtt.MoveNext
Wend
strAtt = strAtt & "'"
strAtt = Left(strAtt, Len(strAtt) - 3)
strAtt = "array(" & strAtt & ")"

If EmailSent("PlannerReports@beaerospace.com", strTo, "Planner reporting test only", "Soon to be implemented", strAtt) Then
Me.txtStatus = "Emails have been sent"
Else
Me.txtStatus = "Emails were not sent"
GoTo err_out
End If

'If EmailSent("test@beaerospace.com", strTo, "test", "test", "k:\Production Control\Program Folder\PlannerRptsFromDB\NEXGEN PS PO-6-27.pdf") Then

GoTo exit_out
err_out:
MsgBox ("Error in PrepAndSendEmail: " & Err.Number & vbCrLf & Err.Description)
exit_out:
Set rsTo = Nothing
Set rsAtt = Nothing
Set db = Nothing
End Sub

Respectfully,
[RCEmailSigLogo]
Liz Ravenwood
Data Technologies
Interior Systems
Rockwell Collins
1851 So. Pantano Rd, Tucson, AZ 85710 USA
(520) 239-4808
Liz_ravenwood@beaerospace.com:Liz_ravenwood@beaerospace.com>
rockwellcollins.com<http://www.rockwellcollins.com/>

This email (and all attachments) is for the sole use of the intended recipient(s) and may contain privileged and/or proprietary information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message.

[Non-text portions of this message have been removed]

__._,_.___

Posted by: wrmosca <wrmosca@comcast.net>

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?

Visit Your Group

New Members 1

• Privacy • Unsubscribe • Terms of Use

__,_._,___

Re: [MS_AccessPros] sending multiple attachments through Lotusmaill

Liz - check our files section. I wrote code for multiple attachments.

Regards,

Bill Mosca

------ Original message------

From: Liz Ravenwood liz_ravenwood@beaerospace.com [MS_Access_Professionals]

Date: Mon, Jul 2, 2018 8:13 AM

To: MS_Access_Professionals@yahoogroups.com;

Cc:

Subject:[MS_AccessPros] sending multiple attachments through Lotusmaill

__._,_.___

Posted by: wrmosca <wrmosca@comcast.net>

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?

Visit Your Group

New Members 1

• Privacy • Unsubscribe • Terms of Use

__,_._,___

[MS_AccessPros] sending multiple attachments through Lotusmaill

Friends, I need to email multiple attachments, and the code runs but fails to send anything. I can send one attachment only.

Anyone know how to do this?

Here's the function:

Public Function EmailSent(strFrom As String, strTo As String, strSub As String, strBody As String, Optional strAttach As Variant) As Boolean
On Error GoTo err_out

Dim L As LotusMail.Utility, Output As String
Set L = New LotusMail.Utility
' FROM, TO, Subject, Body, Attachment
Output = L.SendMail(strFrom, strTo, strSub, strBody, strAttach)
Set L = Nothing
EmailSent = True

GoTo exit_out
err_out:
EmailSent = False
exit_out:
'bye
End Function

And here is the calling code:

Private Sub PrepAndSendEmail()
On Error GoTo err_out
Dim db As DAO.Database
Dim rsTo As DAO.Recordset
Dim rsAtt As DAO.Recordset
Dim strsql As String
Set db = CurrentDb()
Set rsTo = db.OpenRecordset("EmailList")
Dim strTo As String
While Not rsTo.EOF
strTo = rsTo(0).Value & "; " & strTo
rsTo.MoveNext
Wend
Set rsAtt = db.OpenRecordset("pdfFiles")
Dim strAtt As String
strAtt = ""
While Not rsAtt.EOF
strAtt = "'" & rsAtt(0).Value & "', " & strAtt
rsAtt.MoveNext
Wend
strAtt = strAtt & "'"
strAtt = Left(strAtt, Len(strAtt) - 3)
strAtt = "array(" & strAtt & ")"

If EmailSent("PlannerReports@beaerospace.com", strTo, "Planner reporting test only", "Soon to be implemented", strAtt) Then
Me.txtStatus = "Emails have been sent"
Else
Me.txtStatus = "Emails were not sent"
GoTo err_out
End If

'If EmailSent("test@beaerospace.com", strTo, "test", "test", "k:\Production Control\Program Folder\PlannerRptsFromDB\NEXGEN PS PO-6-27.pdf") Then

GoTo exit_out
err_out:
MsgBox ("Error in PrepAndSendEmail: " & Err.Number & vbCrLf & Err.Description)
exit_out:
Set rsTo = Nothing
Set rsAtt = Nothing
Set db = Nothing
End Sub

Respectfully,
[RCEmailSigLogo]
Liz Ravenwood
Data Technologies
Interior Systems
Rockwell Collins
1851 So. Pantano Rd, Tucson, AZ 85710 USA
(520) 239-4808
Liz_ravenwood@beaerospace.com<mailto:Liz_ravenwood@beaerospace.com>
rockwellcollins.com<http://www.rockwellcollins.com/>

This email (and all attachments) is for the sole use of the intended recipient(s) and may contain privileged and/or proprietary information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message.

[Non-text portions of this message have been removed]

__._,_.___

Posted by: Liz Ravenwood <Liz_Ravenwood@beaerospace.com>

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (1)

Have you tried the highest rated email app?

Visit Your Group

New Members 1

• Privacy • Unsubscribe • Terms of Use

__,_._,___

Re: [MS_AccessPros] Query: Make EndDateTime of a record the StartDateTime of next record

Duane,

Here is a simplified version to make it easier to see.

Tables are linked by column product. Minutes are calculated by multiplying Product Table minutes of product times order qty.

Date format I use is mm/dd/yyyy if that makes a difference.

Current Output
Product	Description	Order	Qty	ProductionDate	Minutes	StartDateTime	EndDateTime
P01	Photo Album	1	5	7/3/2018	120	7/3/2018 6:00	7/3/18 8:00
P01	Photo Album	2	15	7/3/2018	145	7/3/2018 6:00	7/3/18 8:25
P01	Photo Album	3	10	7/3/2018	36	7/3/2018 6:00	7/3/18 6:36

Desired Output
Product	Description	Order	Qty	ProductionDate	Minutes	StartDateTime	EndDateTime
P01	Photo Album	1	5	7/3/2018	120	7/3/2018 6:00	7/3/18 8:00
P01	Photo Album	2	15	7/3/2018	360	7/3/2018 8:00	7/3/18 14:00
P01	Photo Album	3	10	7/3/2018	240	7/3/2018 14:00	7/3/18 18:00

Table Products
Product	Description	Minutes
P01	Photo Album	24

Table Orders
Product	Description	Order	Qty	ProductionDate
P01	Photo Album	1	5	7/3/2018
P01	Photo Album	2	15	7/3/2018
P01	Photo Album	3	10	7/3/2018

__._,_.___

Posted by: lrheimpel@gmail.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?

Visit Your Group

New Members 1

• Privacy • Unsubscribe • Terms of Use

__,_._,___

Minggu, 01 Juli 2018

Re: [MS_AccessPros] Query: Make EndDateTime of a record the StartDateTime of next record

It would really help if you provided some sample records with the desired output with field and table names.

I expect you could use either a subquery or DSum() to calculate the durations from the previous steps in the sequence.

Regards,

Duane Hookom

From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of lrheimpel@gmail.com [MS_Access_Professionals]

Hi,

I have a query that lists products in the sequence they are to be manufactured. I calculate the time it will take to make each product (multiply individual per piece time times the quantity, result is in minutes). I have 2 calculated columns at the end, one is StartDateTime (set to now()) and EndDateTime (calculated using dateadd function, adding the manufacturing minutes to the StartDateTime column. This tells me if I started all products at now(), I would complete each product at some calculated time.

Problem is, I make the products in sequence, not at the same time.

How can I make the EndDateTime of record number one, become the StartDateTime for record number two, EndDateTime of record number two the StartDateTime for record number three and so on all the way to the last record.

Any suggestions? I'll really appreciate this!

__._,_.___

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?

Visit Your Group

• Privacy • Unsubscribe • Terms of Use

__,_._,___