This week or not this week - SQL help

[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

 

[Clifford Bass]

Hi,

To start with, you can probably make it easier on yourself if you
combine the contents of the two tables into one table with a status code.

tblSupplierData
SupplierID
[Other info that identifies this instance]
ProcessDate
AccountStatusCode (A code from a tblAccountStatusCodes; Overdue,
Delinquent, Cleared)

Regarless of whether or not you make that change, it is pretty hard to
help with an SQL statement without the pertinant table and field information;
such as what uniquely identifies an individual record and the other
applicable fields. So post that information.

Clifford Bass

 

[Ikenest]

Hi,

Thanks for your reply. 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.

FYI, the data is already combined in one 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.

Thanks for following up on this.
--
Nestor Ike

Hi,

To start with, you can probably make it easier on yourself if you
combine the contents of the two tables into one table with a status code.

tblSupplierData
SupplierID
[Other info that identifies this instance]
ProcessDate
AccountStatusCode (A code from a tblAccountStatusCodes; Overdue,
Delinquent, Cleared)

Regarless of whether or not you make that change, it is pretty hard to
help with an SQL statement without the pertinant table and field information;
such as what uniquely identifies an individual record and the other
applicable fields. So post that information.

Clifford Bass

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

 

[Ikenest]

Please help me with writing the 2 different SQL statements (view) 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 (data type: text), CustRepCode (text), CustRepName (text),
CustomerNumber (text), CustomerName (text), AccountNumber (text),
TotalDollarsDue (number), LateFees (number), NbrDaysLate (number),
ProcessedDate (date), Tax (number), CustCity (text), CustState (text),
AssetName (text)

The primary key is automatically assigned by Access

Thanks!

--
Nestor Ike

Hi,

To start with, you can probably make it easier on yourself if you
combine the contents of the two tables into one table with a status code.

tblSupplierData
SupplierID
[Other info that identifies this instance]
ProcessDate
AccountStatusCode (A code from a tblAccountStatusCodes; Overdue,
Delinquent, Cleared)

Regarless of whether or not you make that change, it is pretty hard to
help with an SQL statement without the pertinant table and field information;
such as what uniquely identifies an individual record and the other
applicable fields. So post that information.

Clifford Bass

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

 

[Clifford Bass]

Hi,

I am not sure why you are doing pivot tables. But I have not used
them, so that could by why I am not sure. Regardless, if you will provide
the Access supplier data table name, the field names in that table, and what
field(s) make up the primary key I should be able to come up with a couple of
queries that give you the desired results.

Clifford Bass

 

[Clifford Bass]

Hi,

So, outside of the automatically assigned primary key, for any one row,
which fields uniquely identify it from all of the other rows?

Clifford Bass

 

[Ikenest]

Hi,

See the column names on my previous email.

Per Excel Help definition, "A PivotTable report is an interactive table that
you can use to quickly summarize large amounts of data. You can rotate its
rows and columns to see different summaries of the source data, filter the
data by displaying different pages, or display the details for areas of
interest."

Still there is a need to have the data organized in an Excel file or a
database of any kind (Access, Oracle, etc) for retrieval. Pivot tables are
mainly used for data presentation.

Thanks again,

 

[Ikenest]

Hi,

I believe it is the combination of different fields, mainly the
'processeddate" with the other fields. The "processeddate" falls on any
Sunday and the first day of the month. Because, there may a customer with the
same account number that showed available (did not pay) the previous week and
still available this week or the other way around: customer no available
(paid) last week and showed available (did not pay) this week. So you will
have duplicate account numbers/customer numbers on the AccountNumber /
CustomerNumber fields.
I hope this helps.

Thanks,

 

[Clifford Bass]

Hi,

One more clarification needed: If there was processing on say
12/28/2008 and 1/1/2009, does that count as the same week, or a "different
week"? This impacts on how to determine "this" week and "last" week.

Thanks,

Clifford Bass

 

[Ikenest]

Hi,

It counts as a different week. And also, it does impact on how to determine
"this" week and "last" week.

Thanks a ton!

 

[Clifford Bass]

Hi,

Okay, here is what I came up with.

1) Create a table called "tblDual" with a field of any sort in it. Add
one row and only one row.

2) Create a query called "qryThisWeekLastWeek". Its SQL will be:

PARAMETERS [Report on Date:] DateTime;
SELECT IIf(IsNull([Report on Date:]),Date(),[Report on Date:]) AS
ReportOnDate,
DateAdd("d",1-IIf(Weekday([ReportOnDate])>Day([ReportOnDate]),Day([ReportOnDate]),Weekday([ReportOnDate])),[ReportOnDate])
AS ThisWeek, DateAdd("d",-1,[ThisWeek]) AS LastWeekEnd,
DateAdd("d",1-IIf(Weekday([LastWeekEnd])>Day([LastWeekEnd]),Day([LastWeekEnd]),Weekday([LastWeekEnd])),[LastWeekEnd]) AS LastWeek
FROM tblDual;

The purpose of this is to compute the process dates of "this" week and
"last" week based on what you wrote. I added in the ability to prompt for
the week on which to report. If you just click okay without entering
anything it will use the current week. If you enter a date, it will use the
week based off of that date. That way you can run the process for other
weeks than the current one--for historical purposes or other reasons such no
one did it some week.

3) The query to show overdue customers:

SELECT A.CustomerNumber, A.AccountNumber, A.TotalDollarsDue, A.LateFees,
A.ProcessedDate
FROM tblSupplierData AS A INNER JOIN qryThisWeekLastWeek AS B ON
A.ProcessedDate = B.ThisWeek
WHERE (((Exists (SELECT * FROM tblSupplierData AS C WHERE C.CustomerNumber =
A.CustomerNumber and C.ProcessedDate = LastWeek))=False));

4) The query to show cleared customers:

SELECT A.CustomerNumber, A.AccountNumber, A.TotalDollarsDue, A.LateFees,
A.ProcessedDate
FROM tblSupplierData AS A INNER JOIN qryThisWeekLastWeek AS B ON
A.ProcessedDate = B.LastWeek
WHERE (((Exists (SELECT * FROM tblSupplierData AS C WHERE C.CustomerNumber =
A.CustomerNumber and C.ProcessedDate = ThisWeek))=False));

These queries connect on the customer number. If you want to do
accounts instead change the appropriate CustomerNumber conditions to
AccountNumber. Or, if you want to do it based on a combination of
CustomerNumber and AccountNumber add "and C.AccountNumber = A.AccountNumber",
without the quotes between the "C.CustomerNumber" and the following "and".
Also, you will need to change the table name to your table's name. And you
can add other fields as desired. The queries are based on the ProcessedDate
being only a Sunday or the first day of a month. Finally, to get a listing
of those who have rows in both weeks use the first query and modify "False"
to "True".

Let me know if that does not do it.

Clifford Bass