Domain aggregate sums and/or max's

  • Thread starter Thread starter Brett T. Burtner
  • Start date Start date
B

Brett T. Burtner

Please help. I have an old Excel spreadsheet my company has asked me to
analyze data from. I need to look at the following fields:

[Serial Number]
[Date]
Code: 
[Deposit]
[Rent]

For each [Serial Number], I need to find the most recent [Date] entered
where [Code] equals "SV".
Then, once I've found the most recent [Date], I need to calculate how much
to send a bill for each Serial Number fitting the above criteria, using the
following equation:

(((TodaysDate)-([Date])*[Rent])-[Deposit])

Please help!
 
I would create the following totals select query after
importing the data to a table:
SELECT Test1.[Serial Number], Test1.Code, Test1.Deposit,
Max(Test1.Date) AS MaxOfDate, Test1.Rent FROM Test1
GROUP BY Test1.[Serial Number], Test1.Code, Test1.Deposit,
Test1.Rent HAVING (((Test1.Code)="SV"));
This will give you all you need to make your report.
I used "test1" as the table name so you'll have to
substitute.
Hope this helps.
Fons
 
Thanks Fons. I tried what you recommended, and I still have problems. For
example: there is one [Serial Number] listed twice, with two different
[Date] values (two records still displayed). I need only the most recent
displayed. Do you know how to do that? I would appreciate any help. Thank
you, and good day!

Brett T. Burtner

Fons Ponsioen said:
I would create the following totals select query after
importing the data to a table:
SELECT Test1.[Serial Number], Test1.Code, Test1.Deposit,
Max(Test1.Date) AS MaxOfDate, Test1.Rent FROM Test1
GROUP BY Test1.[Serial Number], Test1.Code, Test1.Deposit,
Test1.Rent HAVING (((Test1.Code)="SV"));
This will give you all you need to make your report.
I used "test1" as the table name so you'll have to
substitute.
Hope this helps.
Fons
-----Original Message-----
Please help. I have an old Excel spreadsheet my company has asked me to
analyze data from. I need to look at the following fields:

[Serial Number]
[Date]
Code: 
[Deposit]
[Rent]

For each [Serial Number], I need to find the most recent [Date] entered
where [Code] equals "SV".
Then, once I've found the most recent [Date], I need to calculate how much
to send a bill for each Serial Number fitting the above criteria, using the
following equation:

(((TodaysDate)-([Date])*[Rent])-[Deposit])

Please help!


.
[/QUOTE][/QUOTE]
Click to expand...
Click to expand...
 
Are you sure that you have "max" in the totals row for the
date in your query. And since we have group by in all the
other block in the totals row, is any of the other data
also varying.
If you wish send me a sample data list of the query so I
can look at it.
My email is above.
Fons
-----Original Message-----
Thanks Fons. I tried what you recommended, and I still have problems. For
example: there is one [Serial Number] listed twice, with two different
[Date] values (two records still displayed). I need only the most recent
displayed. Do you know how to do that? I would appreciate any help. Thank
you, and good day!

Brett T. Burtner

I would create the following totals select query after
importing the data to a table:
SELECT Test1.[Serial Number], Test1.Code, Test1.Deposit,
Max(Test1.Date) AS MaxOfDate, Test1.Rent FROM Test1
GROUP BY Test1.[Serial Number], Test1.Code, Test1.Deposit,
Test1.Rent HAVING (((Test1.Code)="SV"));
This will give you all you need to make your report.
I used "test1" as the table name so you'll have to
substitute.
Hope this helps.
Fons
-----Original Message-----
Please help. I have an old Excel spreadsheet my
Click to expand...
company
has asked me to
analyze data from. I need to look at the following fields:

[Serial Number]
[Date]
Code: 
[Deposit]
[Rent]

For each [Serial Number], I need to find the most[/QUOTE] recent
[Date] entered[QUOTE]
where [Code] equals "SV".
Then, once I've found the most recent [Date], I need to calculate how much
to send a bill for each Serial Number fitting the above criteria, using the
following equation:

(((TodaysDate)-([Date])*[Rent])-[Deposit])

Please help!


.
[/QUOTE][/QUOTE]


.
[/QUOTE]
Click to expand...
Click to expand...
Click to expand...
 
I emailed you some sample data, but the answer to your question is "Yes -- I
am sure I have "max" in the totals row for the date in my query. Also, yes,
some of the other data changes. Thanks in advance.

Brett

Fons Ponsioen said:
Are you sure that you have "max" in the totals row for the
date in your query. And since we have group by in all the
other block in the totals row, is any of the other data
also varying.
If you wish send me a sample data list of the query so I
can look at it.
My email is above.
Fons
-----Original Message-----
Thanks Fons. I tried what you recommended, and I still have problems. For
example: there is one [Serial Number] listed twice, with two different
[Date] values (two records still displayed). I need only the most recent
displayed. Do you know how to do that? I would appreciate any help. Thank
you, and good day!

Brett T. Burtner

I would create the following totals select query after
importing the data to a table:
SELECT Test1.[Serial Number], Test1.Code, Test1.Deposit,
Max(Test1.Date) AS MaxOfDate, Test1.Rent FROM Test1
GROUP BY Test1.[Serial Number], Test1.Code, Test1.Deposit,
Test1.Rent HAVING (((Test1.Code)="SV"));
This will give you all you need to make your report.
I used "test1" as the table name so you'll have to
substitute.
Hope this helps.
Fons
-----Original Message-----
Please help. I have an old Excel spreadsheet my company
has asked me to
analyze data from. I need to look at the following
fields:

[Serial Number]
[Date]
Code: 
[Deposit]
[Rent]

For each [Serial Number], I need to find the most recent
[Date] entered
where [Code] equals "SV".
Then, once I've found the most recent [Date], I need to
calculate how much
to send a bill for each Serial Number fitting the above
criteria, using the
following equation:

(((TodaysDate)-([Date])*[Rent])-[Deposit])

Please help!


.
[/QUOTE]


.
[/QUOTE][/QUOTE]
Click to expand...
Click to expand...
Click to expand...
 
Back
Top