Access Query Problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have say 3 columns 1st=Date, 2nd and 3rd=May and June. May and June
columns have a list of budget figures.

I have written a query where I want to be ably to select any date from the
1st column and it will only bring up a list of budget figures for that date.

Hope this makes sense
Amy
 
Sorry for not being clear. This is the raw data. How do i write a function
that allows me to select a month 200601 OR 200602 and it only brings up the
figures that are in that column.

Code Month 200601 200602
AM01 200601 £100 £200
AM02 200601 £300 £400
AM03 200601 £100 £200
AM01 200602 £100 £200
AM02 200602 £300 £400
AM03 200602 £100 £200

Therefore, if I typed 200602 the only data that appears would be

Code Month 200602
AM01 200602 £200
AM02 200602 £400
AM03 200602 £200
 
Hi Amy - why do you have the Month Code in the Month column, but then an
amount in both of the other 2 columns? My first response would be that you
are duplicating the data and can alter your table, UNLESS there is a reason
for the amounts to be in twice. Are they budget figures? Would the figure for
June on the May line alter in June and/or the figure for May alter in June.
Does this make sense to you? If the figures don't change that 1 column is
enough for your amounts and it is easy then to get what you need. - Dika
 
Its not duplicated this is just an example the real amounts will be budget
figures which are all different. I need the code column as the data needs to
be split by code then the budget figure according to the month that I select
 
Right, got ya. In that case - in your Query add another field by clicking on
the Field line on a blank column and enter
Selected Budget: IIf([month]=200601,[200601],IIf([month]=200602,[200602],0))
(Selected Budget can be any text you want as it is the Field Name.)

If you have more months to be selected enter IIF([month]=xxxxx,[xxxxx],0)
where the 0 is currently (obviously the xxxxx stands for 200601, etc. There
is a limit to how many IIF statements you can 'embed' and the total amount of
characters in an expression (can't remember off the top of my head) so you
may need to make your codes smaller. I sometimes split selections like into 2
fields.
Take the Tick out of the show box of the fields you don't want to see and
bobs your uncle. Hope this is understandable and helps! Yours - Dika
 
Back
Top