Query pulling up multiple entries

G

Guest

Hello,

I have a query based on three tables linked by a common identifier which is
meant to pull up monthly client market values based on the historical date I
enter in. If I don't specify a date, it lists each discreet market value
based on the length of history of the client, so theoretically, 15 data
points. If I specify a date in the query, it lists that specific period's
market value, say October 07, but 15 times over. How can I write syntax or
restructure the query to pull up one discreet value per client for the time
period I want?

Thanks so much.
 
G

Guest

SELECT History.Date, "Total Assets" AS Report, History.DateEnteredFund,
History.CompanyName, [Funds List].Funds, [Funds List].[Asset Class],
History.Expr1, Client.[Client Type], Client.[Alternate Client Type], [Funds
List].[I and R Assets], [Funds List].[Sub Advised Account], [Funds
List].[Discretionary Assets], Client.ERISA, "Defined benefit" AS [Plan Type],
[Funds List].[Country of Investment], Client.[Residency of Account], [Funds
List].[Tax Exempt], [Funds List].Strategy, [Funds List].[Fund Type], [Funds
List].Style, [Funds List].Registered
FROM ([Funds List] INNER JOIN History ON [Funds List].Funds=History.Funds)
INNER JOIN Client ON [Funds List].Funds=Client.Fund
WHERE (((History.Date)=#10/31/2007#) AND (([Funds List].Funds) Not In
("ewfi","gfm i")));
 
G

Guest

Try this
SELECT DISTINCT History.Date, "Total Assets" AS Report,...
--
Dave Hargis, Microsoft Access MVP


citloner said:
SELECT History.Date, "Total Assets" AS Report, History.DateEnteredFund,
History.CompanyName, [Funds List].Funds, [Funds List].[Asset Class],
History.Expr1, Client.[Client Type], Client.[Alternate Client Type], [Funds
List].[I and R Assets], [Funds List].[Sub Advised Account], [Funds
List].[Discretionary Assets], Client.ERISA, "Defined benefit" AS [Plan Type],
[Funds List].[Country of Investment], Client.[Residency of Account], [Funds
List].[Tax Exempt], [Funds List].Strategy, [Funds List].[Fund Type], [Funds
List].Style, [Funds List].Registered
FROM ([Funds List] INNER JOIN History ON [Funds List].Funds=History.Funds)
INNER JOIN Client ON [Funds List].Funds=Client.Fund
WHERE (((History.Date)=#10/31/2007#) AND (([Funds List].Funds) Not In
("ewfi","gfm i")));





Klatuu said:
Post the SQL of your query.
Click to expand...
Click to expand...
 
G

Guest

Hi there. It reduced the number of records but not fully, took it down to
around 2500. Should I be applying a filter to the query?

Klatuu said:
Try this
SELECT DISTINCT History.Date, "Total Assets" AS Report,...
--
Dave Hargis, Microsoft Access MVP


citloner said:
SELECT History.Date, "Total Assets" AS Report, History.DateEnteredFund,
History.CompanyName, [Funds List].Funds, [Funds List].[Asset Class],
History.Expr1, Client.[Client Type], Client.[Alternate Client Type], [Funds
List].[I and R Assets], [Funds List].[Sub Advised Account], [Funds
List].[Discretionary Assets], Client.ERISA, "Defined benefit" AS [Plan Type],
[Funds List].[Country of Investment], Client.[Residency of Account], [Funds
List].[Tax Exempt], [Funds List].Strategy, [Funds List].[Fund Type], [Funds
List].Style, [Funds List].Registered
FROM ([Funds List] INNER JOIN History ON [Funds List].Funds=History.Funds)
INNER JOIN Client ON [Funds List].Funds=Client.Fund
WHERE (((History.Date)=#10/31/2007#) AND (([Funds List].Funds) Not In
("ewfi","gfm i")));





Klatuu said:
Post the SQL of your query.
--
Dave Hargis, Microsoft Access MVP


:

Hello,

I have a query based on three tables linked by a common identifier which is
meant to pull up monthly client market values based on the historical date I
enter in. If I don't specify a date, it lists each discreet market value
based on the length of history of the client, so theoretically, 15 data
points. If I specify a date in the query, it lists that specific period's
market value, say October 07, but 15 times over. How can I write syntax or
restructure the query to pull up one discreet value per client for the time
period I want?

Thanks so much.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Sorry, citloner, as I read through the SQL, I think the joins may be causing
the additional rows, but without it in front of me, I can't help much. I
would have to experiment with it for a while.
--
Dave Hargis, Microsoft Access MVP


citloner said:
Hi there. It reduced the number of records but not fully, took it down to
around 2500. Should I be applying a filter to the query?

Klatuu said:
Try this
SELECT DISTINCT History.Date, "Total Assets" AS Report,...
--
Dave Hargis, Microsoft Access MVP


citloner said:
SELECT History.Date, "Total Assets" AS Report, History.DateEnteredFund,
History.CompanyName, [Funds List].Funds, [Funds List].[Asset Class],
History.Expr1, Client.[Client Type], Client.[Alternate Client Type], [Funds
List].[I and R Assets], [Funds List].[Sub Advised Account], [Funds
List].[Discretionary Assets], Client.ERISA, "Defined benefit" AS [Plan Type],
[Funds List].[Country of Investment], Client.[Residency of Account], [Funds
List].[Tax Exempt], [Funds List].Strategy, [Funds List].[Fund Type], [Funds
List].Style, [Funds List].Registered
FROM ([Funds List] INNER JOIN History ON [Funds List].Funds=History.Funds)
INNER JOIN Client ON [Funds List].Funds=Client.Fund
WHERE (((History.Date)=#10/31/2007#) AND (([Funds List].Funds) Not In
("ewfi","gfm i")));





:

Post the SQL of your query.
--
Dave Hargis, Microsoft Access MVP


:

Hello,

I have a query based on three tables linked by a common identifier which is
meant to pull up monthly client market values based on the historical date I
enter in. If I don't specify a date, it lists each discreet market value
based on the length of history of the client, so theoretically, 15 data
points. If I specify a date in the query, it lists that specific period's
market value, say October 07, but 15 times over. How can I write syntax or
restructure the query to pull up one discreet value per client for the time
period I want?

Thanks so much.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Pull latest date from Multiple entries 2
IIF statement in a query 2
using Dates as a Criteria/Lookup in Query 2
Add a field to Query 2
Date Problem in query result 1
query multiple non-consecutive dates 4
Date/time query issues 3
Query/Function/Parameter Troubles... 2

Top