Query pulling up multiple entries

[citloner]

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.

 

[Klatuu]

Post the SQL of your query.

 

[citloner]

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]

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

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.

 

[citloner]

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?

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

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.

 

[Klatuu]

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

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?

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

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.