Table query from Access beginner 1

[DaMorf]

Hi,

I am fairly new to access (about 6 months of on/off usage) and i am having some trouble with a table, i have a table which lists budgets for different area's and shows month by month data.

the problem i have is i need to run a query that will return the budget for a given month using a combo box as the criteria for the area i am looking at and for the month.

i have no problem getting the query to return data for the area as that is a column but it will obvoiusly show the whole years data and not just the month i am looking for.

Many thanks,

 

[Beowulf005]

I found the best place for access help is in forum702

When frustrated remember, in the computer world there is almost always a backdoor.

 

[PhilH123]

DaMorf,

Have you got a date field within the table??

If so, what you are looking for will be the Month function. Create a field within your query such as MonthNum:Month([YourDateField]) and use criteria such as Month(Now)

Hope this points you in the right direction.

Phil H.

 

[DaMorf]

Thanks for your reply Phil,

No I don't have a date feild just 12 individual month fields and a Business area field.

i don't know that i am organising this correctly but i need to find a way of selecteing the month field from criteria in a combo box

 

[PhilH123]

Hi,

You're right in thinking that your data could be better organised. But, what you are trying to do is still possible.

You need to create a field in your query which uses several nested IIf statements to get the correct data.

Below is what you will need to type in the Field box of your query (changing values and field names as appropriate) :

ReturnedValue : IIf([Forms]![frmSelect]![Combo0]="January",[BusUnitYear]![January],IIf([Forms]![frmSelect]![Combo0]="February",[BusUnitYear]![February],IIf([Forms]![frmSelect]![Combo0]="March",[BusUnitYear]![March],IIf([Forms]![frmSelect]![Combo0]="April",[BusUnitYear]![April],IIf([Forms]![frmSelect]![Combo0]="May",[BusUnitYear]![May],IIf([Forms]![frmSelect]![Combo0]="June",[BusUnitYear]![June],IIf([Forms]![frmSelect]![Combo0]="July",[BusUnitYear]![July],IIf([Forms]![frmSelect]![Combo0]="August",[BusUnitYear]![August],IIf([Forms]![frmSelect]![Combo0]="September",[BusUnitYear]![September],IIf([Forms]![frmSelect]![Combo0]="October",[BusUnitYear]![October],IIf([Forms]![frmSelect]![Combo0]="November",[BusUnitYear]![November],IIf([Forms]![frmSelect]![Combo0]="December",[BusUnitYear]![December],""))))))))))))

If you are going to use this data regularly though you really should look at a better table structure which will make querying a lot easier.

Regards,
Phil H.

 

[fneily]

DaMorf
If you just created this database, I strongly advice you to scrap it and start over correctly. Read up on Normalizing and Relationships. Right now you have a mess, not an Access database. You should also take some Access courses. I've taught over 4000 people and may have met 1 or 2 that learned Access by themselves. Case in point, using DAO/ADO you could loop through your field names and accumulate the totals.
By as mentioned, post Access questions in the Access forums. That's where the experts hang out.