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

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Douglas J. Steele (NOSPAM_djsteele_at_NOSPAM_canada.com)
Date: 03/17/05 

Date: Thu, 17 Mar 2005 17:54:51 -0500

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 

-- 
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)
"GM" <ghm101@hotmail.com> wrote in message 
news:enEK7xzKFHA.4052@tk2msftngp13.phx.gbl...
> 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
>
>

Relevant Pages

  • Re: SQL for Counting 1 total of 4 fields - normalization issue onl
    ... This was my attempt to normalize an existing ... Chris F ... "Duane Hookom" wrote: ... union query by record or some other way, ...
    (microsoft.public.access.queries)
  • RE: Error message -- "property value is too large"
    ... Microsoft Access Support ... Microsoft Security Bulletin MS03-026? ... I created a query based on these ... |> - Normalize your data by creating ONE table that has a field for the ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Using VBA to Loop through Access Table
    ... I wouldn't use VBA code to normalize data. ... FROM TableName AS T1 ... Then create an append query that uses the above query as the ... There are about 1,000 Parts and many Vendors that are subject to change. ...
    (microsoft.public.access.modulesdaovba)
  • RE: Display fields Limited on Values
    ... you could normalize with a union query. ... SELECT PersonID, Require1 as Met, "Require1" as Requirement ... "Duane Hookom" wrote: ...
    (microsoft.public.access.queries)
  • Re: Using VBA to Loop through Access Table
    ... VBA is the only way to automate the process so a non-programmer could run ... UNION query, but to do that, VBA would need data from other tables. ... normalize the data: ... There are about 1,000 Parts and many Vendors that are subject to change. ...
    (microsoft.public.access.modulesdaovba)