SQL Help Needed

[Ikenest]

In Excel, I process weekly delinquent supplier data from a download, using
v-lookups. I believe that I could do better using the power of Microsoft
Access. The data is now an Access table.

Currently, I have 2 pivot tables that should feed from the supplier data
table.

Pivot table 1 displays info of delinquent suppliers info (columns) that are
available (delinquent) in the current week, i.e. ProcessedDate='1/5/2009',
and not available the previous week, i.e. ProcessedDate='12/29/2008', called
"OverDue accounts".

Pivot table 2 displays all suppliers info (columns) that were available
(delinquent) in the past week, i.e. ProcessedDate='12/29/2008', and not
available in the current week, i.e. ProcessedDate='1/5/2009', called "Cleared
Accounts".

I need help in writing the SQL to pull the info and do away with the time it
takes to process the data in Excel.

Please help. Thanks

 

[mcescher]

In Excel, I process weekly delinquent supplier data from a download, using
v-lookups. I believe that I could do better using the power of Microsoft
Access. The data is now an Access table.

Currently, I have 2 pivot tables that should feed from the supplier data
table.

Pivot table 1 displays info of delinquent suppliers info (columns) that are
available (delinquent) in the current week, i.e. ProcessedDate='1/5/2009',
and not available the previous week, i.e. ProcessedDate='12/29/2008', called
"OverDue accounts".

Pivot table 2 displays all suppliers info (columns) that were available
(delinquent) in the past week, i.e. ProcessedDate='12/29/2008', and not
available in the current week, i.e. ProcessedDate='1/5/2009', called "Cleared
Accounts".

I need help in writing the SQL to pull the info and do away with the timeit
takes to process the data in Excel.

Please help. Thanks

Take a look at crosstab queries. They do the same thing as a pivot
table. If you continue to have problems, post back with the table
column names, and we'll offer more help.

Hope this helps,
Chris M.

 

[Ikenest]

Thanks for your reply. I am not comfortable using Crosstab query. Please help
me with writing the 2 different SQL statements to retrieve the info that I
will use to populate the 2 pivot tables:

1. This week: Customer available with this week's processdate, but not
available last week: delinquent accounts
2. Last week: Customer available last week, but not any longer this week:
current accounts

Below are the columns list:

Division, Site, CustRepCode, CustRepName, CustomerNumber, CustomerName,
AccountNumber, TotalDollarsDue, LateFees, NbrDaysLate, ProcessedDate, Tax,
CustCity, CustState, AssetName

Thanks!

 

[Ikenest]

slight correction: 2. should be called "cured accounts"

Thanks for your reply. I am not comfortable using Crosstab query. Please help
me with writing the 2 different SQL statements to retrieve the info that I
will use to populate the 2 pivot tables:

1. This week: Customer available with this week's processdate, but not
available last week: delinquent accounts
2. Last week: Customer available last week, but not any longer this week:
cured accounts

Below are the columns list:

Division, Site, CustRepCode, CustRepName, CustomerNumber, CustomerName,
AccountNumber, TotalDollarsDue, LateFees, NbrDaysLate, ProcessedDate, Tax,
CustCity, CustState, AssetName

Thanks!