Plugging todays today into Table Name

Plugging todays today into Table Name

I use the following query to generate a list of addresses to mail to.  I'd 
like to be able to put the creation date into the name of the table (e.g. 
INTO [20080815 FCP S1 Postcards to Send].)   I realize I could do this 
manually every time I run it, but I'd like to be able to do it 
programatically.  If this is possible, could someone give me a pointer as to 
how to accomplish this?  I want to be able to create the table name at run 
time.

SELECT [GES].[ID], [GES].[CaseID], [GES].[Account], [GES].[OwnerName], 
[GES].[PropAddr], [GES].[MailAddr1] AS Address1, [GES].[MailAddr2], 
[GES].[MailCity], [GES].[MailState], [GES].[MailZIP], [GES].[DtUpdated], 
[GES].[DtAdded], [GES].[DtLtrSent], [GES].[DocStatus], [GES].[EstSellDate] 
INTO [FCP S1 Postcards to Send]
FROM GES
WHERE ((([GES].[DtLtrSent])=Date()) And (([GES].[DocStatus])="S2"))
ORDER BY [GES].[ID];

date: Sat, 16 Aug 2008 20:00:57 +0100 author: George

Re: Plugging todays today into Table Name

On Aug 16, 2:00 pm, "George"  wrote:
> I use the following query to generate a list of addresses to mail to.  I'd
> like to be able to put the creation date into the name of the table (e.g.
> INTO [20080815 FCP S1 Postcards to Send].)   I realize I could do this
> manually every time I run it, but I'd like to be able to do it
> programatically.  If this is possible, could someone give me a pointer as to
> how to accomplish this?  I want to be able to create the table name at run
> time.
>
> SELECT [GES].[ID], [GES].[CaseID], [GES].[Account], [GES].[OwnerName],
> [GES].[PropAddr], [GES].[MailAddr1] AS Address1, [GES].[MailAddr2],
> [GES].[MailCity], [GES].[MailState], [GES].[MailZIP], [GES].[DtUpdated],
> [GES].[DtAdded], [GES].[DtLtrSent], [GES].[DocStatus], [GES].[EstSellDate> INTO [FCP S1 Postcards to Send]
> FROM GES
> WHERE ((([GES].[DtLtrSent])=Date()) And (([GES].[DocStatus])="S2"))
> ORDER BY [GES].[ID];

Why do you need a table?  A table and a query both return the same
thing: a bunch of records.  If all you wanted to do was log the fact
that you had sent something to a list of people in your address list,
you could just as easily create a cartesian product of people
(filtered)
mailings (filtered)
and then insert the (personID, MailingID, Date()) into the
SentMailings table.

date: Sat, 16 Aug 2008 15:27:11 -0700 (PDT) author: unknown

Re: Plugging todays today into Table Name

wrote in message 
news:f8133ace-a6a3-4817-ac4e-0be51382600d@k30g2000hse.googlegroups.com...
On Aug 16, 2:00 pm, "George"  wrote:
> I use the following query to generate a list of addresses to mail to. I'd
> like to be able to put the creation date into the name of the table (e.g.
> INTO [20080815 FCP S1 Postcards to Send].) I realize I could do this
> manually every time I run it, but I'd like to be able to do it
> programatically. If this is possible, could someone give me a pointer as 
> to
> how to accomplish this? I want to be able to create the table name at run
> time.
>
> SELECT [GES].[ID], [GES].[CaseID], [GES].[Account], [GES].[OwnerName],
> [GES].[PropAddr], [GES].[MailAddr1] AS Address1, [GES].[MailAddr2],
> [GES].[MailCity], [GES].[MailState], [GES].[MailZIP], [GES].[DtUpdated],
> [GES].[DtAdded], [GES].[DtLtrSent], [GES].[DocStatus], [GES].[EstSellDate]
> INTO [FCP S1 Postcards to Send]
> FROM GES
> WHERE ((([GES].[DtLtrSent])=Date()) And (([GES].[DocStatus])="S2"))
> ORDER BY [GES].[ID];

Why do you need a table?  A table and a query both return the same
thing: a bunch of records.  If all you wanted to do was log the fact
that you had sent something to a list of people in your address list,
you could just as easily create a cartesian product of people
(filtered)
mailings (filtered)
and then insert the (personID, MailingID, Date()) into the
SentMailings table.

Because I have to upload the table into the USPS.com web site in  order to 
do my mailings, and I like to keep track of the the records that were sent 
on specific days.  Also, I'd like to be able to create a unique table name 
since I can create the same type of mail file on two consecutive days.  Then 
I can append that table to a table of sent records.

date: Sun, 17 Aug 2008 00:07:32 +0100 author: George

Re: Plugging todays today into Table Name

On Aug 16, 6:07 pm, "George"  wrote:
>  wrote in message
>
> news:f8133ace-a6a3-4817-ac4e-0be51382600d@k30g2000hse.googlegroups.com...
> On Aug 16, 2:00 pm, "George"  wrote:
>
>
>
> > I use the following query to generate a list of addresses to mail to. I'd
> > like to be able to put the creation date into the name of the table (e.g.
> > INTO [20080815 FCP S1 Postcards to Send].) I realize I could do this
> > manually every time I run it, but I'd like to be able to do it
> > programatically. If this is possible, could someone give me a pointer as
> > to
> > how to accomplish this? I want to be able to create the table name at run
> > time.
>
> > SELECT [GES].[ID], [GES].[CaseID], [GES].[Account], [GES].[OwnerName],
> > [GES].[PropAddr], [GES].[MailAddr1] AS Address1, [GES].[MailAddr2],
> > [GES].[MailCity], [GES].[MailState], [GES].[MailZIP], [GES].[DtUpdated]> > [GES].[DtAdded], [GES].[DtLtrSent], [GES].[DocStatus], [GES].[EstSellDate]
> > INTO [FCP S1 Postcards to Send]
> > FROM GES
> > WHERE ((([GES].[DtLtrSent])=Date()) And (([GES].[DocStatus])="S2"))
> > ORDER BY [GES].[ID];
>
> Why do you need a table?  A table and a query both return the same
> thing: a bunch of records.  If all you wanted to do was log the fact
> that you had sent something to a list of people in your address list,
> you could just as easily create a cartesian product of people
> (filtered)
> mailings (filtered)
> and then insert the (personID, MailingID, Date()) into the
> SentMailings table.
>
> Because I have to upload the table into the USPS.com web site in  order to
> do my mailings, and I like to keep track of the the records that were sent
> on specific days.  Also, I'd like to be able to create a unique table name
> since I can create the same type of mail file on two consecutive days.  Then
> I can append that table to a table of sent records.

You could do it something like this:

Public Function CreateMyTable()
    Dim strSQL As String

'---build the SQL statement to create the table
    strSQL = "SELECT [GES].[ID], [GES].[CaseID], [GES].[Account],
[GES].[OwnerName], "
    strSQL = strSQL & "[GES].[MailCity], [GES].[MailState], [GES].
[MailZIP], [GES].[DtUpdated], "
    strSQL = strSQL & "[GES].[DtAdded] , [GES].[DtLtrSent], [GES].
[DocStatus], [GES].[EstSellDate] "
    strSQL = strSQL & "INTO [FCP S1 Postcards to Send " & Format(Date,
"mmm-dd-yyyy") & "] "
    strSQL = strSQL & "FROM GES "
    strSQL = strSQL & "WHERE ((([GES].[DtLtrSent]) = Date) And (([GES].
[DocStatus]) = 'S2')) ORDER BY [GES].[ID];"

'---execute the statement
    dbengine(0)(0).Execute strSQL

End Function

date: Sat, 16 Aug 2008 18:23:11 -0700 (PDT) author: unknown