Re: Please Help with creating (what I call) a Dynamic Query
From: GM (ghm101_at_hotmail.com)
Date: 03/17/05
- Next message: Vadimbar: "Output query data to a field in a form"
- Previous message: Eric: "Help with Expression in a query"
- In reply to: Tom Wickerath: "RE: Please Help with creating (what I call) a Dynamic Query"
- Next in thread: Douglas J. Steele: "Re: Please Help with creating (what I call) a Dynamic Query"
- Reply: Douglas J. Steele: "Re: Please Help with creating (what I call) a Dynamic Query"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 17 Mar 2005 21:59:50 -0000
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
"Tom Wickerath" <AOS168 AT @comcast DOT net> wrote in message
news:96F53E5F-D2FC-4C33-81AE-DDC9E22FEF0E@microsoft.com...
> 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
> ___________________________________________
>
> "GM" wrote:
>
> 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
- Next message: Vadimbar: "Output query data to a field in a form"
- Previous message: Eric: "Help with Expression in a query"
- In reply to: Tom Wickerath: "RE: Please Help with creating (what I call) a Dynamic Query"
- Next in thread: Douglas J. Steele: "Re: Please Help with creating (what I call) a Dynamic Query"
- Reply: Douglas J. Steele: "Re: Please Help with creating (what I call) a Dynamic Query"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
