How do I group a text field in Microsoft Access query?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a query that has criteria that groups information from one table by
date and then a second table by date and then a calculation is preformed by
expression.

My problem is that when I run the query each item from the first table is
listed separately by date even when they are from the same vendor. Do I need
to remove things from my query or reorder things to get it consolidate all of
the same vendor into one row?
 
Post your SQL by opening the query in design view, click on menu VIEW - SQL
View, highlight all, copy, and paste in a post.
 
SELECT client_transactions.[Client Number], client_transactions.Symbol,
Sum(client_transactions.Shares) AS SumOfShares, investment_value.Date,
investment_value.[Current Value], Sum([Shares]*[Current Value]) AS TotalValue
FROM investment_value INNER JOIN client_transactions ON
investment_value.Symbol = client_transactions.Symbol
GROUP BY client_transactions.[Client Number], client_transactions.Date,
client_transactions.Symbol, investment_value.Date, investment_value.[Current
Value]
HAVING (((client_transactions.[Client Number])="00001") AND
((client_transactions.Date) Between #1/1/2004# And #6/30/2006#) AND
((investment_value.Date) Between #6/1/2006# And #6/30/2006#));
 
I still do not understand your question.
Here is an example of output from the query.
Client Number Symbol SumOfShares Date Current Value TotalValue
00001 ABA 4 6/3/2006 147 588
00001 ABA 4 6/4/2006 148 592
00001 ABA 4 6/5/2006 155 620
00001 ABA 4 6/6/2006 154 616
00001 CSC 2 6/3/2006 47 94
00001 CSC 2 6/4/2006 48 96
00001 CSC 2 6/5/2006 55 110
00001 CSC 2 6/6/2006 54 108

What do you not want?

rmoore said:
SELECT client_transactions.[Client Number], client_transactions.Symbol,
Sum(client_transactions.Shares) AS SumOfShares, investment_value.Date,
investment_value.[Current Value], Sum([Shares]*[Current Value]) AS TotalValue
FROM investment_value INNER JOIN client_transactions ON
investment_value.Symbol = client_transactions.Symbol
GROUP BY client_transactions.[Client Number], client_transactions.Date,
client_transactions.Symbol, investment_value.Date, investment_value.[Current
Value]
HAVING (((client_transactions.[Client Number])="00001") AND
((client_transactions.Date) Between #1/1/2004# And #6/30/2006#) AND
((investment_value.Date) Between #6/1/2006# And #6/30/2006#));

KARL DEWEY said:
Post your SQL by opening the query in design view, click on menu VIEW - SQL
View, highlight all, copy, and paste in a post.
Click to expand...
Click to expand...
 
I want all entries from same symbol

00001 ABA 4 6/3/2006 147 588
00001 ABA 4 6/4/2006 148 592
00001 ABA 4 6/5/2006 155 620
00001 ABA 4 6/6/2006 154 616
00001 CSC 2 6/3/2006 47 94
00001 CSC 2 6/4/2006 48 96
00001 CSC 2 6/5/2006 55 110
00001 CSC 2 6/6/2006 54 108

to sum to one line regardless of date. e.g.

00001 ABA 16 xxxxxxx 147 2352
00001 CSC 8 xxxxxxx 47 1504

The current value will always remain the same for the symbol no matter the
date. So, I want them to consolidate to one line. Make any sense??

KARL DEWEY said:
I still do not understand your question.
Here is an example of output from the query.
Client Number Symbol SumOfShares Date Current Value TotalValue
00001 ABA 4 6/3/2006 147 588
00001 ABA 4 6/4/2006 148 592
00001 ABA 4 6/5/2006 155 620
00001 ABA 4 6/6/2006 154 616
00001 CSC 2 6/3/2006 47 94
00001 CSC 2 6/4/2006 48 96
00001 CSC 2 6/5/2006 55 110
00001 CSC 2 6/6/2006 54 108

What do you not want?

rmoore said:
SELECT client_transactions.[Client Number], client_transactions.Symbol,
Sum(client_transactions.Shares) AS SumOfShares, investment_value.Date,
investment_value.[Current Value], Sum([Shares]*[Current Value]) AS TotalValue
FROM investment_value INNER JOIN client_transactions ON
investment_value.Symbol = client_transactions.Symbol
GROUP BY client_transactions.[Client Number], client_transactions.Date,
client_transactions.Symbol, investment_value.Date, investment_value.[Current
Value]
HAVING (((client_transactions.[Client Number])="00001") AND
((client_transactions.Date) Between #1/1/2004# And #6/30/2006#) AND
((investment_value.Date) Between #6/1/2006# And #6/30/2006#));

KARL DEWEY said:
Post your SQL by opening the query in design view, click on menu VIEW - SQL
View, highlight all, copy, and paste in a post.

:

I have a query that has criteria that groups information from one table by
date and then a second table by date and then a calculation is preformed by
expression.

My problem is that when I run the query each item from the first table is
listed separately by date even when they are from the same vendor. Do I need
to remove things from my query or reorder things to get it consolidate all of
the same vendor into one row?
Click to expand...
Click to expand...
Click to expand...
 
I think your approach is wrong. Check this out.
The client bought shares in two companies at different times.
Client Number Symbol Shares Date
00001 CSC 2 3/3/2006
00001 ABA 4 2/2/2006
00001 CSC 66 4/4/2006
00001 ABA 2 5/5/2006

The price of the shares keeps changing.
Symbol Current Value Date
CSC 47 6/3/2006
CSC 48 6/4/2006
CSC 55 6/5/2006
CSC 54 6/6/2006
ABA 147 6/3/2006
ABA 148 6/4/2006
ABA 155 6/5/2006
ABA 154 6/6/2006

What is the value of holdings?
Client Number Symbol Total Shares Valuation Date Share Value Total Value of
Shares
00001 ABA 6 6/7/2006 154 924
00001 CSC 68 6/6/2006 54 3672

If this is what you want then use these three queries ----



rmoore said:
I want all entries from same symbol

00001 ABA 4 6/3/2006 147 588
00001 ABA 4 6/4/2006 148 592
00001 ABA 4 6/5/2006 155 620
00001 ABA 4 6/6/2006 154 616
00001 CSC 2 6/3/2006 47 94
00001 CSC 2 6/4/2006 48 96
00001 CSC 2 6/5/2006 55 110
00001 CSC 2 6/6/2006 54 108

to sum to one line regardless of date. e.g.

00001 ABA 16 xxxxxxx 147 2352
00001 CSC 8 xxxxxxx 47 1504

The current value will always remain the same for the symbol no matter the
date. So, I want them to consolidate to one line. Make any sense??

KARL DEWEY said:
I still do not understand your question.
Here is an example of output from the query.
Client Number Symbol SumOfShares Date Current Value TotalValue
00001 ABA 4 6/3/2006 147 588
00001 ABA 4 6/4/2006 148 592
00001 ABA 4 6/5/2006 155 620
00001 ABA 4 6/6/2006 154 616
00001 CSC 2 6/3/2006 47 94
00001 CSC 2 6/4/2006 48 96
00001 CSC 2 6/5/2006 55 110
00001 CSC 2 6/6/2006 54 108

What do you not want?

rmoore said:
SELECT client_transactions.[Client Number], client_transactions.Symbol,
Sum(client_transactions.Shares) AS SumOfShares, investment_value.Date,
investment_value.[Current Value], Sum([Shares]*[Current Value]) AS TotalValue
FROM investment_value INNER JOIN client_transactions ON
investment_value.Symbol = client_transactions.Symbol
GROUP BY client_transactions.[Client Number], client_transactions.Date,
client_transactions.Symbol, investment_value.Date, investment_value.[Current
Value]
HAVING (((client_transactions.[Client Number])="00001") AND
((client_transactions.Date) Between #1/1/2004# And #6/30/2006#) AND
((investment_value.Date) Between #6/1/2006# And #6/30/2006#));

:

Post your SQL by opening the query in design view, click on menu VIEW - SQL
View, highlight all, copy, and paste in a post.

:

I have a query that has criteria that groups information from one table by
date and then a second table by date and then a calculation is preformed by
expression.

My problem is that when I run the query each item from the first table is
listed separately by date even when they are from the same vendor. Do I need
to remove things from my query or reorder things to get it consolidate all of
the same vendor into one row?
Click to expand...
Click to expand...
Click to expand...
 
Sorry, I clicked wrong.

rmoore-1 ---
SELECT client_transactions.[Client Number], client_transactions.Symbol,
Sum(client_transactions.Shares) AS [Total Shares]
FROM client_transactions
WHERE (((client_transactions.[Client Number])=[Enter Client number]))
GROUP BY client_transactions.[Client Number], client_transactions.Symbol;

rmoore-2 ----
SELECT investment_value.Symbol, Max(investment_value.Date) AS MaxOfDate,
Last(investment_value.[Current Value]) AS [LastOfCurrent Value]
FROM investment_value
GROUP BY investment_value.Symbol;

rmoore-3 ---
SELECT [rmoore-1].[Client Number], [rmoore-1].Symbol, [rmoore-1].[Total
Shares], [rmoore-2].MaxOfDate AS [Valuation Date], [rmoore-2].[LastOfCurrent
Value] AS [Share Value], [Total Shares]*[LastOfCurrent Value] AS [Total Value
of Shares]
FROM [rmoore-1] INNER JOIN [rmoore-2] ON [rmoore-1].Symbol =
[rmoore-2].Symbol;


KARL DEWEY said:
I think your approach is wrong. Check this out.
The client bought shares in two companies at different times.
Client Number Symbol Shares Date
00001 CSC 2 3/3/2006
00001 ABA 4 2/2/2006
00001 CSC 66 4/4/2006
00001 ABA 2 5/5/2006

The price of the shares keeps changing.
Symbol Current Value Date
CSC 47 6/3/2006
CSC 48 6/4/2006
CSC 55 6/5/2006
CSC 54 6/6/2006
ABA 147 6/3/2006
ABA 148 6/4/2006
ABA 155 6/5/2006
ABA 154 6/6/2006

What is the value of holdings?
Client Number Symbol Total Shares Valuation Date Share Value Total Value of
Shares
00001 ABA 6 6/7/2006 154 924
00001 CSC 68 6/6/2006 54 3672

If this is what you want then use these three queries ----



rmoore said:
I want all entries from same symbol

00001 ABA 4 6/3/2006 147 588
00001 ABA 4 6/4/2006 148 592
00001 ABA 4 6/5/2006 155 620
00001 ABA 4 6/6/2006 154 616
00001 CSC 2 6/3/2006 47 94
00001 CSC 2 6/4/2006 48 96
00001 CSC 2 6/5/2006 55 110
00001 CSC 2 6/6/2006 54 108

to sum to one line regardless of date. e.g.

00001 ABA 16 xxxxxxx 147 2352
00001 CSC 8 xxxxxxx 47 1504

The current value will always remain the same for the symbol no matter the
date. So, I want them to consolidate to one line. Make any sense??

KARL DEWEY said:
I still do not understand your question.
Here is an example of output from the query.
Client Number Symbol SumOfShares Date Current Value TotalValue
00001 ABA 4 6/3/2006 147 588
00001 ABA 4 6/4/2006 148 592
00001 ABA 4 6/5/2006 155 620
00001 ABA 4 6/6/2006 154 616
00001 CSC 2 6/3/2006 47 94
00001 CSC 2 6/4/2006 48 96
00001 CSC 2 6/5/2006 55 110
00001 CSC 2 6/6/2006 54 108

What do you not want?

:

SELECT client_transactions.[Client Number], client_transactions.Symbol,
Sum(client_transactions.Shares) AS SumOfShares, investment_value.Date,
investment_value.[Current Value], Sum([Shares]*[Current Value]) AS TotalValue
FROM investment_value INNER JOIN client_transactions ON
investment_value.Symbol = client_transactions.Symbol
GROUP BY client_transactions.[Client Number], client_transactions.Date,
client_transactions.Symbol, investment_value.Date, investment_value.[Current
Value]
HAVING (((client_transactions.[Client Number])="00001") AND
((client_transactions.Date) Between #1/1/2004# And #6/30/2006#) AND
((investment_value.Date) Between #6/1/2006# And #6/30/2006#));

:

Post your SQL by opening the query in design view, click on menu VIEW - SQL
View, highlight all, copy, and paste in a post.

:

I have a query that has criteria that groups information from one table by
date and then a second table by date and then a calculation is preformed by
expression.

My problem is that when I run the query each item from the first table is
listed separately by date even when they are from the same vendor. Do I need
to remove things from my query or reorder things to get it consolidate all of
the same vendor into one row?
Click to expand...
Click to expand...
Click to expand...
 
Okay. I understand were you're coming from. That is the price you paid for
them when you bought them.

But here I'm trying to find out the value of the holdings at a specific
date. Client 00001 may have bought them at different values on different
days, but let's say at 6/06/06 each share he bought, regardless of what he
bought them at have all the same value per share.

So, here I want all the total shares he owns for each symbol with the
criteria being from xxx date to xxx date multplied by the value at another
xxx date. The query I have now pulls that data e.g.
Client Number Date Symbol SumOfShares Date Current Value TotalValue
00001 12/30/2005 MMK 93.74 6 /30/2006 $1.00 $93.74
00001 4 /20/2006 MMK 380.49 6 /30/2006 $1.00 $380.49
00001 1 /30/2006 MMK -50000 6 /30/2006 $1.00 ($50,000.00)
00001 12/15/2005 MMK 130.22 6 /30/2006 $1.00 $130.22
00001 8 /10/2005 MMK -15000 6 /30/2006 $1.00 ($15,000.00)
00001 8 /18/2005 MMK 147.93 6 /30/2006 $1.00 $147.93

How can these sum into one line? e.g.

00001 xxxxxxxxxx MMK -64247.62 6/30/2006 $1.00 ($64,247.62)
 
The queries as posted give the value of all own as of the latest value date.

You could add date criteria to rmoore-1 that is like <=[Enter Your Check
Date]
 
Back
Top