Loop through query and export records on a date by date basis asseperate files...

[Need2Know]

I have a query detailing all transactions. I'd like to loop through
the query and export transactions on a day by day basis to seperate
files. For example: all transactions for Date1 would be saved to
File1, Date2 to File2, Date3 to File3, etc. any assistance is greatly
appreciated. Thank you all in advance.

 

[John W. Vinson]

I have a query detailing all transactions. I'd like to loop through
the query and export transactions on a day by day basis to seperate
files. For example: all transactions for Date1 would be saved to
File1, Date2 to File2, Date3 to File3, etc. any assistance is greatly
appreciated. Thank you all in advance.

Are these dates in separate *records* (with a date/time field indicating the
date), or separate *fields*? Please post the SQL view of your query.

I'd use a parameter query to extract just the one date's data and export that
query, probably in a loop called from VBA code - but I'll need to know the
structure of the data to suggest how.

 

[Need2Know]

Are these dates in separate *records* (with a date/time field indicating the
date), or separate *fields*? Please post the SQL view of your query.

I'd use a parameter query to extract just the one date's data and export that
query, probably in a loop called from VBA code - but I'll need to know the
structure of the data to suggest how.

Hello John,

Thank you for your quick reply. My query is below:

SELECT tbl_TXN.[PORTFOLIO CODE], IIf(IsNull(tbl_txn.[CUSIP OR LOAN
#]),"NOTRANSCTION",tbl_txn.[CUSIP OR LOAN #]) AS [CUSIPLOAN#], tbl_TXN.
[TRADE DATE], tbl_TXN.[TIME STAMP], IIf(IsNull(IIf(IsNull(tbl_hld!
[cusip or loan #]) And tbl_txn![cusip or loan #]<>"NOTRANSACTION" And
[TRANS TYPE]="Income","PORTIncome",IIf(IsNull(tbl_hld![cusip or loan
#]) And tbl_txn![cusip or loan #]<>"NOTRANSACTION" And [TRANS TYPE]
="Expense","PORTExpense",[TRANS TYPE]))),"NOTRANSACTION",IIf(IsNull
(tbl_hld![cusip or loan #]) And tbl_txn![cusip or loan #]
<>"NOTRANSACTION" And [TRANS TYPE]="Income","PORTIncome",IIf(IsNull
(tbl_hld![cusip or loan #]) And tbl_txn![cusip or loan #]
<>"NOTRANSACTION" And [TRANS TYPE]="Expense","PORTExpense",[TRANS
TYPE]))) AS TRANSTYPE, tbl_TXN.[SECURITY CURRENCY], tbl_TXN.[CASH
BALANCE], tbl_TXN.[QUANTITY (UNITS)], tbl_TXN.[ACCRUED INT PUR/SOLD
BASE], tbl_TXN.[TOTAL AMOUNT BASE], tbl_TXN.[ACCRUED INT PUR/SOLD
LOCAL], tbl_TXN.[TOTAL AMOUNT LOCAL], tbl_TXN.[INVESTMENT TYPE],
tbl_TXN.[TRANS NUMBER], tbl_TXN.[RM NUMBER], tbl_TXN.[BROKER NAME],
tbl_TXN.[TRANSACTION REMARKS], tbl_TXN.[MKT VALUE BASE], tbl_TXN.[MKT
VALUE LOCAL], tbl_TXN.[TRAN DATE]
FROM tbl_TXN LEFT JOIN tbl_HLD ON (tbl_TXN.[TRADE DATE] = tbl_HLD.[AS
OF DATE]) AND (tbl_TXN.[CUSIP OR LOAN #] = tbl_HLD.[CUSIP OR LOAN #])
AND (tbl_TXN.[PORTFOLIO CODE] = tbl_HLD.[PORTFOLIO CODE])
ORDER BY tbl_TXN.[PORTFOLIO CODE], IIf(IsNull(tbl_txn.[CUSIP OR LOAN
#]),"NOTRANSCTION",tbl_txn.[CUSIP OR LOAN #]), tbl_TXN.[TRAN DATE];

 

[Ken Snell MVP]

Create a Query and Export multiple "filtered" versions of a Query (based on
data in another table) to separate EXCEL files via TransferSpreadsheet (VBA)
http://www.accessmvp.com/KDSnell/EXCEL_Export.htm#FilterExportSepFiles

 

[Nurse Nancy]

Hi
First of All, Ken, thanks so much for making these available to everyone,,
you have saved a lot of people many hours of work!!!!

I have looked at Ken's Examples for Exporting to EXCEL Workbook Files-- and
I am a bit confused if this is what I need.

I already have a query built that has a paramater asking the user which Rep
to filter by,, or if they leave it blank, it displays all Reps.

i want to be able to run the query and export one file for each rep if they
leave it blank

Can anyone tell me which of Kens examples if any,, is the right solution.

HERE is my sql

SELECT RS.[Rep ID], RBPT.[Week Start Date], RBPT.STATION, M.Market,
RBPT.[Customer ID], RBPT.[Product Name], RBPT.[Flight Start Date],
RBPT.[Flight End Date], RBPT.[DR Rep], RBPT.[Primary Day Part] AS [Prim DP],
RBPT.[Rotator Day Part], RBPT.[Other Day Part], RBPT.[Opt In], RS.[Format
ID], RBPT.[# Prime Spots], RBPT.[# Rot Spots], RBPT.[# Other Spots],
CON.[E-mail Address], CON.[Mobile Phone] AS Cell, Sum(([Prime Rate]*[# Prime
Spots]+[Rot Rate]*[# Rot Spots]+[RBPT.Other Rate]*[# Other Spots])) AS Total,
RBPT.[Prime Rate], RBPT.[Rot Rate], RBPT.[Other Rate], M.[State ID],
RS.[Station Comments], Buy.[Buy Status], RBPT.[Buy Status Comments], [REP
TABLE].[Rep Contact_Main], [REP TABLE].[Rep Contact_Second], RS.[Contact ID],
RS.[Contact Full Name]
FROM ((([Contacts Merged Table] AS CON RIGHT JOIN ([RADIO BUY AND POST
TABLE] AS RBPT LEFT JOIN [RADIO STATIONS TABLE] AS RS ON RBPT.STATION =
RS.[Station Call Letters]) ON CON.[Contact ID] = RS.[Contact ID]) LEFT JOIN
[Buy Status Table] AS Buy ON RBPT.[Buy Status] = Buy.[Buy Status]) LEFT JOIN
[MARKETS TABLE] AS M ON RS.[Market ID] = M.[Market ID]) INNER JOIN [REP
TABLE] ON RBPT.[Station Rep] = [REP TABLE].[Rep ID]
GROUP BY RS.[Rep ID], RBPT.[Week Start Date], RBPT.STATION, M.Market,
RBPT.[Customer ID], RBPT.[Product Name], RBPT.[Flight Start Date],
RBPT.[Flight End Date], RBPT.[DR Rep], RBPT.[Primary Day Part], RBPT.[Rotator
Day Part], RBPT.[Other Day Part], RBPT.[Opt In], RS.[Format ID], RBPT.[#
Prime Spots], RBPT.[# Rot Spots], RBPT.[# Other Spots], CON.[E-mail Address],
CON.[Mobile Phone], RBPT.[Prime Rate], RBPT.[Rot Rate], RBPT.[Other Rate],
M.[State ID], RS.[Station Comments], Buy.[Buy Status], RBPT.[Buy Status
Comments], [REP TABLE].[Rep Contact_Main], [REP TABLE].[Rep Contact_Second],
RS.[Contact ID], RS.[Contact Full Name], RBPT.[Campaign ID], RBPT.Keep,
RS.[Contact ID]
HAVING (((RS.[Rep ID]) Like "*" & [Enter Rep Firm, or leave blank for ALL ]
& "*") AND ((RBPT.[Week Start Date])=[Forms]![Weekly Buy Table Form]![Week
Start Date]) AND ((RBPT.[Campaign ID])=[Forms]![Weekly Buy Table
Form]![Campaign ID]) AND ((RBPT.Keep)=Yes))
ORDER BY RS.[Rep ID], RBPT.STATION;




Nancy

 

[Ken Snell]

Nancy -

This example:

Create a Query and Export multiple "filtered" versions of a Query (based on
data in another table) to separate EXCEL files via TransferSpreadsheet (VBA)
http://www.accessmvp.com/KDSnell/EXCEL_Export.htm#FilterExportSepFiles

assumes that you want to export filtered data, filtered by specific values
in a table (in your case, the Reps), to EXCEL files. It does not include the
ability *as written* for you to enter a parameter or not enter a parameter.

You could use an InputBox function to ask which Rep you want to filter on,
and if no value is provided, then export all Reps' data, filtered to
separate workbooks. The VBA example code that I posted can be modified to do
this (assuming that you remove the parameter from the query, so that it
won't prompt the user for it as well). If you need assistance with doing
this, post back with the code that you've set up, or with specific
information about the query and table names that contain the appropriate
data.
--

Ken Snell
http://www.accessmvp.com/KDSnell/

Hi
First of All, Ken, thanks so much for making these available to everyone,,
you have saved a lot of people many hours of work!!!!

I have looked at Ken's Examples for Exporting to EXCEL Workbook Files--
and
I am a bit confused if this is what I need.

I already have a query built that has a paramater asking the user which
Rep
to filter by,, or if they leave it blank, it displays all Reps.

i want to be able to run the query and export one file for each rep if
they
leave it blank

Can anyone tell me which of Kens examples if any,, is the right solution.

HERE is my sql

SELECT RS.[Rep ID], RBPT.[Week Start Date], RBPT.STATION, M.Market,
RBPT.[Customer ID], RBPT.[Product Name], RBPT.[Flight Start Date],
RBPT.[Flight End Date], RBPT.[DR Rep], RBPT.[Primary Day Part] AS [Prim
DP],
RBPT.[Rotator Day Part], RBPT.[Other Day Part], RBPT.[Opt In], RS.[Format
ID], RBPT.[# Prime Spots], RBPT.[# Rot Spots], RBPT.[# Other Spots],
CON.[E-mail Address], CON.[Mobile Phone] AS Cell, Sum(([Prime Rate]*[#
Prime
Spots]+[Rot Rate]*[# Rot Spots]+[RBPT.Other Rate]*[# Other Spots])) AS
Total,
RBPT.[Prime Rate], RBPT.[Rot Rate], RBPT.[Other Rate], M.[State ID],
RS.[Station Comments], Buy.[Buy Status], RBPT.[Buy Status Comments], [REP
TABLE].[Rep Contact_Main], [REP TABLE].[Rep Contact_Second], RS.[Contact
ID],
RS.[Contact Full Name]
FROM ((([Contacts Merged Table] AS CON RIGHT JOIN ([RADIO BUY AND POST
TABLE] AS RBPT LEFT JOIN [RADIO STATIONS TABLE] AS RS ON RBPT.STATION =
RS.[Station Call Letters]) ON CON.[Contact ID] = RS.[Contact ID]) LEFT
JOIN
[Buy Status Table] AS Buy ON RBPT.[Buy Status] = Buy.[Buy Status]) LEFT
JOIN
[MARKETS TABLE] AS M ON RS.[Market ID] = M.[Market ID]) INNER JOIN [REP
TABLE] ON RBPT.[Station Rep] = [REP TABLE].[Rep ID]
GROUP BY RS.[Rep ID], RBPT.[Week Start Date], RBPT.STATION, M.Market,
RBPT.[Customer ID], RBPT.[Product Name], RBPT.[Flight Start Date],
RBPT.[Flight End Date], RBPT.[DR Rep], RBPT.[Primary Day Part],
RBPT.[Rotator
Day Part], RBPT.[Other Day Part], RBPT.[Opt In], RS.[Format ID], RBPT.[#
Prime Spots], RBPT.[# Rot Spots], RBPT.[# Other Spots], CON.[E-mail
Address],
CON.[Mobile Phone], RBPT.[Prime Rate], RBPT.[Rot Rate], RBPT.[Other Rate],
M.[State ID], RS.[Station Comments], Buy.[Buy Status], RBPT.[Buy Status
Comments], [REP TABLE].[Rep Contact_Main], [REP TABLE].[Rep
Contact_Second],
RS.[Contact ID], RS.[Contact Full Name], RBPT.[Campaign ID], RBPT.Keep,
RS.[Contact ID]
HAVING (((RS.[Rep ID]) Like "*" & [Enter Rep Firm, or leave blank for
ALL ]
& "*") AND ((RBPT.[Week Start Date])=[Forms]![Weekly Buy Table Form]![Week
Start Date]) AND ((RBPT.[Campaign ID])=[Forms]![Weekly Buy Table
Form]![Campaign ID]) AND ((RBPT.Keep)=Yes))
ORDER BY RS.[Rep ID], RBPT.STATION;




Nancy

Create a Query and Export multiple "filtered" versions of a Query (based
on
data in another table) to separate EXCEL files via TransferSpreadsheet
(VBA)
http://www.accessmvp.com/KDSnell/EXCEL_Export.htm#FilterExportSepFiles

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/