Please Help with creating (what I call) a Dynamic Query

[GM]

Hi,

I would appreciate some help with the following, hopefully I am going about
it the right way. I'm a bit new at this sort of access stuff.

I am calling a query from a form that retrieves a value from a list box

I then need this value to form part of the select statement of the
subsequent query.

Simplified example:

I have a table that has the following detail:

Table: MonthlyBudget
AccountNo Month1 Month2 Month3 Month4......
ac1 100.00 200.00 129.00
ac2 101.00 202.00 930.00
ac3 104.00 210.00 292.00

The aim of my query is to return a specific months figures according to the
parameter
eg where paramatervalue=2 (for Month2)
AccountNo Month
ac1 200.00
ac2 202.00
ac3 210.00

I am attempting to do this as follows:
SELECT "2" as Prd, MonthlyBudget.[Ac Ref],
dlookup(("Month"&""&[Prd]),"MonthlyBudget") AS Month
FROM MonthlyBudget;

(I've replaced my list box=Prd with just "2" to simplify things)

The result of the above is only the first value for Month 2 for all
accounts.
ac1 200
ac2 200
ac3 200

I think I need some criteria in my dlookup function but can't figure out
what to put.
(I am not certain dlookup is the best thing to use)

Any help would be very welcome

Cheers
Greg

 

[Tom Wickerath]

Hi Greg,

You do not have a normalized design and, as such, you will find it a lot
more difficult to write simple queries. Your current design includes groups
of repeating data (Month1, Month2....Month12). One account can have many
months worth of data, so you have a one-to-many (1:M) relationship. This data
should be broken down into two tables (possibly three). I'd suggest reading
up some more on database design:
http://www.datatexcg.com/Downloads/DatabaseDesignTips1997.pdf

http://www.eade.com/AccessSIG/downloads.htm
(See the last download titled "Understanding Normalization")

http://support.microsoft.com/?id=234208
http://support.microsoft.com/?id=289533

Also recommended: Find the copy of Northwind.mdb that is probably already
installed on your hard drive. Study the relationships between the various
tables (Tools > Relationships...)

Also, domain aggregrate functions are designed to return only a single
value. With a proper table design, you should have no need for a domain
aggregrate function.

Tom
___________________________________________

:

Hi,

I would appreciate some help with the following, hopefully I am going about
it the right way. I'm a bit new at this sort of access stuff.

I am calling a query from a form that retrieves a value from a list box

I then need this value to form part of the select statement of the
subsequent query.

Simplified example:

I have a table that has the following detail:

Table: MonthlyBudget
AccountNo Month1 Month2 Month3 Month4......
ac1 100.00 200.00 129.00
ac2 101.00 202.00 930.00
ac3 104.00 210.00 292.00

The aim of my query is to return a specific months figures according to the
parameter
eg where paramatervalue=2 (for Month2)
AccountNo Month
ac1 200.00
ac2 202.00
ac3 210.00

I am attempting to do this as follows:
SELECT "2" as Prd, MonthlyBudget.[Ac Ref],
dlookup(("Month"&""&[Prd]),"MonthlyBudget") AS Month
FROM MonthlyBudget;

(I've replaced my list box=Prd with just "2" to simplify things)

The result of the above is only the first value for Month 2 for all
accounts.
ac1 200
ac2 200
ac3 200

I think I need some criteria in my dlookup function but can't figure out
what to put.
(I am not certain dlookup is the best thing to use)

Any help would be very welcome

Cheers
Greg

 

[GM]

Hi Tom

Thanks for your answer, I hear, understand and agreee your point re
normalisation, but his data comes from a Sage line 50 database brought to my
Access DB as link tables.

The table in question stores several separate types of data, representing
the months of the year as follows
BalanceMth1, BalanceMth2...etc....BalanceMth12, BudgetMth1, BudgetMth2..etc
..BudgetMth12, YearPriorMth1, YearPriorMth2..etc..YearPriorMth12

I guess I could write some queries which would reorganise the data in a
"pseudo" normalised system and then query from there but I didn't thing
that would yield a much better solution.

Hence my attempt to get a query result in one step.

Ultimately I am just trying to query and report on the data available, does
my original idea make more sense in this context? or am I better trying
something else?

Regards

Greg

Hi Greg,

You do not have a normalized design and, as such, you will find it a lot
more difficult to write simple queries. Your current design includes
groups
of repeating data (Month1, Month2....Month12). One account can have many
months worth of data, so you have a one-to-many (1:M) relationship. This
data
should be broken down into two tables (possibly three). I'd suggest
reading
up some more on database design:
http://www.datatexcg.com/Downloads/DatabaseDesignTips1997.pdf

http://www.eade.com/AccessSIG/downloads.htm
(See the last download titled "Understanding Normalization")

http://support.microsoft.com/?id=234208
http://support.microsoft.com/?id=289533

Also recommended: Find the copy of Northwind.mdb that is probably already
installed on your hard drive. Study the relationships between the various
tables (Tools > Relationships...)

Also, domain aggregrate functions are designed to return only a single
value. With a proper table design, you should have no need for a domain
aggregrate function.

Tom
___________________________________________

:

Hi,

I would appreciate some help with the following, hopefully I am going
about
it the right way. I'm a bit new at this sort of access stuff.

I am calling a query from a form that retrieves a value from a list box

I then need this value to form part of the select statement of the
subsequent query.

Simplified example:

I have a table that has the following detail:

Table: MonthlyBudget
AccountNo Month1 Month2 Month3 Month4......
ac1 100.00 200.00 129.00
ac2 101.00 202.00 930.00
ac3 104.00 210.00 292.00

The aim of my query is to return a specific months figures according to
the
parameter
eg where paramatervalue=2 (for Month2)
AccountNo Month
ac1 200.00
ac2 202.00
ac3 210.00

I am attempting to do this as follows:
SELECT "2" as Prd, MonthlyBudget.[Ac Ref],
dlookup(("Month"&""&[Prd]),"MonthlyBudget") AS Month
FROM MonthlyBudget;

(I've replaced my list box=Prd with just "2" to simplify things)

The result of the above is only the first value for Month 2 for all
accounts.
ac1 200
ac2 200
ac3 200

I think I need some criteria in my dlookup function but can't figure out
what to put.
(I am not certain dlookup is the best thing to use)

Any help would be very welcome

Cheers
Greg

 

[Douglas J. Steele]

I think using a query to normalize the data would help significantly.

Create a query along the lines of the following, and name it (for the sake
of illustration) qryMonthlyBudget:

SELECT AccountNo, 1 AS MonthNumber, Month1 AS MonthValue
FROM MonthlyBudget
UNION
SELECT AccountNo, 2 AS MonthNumber, Month2 AS MonthValue
FROM MonthlyBudget
UNION
SELECT AccountNo, 3 AS MonthNumber, Month3 AS MonthValue
FROM MonthlyBudget
etc.

Now, to get only the results for month 2, you simply need:

SELECT AccountNo, MonthValue
FROM qryMonthlyBudget
WHERE MonthNumber = 2

 

[GM]

Thanks for your answers

The query idea pointed the way

Cheers
Greg