Finding Last field with value

B

Ben

Hi - I have a table "Data" which doesn't have required fields for each
record. What I'm trying to do is use a query to find the last value input in
certain fields. Currently, instead the result is '0' since the last record
doesn't have any data completed. I'm not sure how to do this properly. I've
used the LAST and MAX commands. Below is the SQL Statement. (Sorry for not
explaining better.)

SELECT DISTINCTROW Sum(Data.[# Calls Received]) AS [Sum Of # Calls
Received], Sum(Data.[# Calls Answered]) AS [Sum Of # Calls Answered],
Sum(Data.[# Calls w/in SL]) AS [Sum Of # Calls w/in SL], Sum(Data.[PVS Trades
Online]) AS [SumOfPVS Trades Online], Sum(Data.[PVS Trades Desk]) AS
[SumOfPVS Trades Desk], Sum(Data.[PVK Trades Online]) AS [SumOfPVK Trades
Online], Sum(Data.[PVK Trades Desk]) AS [SumOfPVK Trades Desk],
Max(Data.AUMMM) AS MaxOfAUMMM, Max(Data.AUMEquity) AS MaxOfAUMEquity,
Max(Data.AUMMF) AS MaxOfAUMMF, Max(Data.AUMOptions) AS MaxOfAUMOptions,
Max(Data.AUMBonds) AS MaxOfAUMBonds, Max(Data.AUMOther) AS MaxOfAUMOther,
Last(Data.AUDDate) AS LastOfAUDDate, Sum(Data.PVSTradesEquity) AS
SumOfPVSTradesEquity, Sum(Data.PVSTradesMF) AS SumOfPVSTradesMF,
Sum(Data.PVSTradesOption) AS SumOfPVSTradesOption, Sum(Data.PVSTradesFI) AS
SumOfPVSTradesFI, Sum(Data.PVKTradesEquity) AS SumOfPVKTradesEquity,
Sum(Data.PVKTradesMF) AS SumOfPVKTradesMF, Sum(Data.PVKTradesOption) AS
SumOfPVKTradesOption, Sum(Data.PVKTradesFI) AS SumOfPVKTradesFI,
Sum(Data.[Prime Broker Trades]) AS [SumOfPrime Broker Trades], Sum(Data.[GFV
Accounts]) AS [SumOfGFV Accounts] INTO WTD
FROM Data
WHERE (((Data.[Business Date]) Between [Input Beginning of Week Date] And
[Input End of Week Date (Saturday)]));
 
D

Duane Hookom

Is there any chance you could normalize your table structure? You are
"committing spreadsheet" by using data values as field names.
 
B

Ben

Sorry, I don't understand. In design view, I have either 'Last' , 'Max' , or
'Sum' in the Total field below each. As you can tell, I'm a novice, so I
don't know an easier way to do this.

Duane Hookom said:
Is there any chance you could normalize your table structure? You are
"committing spreadsheet" by using data values as field names.

--
Duane Hookom
Microsoft Access MVP


Ben said:
Hi - I have a table "Data" which doesn't have required fields for each
record. What I'm trying to do is use a query to find the last value input in
certain fields. Currently, instead the result is '0' since the last record
doesn't have any data completed. I'm not sure how to do this properly. I've
used the LAST and MAX commands. Below is the SQL Statement. (Sorry for not
explaining better.)

SELECT DISTINCTROW Sum(Data.[# Calls Received]) AS [Sum Of # Calls
Received], Sum(Data.[# Calls Answered]) AS [Sum Of # Calls Answered],
Sum(Data.[# Calls w/in SL]) AS [Sum Of # Calls w/in SL], Sum(Data.[PVS Trades
Online]) AS [SumOfPVS Trades Online], Sum(Data.[PVS Trades Desk]) AS
[SumOfPVS Trades Desk], Sum(Data.[PVK Trades Online]) AS [SumOfPVK Trades
Online], Sum(Data.[PVK Trades Desk]) AS [SumOfPVK Trades Desk],
Max(Data.AUMMM) AS MaxOfAUMMM, Max(Data.AUMEquity) AS MaxOfAUMEquity,
Max(Data.AUMMF) AS MaxOfAUMMF, Max(Data.AUMOptions) AS MaxOfAUMOptions,
Max(Data.AUMBonds) AS MaxOfAUMBonds, Max(Data.AUMOther) AS MaxOfAUMOther,
Last(Data.AUDDate) AS LastOfAUDDate, Sum(Data.PVSTradesEquity) AS
SumOfPVSTradesEquity, Sum(Data.PVSTradesMF) AS SumOfPVSTradesMF,
Sum(Data.PVSTradesOption) AS SumOfPVSTradesOption, Sum(Data.PVSTradesFI) AS
SumOfPVSTradesFI, Sum(Data.PVKTradesEquity) AS SumOfPVKTradesEquity,
Sum(Data.PVKTradesMF) AS SumOfPVKTradesMF, Sum(Data.PVKTradesOption) AS
SumOfPVKTradesOption, Sum(Data.PVKTradesFI) AS SumOfPVKTradesFI,
Sum(Data.[Prime Broker Trades]) AS [SumOfPrime Broker Trades], Sum(Data.[GFV
Accounts]) AS [SumOfGFV Accounts] INTO WTD
FROM Data
WHERE (((Data.[Business Date]) Between [Input Beginning of Week Date] And
[Input End of Week Date (Saturday)]));
Click to expand...
Click to expand...
 
D

Duane Hookom

I don't have a clear understanding of your table structure or what you are
storing. When I see field names like AUMEquity and AUMMM and AUMMF, it raises
a red flag. What happens if you need to add another AUM type field?

What type of fields are these? What is your table structure? What are you
storing? Do you have a primary key field in DATA?

--
Duane Hookom
Microsoft Access MVP


Ben said:
Sorry, I don't understand. In design view, I have either 'Last' , 'Max' , or
'Sum' in the Total field below each. As you can tell, I'm a novice, so I
don't know an easier way to do this.

Duane Hookom said:
Is there any chance you could normalize your table structure? You are
"committing spreadsheet" by using data values as field names.

--
Duane Hookom
Microsoft Access MVP


Ben said:
Hi - I have a table "Data" which doesn't have required fields for each
record. What I'm trying to do is use a query to find the last value input in
certain fields. Currently, instead the result is '0' since the last record
doesn't have any data completed. I'm not sure how to do this properly. I've
used the LAST and MAX commands. Below is the SQL Statement. (Sorry for not
explaining better.)

SELECT DISTINCTROW Sum(Data.[# Calls Received]) AS [Sum Of # Calls
Received], Sum(Data.[# Calls Answered]) AS [Sum Of # Calls Answered],
Sum(Data.[# Calls w/in SL]) AS [Sum Of # Calls w/in SL], Sum(Data.[PVS Trades
Online]) AS [SumOfPVS Trades Online], Sum(Data.[PVS Trades Desk]) AS
[SumOfPVS Trades Desk], Sum(Data.[PVK Trades Online]) AS [SumOfPVK Trades
Online], Sum(Data.[PVK Trades Desk]) AS [SumOfPVK Trades Desk],
Max(Data.AUMMM) AS MaxOfAUMMM, Max(Data.AUMEquity) AS MaxOfAUMEquity,
Max(Data.AUMMF) AS MaxOfAUMMF, Max(Data.AUMOptions) AS MaxOfAUMOptions,
Max(Data.AUMBonds) AS MaxOfAUMBonds, Max(Data.AUMOther) AS MaxOfAUMOther,
Last(Data.AUDDate) AS LastOfAUDDate, Sum(Data.PVSTradesEquity) AS
SumOfPVSTradesEquity, Sum(Data.PVSTradesMF) AS SumOfPVSTradesMF,
Sum(Data.PVSTradesOption) AS SumOfPVSTradesOption, Sum(Data.PVSTradesFI) AS
SumOfPVSTradesFI, Sum(Data.PVKTradesEquity) AS SumOfPVKTradesEquity,
Sum(Data.PVKTradesMF) AS SumOfPVKTradesMF, Sum(Data.PVKTradesOption) AS
SumOfPVKTradesOption, Sum(Data.PVKTradesFI) AS SumOfPVKTradesFI,
Sum(Data.[Prime Broker Trades]) AS [SumOfPrime Broker Trades], Sum(Data.[GFV
Accounts]) AS [SumOfGFV Accounts] INTO WTD
FROM Data
WHERE (((Data.[Business Date]) Between [Input Beginning of Week Date] And
[Input End of Week Date (Saturday)]));
Click to expand...
Click to expand...
Click to expand...
 
Top