Flat File Problems

[Martin Spence]

I have a very large flat file that I import and need to do some manipulation
on. The challenge for me isnow how to get an aswer to a sate related
calculation. The data looks like:

Serial#1 Item#1 Date
Serial#1 Item#2 Date
Serial#1 Item#3 Date
Serial#2 Item#4 Date
Serial#3 Item#5 Date
Serial#3 Item#6 Date

I want to know for each serial number the count of items - no problem there.
Then I want to know for each serial number the count of items that have a
date value of less than a week apart. The items are unique IDs for a service
call - I have to calculate the % of repeat calls - a repeat call is
considered one that comes in within a week of the prior for each serial
number.

I've scratched my head a bunch and finally given up and need to ask for help.

Thanks Much

 

[Dirk Goldgar]

I have a very large flat file that I import and need to do some
manipulation
on. The challenge for me isnow how to get an aswer to a sate related
calculation. The data looks like:

Serial#1 Item#1 Date
Serial#1 Item#2 Date
Serial#1 Item#3 Date
Serial#2 Item#4 Date
Serial#3 Item#5 Date
Serial#3 Item#6 Date

I want to know for each serial number the count of items - no problem
there.
Then I want to know for each serial number the count of items that have a
date value of less than a week apart. The items are unique IDs for a
service
call - I have to calculate the % of repeat calls - a repeat call is
considered one that comes in within a week of the prior for each serial
number.

Can we safely assume that the "flat file" has been imported into an Access
table, and that thw query can work with the imported table, not with the
flat file itself? Otherwise performance is going to be horrendous.

Make sure that the table has indexes on serial number, item number, and call
date. Then something like this might work:

SELECT
C.SerialNo, Count(*) AS RepeatCalls
FROM
(
SELECT
SerialNo, ItemNo,
CallDate,
(
SELECT Max(S.CallDate) FROM ServiceCalls S
WHERE S.SerialNo = ServiceCalls.SerialNo
AND S.CallDate <= ServiceCalls.CallDate
AND S.ItemNo <> ServiceCalls.ItemNo
) AS PrevCallDate
FROM ServiceCalls
) AS C
WHERE C.CallDate-C.PrevCallDate<=7
GROUP BY C.SerialNo;

Note: if you will never have a repeat call on the same day, this can be
simplified slightly by leaving the criterion on ItemNo out of the query.

 

[Martin Spence]

Thanks - that worked - it took me a while to figure out the SQL as I don't do
SQL - but I got it to work.

Thanks again