[AccessD] Need some help.

Thanks Steve,
I was wondering if it was too big an order since it took awhile for
someone to answer. I have searched around on the web and found a little
code that may help. I have saved queries that do what I want manually
but I was wondering if I couldn't use some code to automate that process
to really make it easy.

I have a Make Table query that gets the week data by entering the ask
for parameters of date start and end. Then I use the same query as an
append query to get the next 3 or 4 weeks depending on a 4 or 5 week
month. I have another query to run against this table to combine it to
consolidate down to each job total. I have another query that sorts the
table down to profit center totals and append it to that table. These
last two query's puts in the data format I need to export to Excel and
then import to the GL.

I am a lazy guy and would rather hit one button and have it done than
set there and type in the date's and open the next query's then open the
table and copy and paste to excel. We have the start and end dates for
the month in a table and it seems easy to open that get start date add 6
days pass start and end for week to query and have it start the process
get the next 7 days till it gets to end of month and then grab next
query etc.
I'm going to try something and see if I can get a start anyway. Thanks
again for your help.
 Steve A. Turner

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Steve Erbach
Sent: Friday, September 26, 2008 12:53 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Need some help.

Steve,

That's a bit of a tall order, but I may have something along the lines
you're looking for.  I wrote a custom billing application for a
Builders Exchange that generates invoices.  The billing process starts
with choosing the billing month and then the user lets 'er rip.  All
the table backups and querying are done in a string of processes that
ends with a report containing the entire set of invoices to print and
mail.

All of the queries are "in-line"; that is, I don't use any saved
queries in the VBA code.    If I need to run a summary query to get a
value or two for further processing it'll look something like this:

530        strSQL1 = ""
540        strSQL1 = strSQL1 & "SELECT DISTINCT tblBillcode.[DuesCode
search string] "
550        strSQL1 = strSQL1 & "FROM tblBillcode "
560        strSQL1 = strSQL1 & "WHERE (((tblBillcode.Billmon)='" &
strBillMonth & "'));"

570        Debug.Print "Extract DuesCode search string from Billcode -
001"
580        Debug.Print strSQL1

590        Set rstDss = dbs.OpenRecordset(strSQL1)

600        strDss = rstDss![DuesCode search string]
610        rstDss.close
620        Set rstDss = Nothing

Then I'll use strDss in a query that creates a table:

650        strSQL2 = ""
660        strSQL2 = strSQL2 & "SELECT DISTINCT Membfile.[Acct#],
Membfile.[Member#], "
670        strSQL2 = strSQL2 & "Membfile.DuesCode, Membfile.[Company
Name] "
680        strSQL2 = strSQL2 & "INTO tmpTblMemb "
690        strSQL2 = strSQL2 & "FROM Membfile "
700        strSQL2 = strSQL2 & "WHERE ((" & strDss & "));"

710        Debug.Print "Select Members whose DuesCode equals what we
just extracted in 001 - 002"
720        Debug.Print strSQL2

730        DoCmd.RunSQL strSQL2

This query creates an intermediate result table that's used further
down the line, etc.

Does this help?

If you're going to use saved queries, then you can create a QueryDef
object and refer to the name of your saved query and use the
DoCmd.RunSQL command to execute it, if I remember correctly.

Steve Erbach
Neenah, WI


On Thu, Sep 25, 2008 at 1:32 PM, Steve Turner <sturner at mseco.com> wrote:
>
>
> Hello List,
>
> I am using A2k with SQL Express, Excel, and other linked tables. The
> main DB is in SQL Express. We have a custom timesheet program that
puts
> Time, Charges, Job numbers worked on in it. I used to export data to
> Lotus 2.1 using A2k and calculate cost for each Job to make entries to
> the GL. I have now written Query's to extract the data, calculate the
> cost and export to Excel so I can import to the GL. Calculations are
> done by the week because of Salaried Costs and the hours worked causes
> the Cost to change each week. I have a Query that sums the week by Job
> based on entered dates. I made a copy as a Make Table Query and a Copy
> to be an Append Query that writes the 4 or 5 weeks to a table to use
> another query to summarize by Job for the Month. I also have a Query
> that summarizes the data to 10 profit centers 0-9 as negative amounts
to
> offset the GL.
>
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com