Query - how would u go about this

[Kwahuboy]

I have a table that looks like this:

Name of table: CategoryTab

Fields in tabe: 2

Fieldnames: Costctr and Category

The data in the table looks like this:

200500 Office

200500 Meetings

200500 Agency

200500 Revenue Offset

200502 Meetings

200502 Office

200502 Agency

200502 Travel

My problem is I want a query on a combo box that will list the costctr once and list all categories associated with that cost center.I want it to look like this.

200500 Office

Meetings

Agency

Revenue Offset

200502 Meetings

Office

Agency

Travel


Can someone help!

Thank You!

Kwahuboy

[KnooKie]

SELECT Costctr, Category
FROM CategoryTab
GROUP BY Costctr
ORDER BY Costctr Asc, Category Asc


paste this into the row source property of your combo box

column count property = 2

[Kwahuboy]

Thanks a lot for your help. I am still getting the same result that is the costctr is repeating on every line instead of repeating once and then the category items connected with it coming up. Now I dont know what to do. Do you have any other ideas around the problem

All I want to do is to be able to click on a cost center number in a combo box and the corresponding categories associated with that cost center showing up in a text box or a combo box. I have no idea how to do that. I am a novice so bear with me.

KW

[KnooKie]

Thanks a lot for your help. I am still getting the same result that is the costctr is repeating on every line instead of repeating once and then the category items connected with it coming up. Now I dont know what to do. Do you have any other ideas around the problem

No easy way around this in the context of what you are asking. Only way to show the info as you say above is in a report

All I want to do is to be able to click on a cost center number in a combo box and the corresponding categories associated with that cost center showing up in a text box or a combo box. I have no idea how to do that. I am a novice so bear with me.

Sounds like you need to have 2 combo boxes. One with the Cost centre number to pick which in turn filters the category combobox.....

combobox1 row source SQL.....column count = 1......bound column = 1.....

SELECT Costctr
FROM CategoryTab
GROUP BY Costctr
ORDER BY Costctr Asc

For combobox2 you need to construct a query with the CategoryTab table in it with the Costctr AND Category fields in the grid. In the criteria part of the costctr field add....

Forms!MyFormName!Combobox1Name

bound column = 2, column count = 2


This will filter the 2nd combobox based on what you select in the first.

The 2nd combobox SQL for the row source should look something like this....

SELECT Costctr, Category
FROM CategoryTab
WHERE costctr = Forms!MyFormName!combobox1Name
ORDER BY Category Asc

[Kwahuboy]

I cant thank you enough for the help you gave me. May God bless you. By the way, what should I put in the after update section of the combo boxes. Also the filtering does not seem to work according to the cost centers. Any help on that. I did exactly what you said. It filters all right but not according to what is in the data. What am I doing wrong.

[KnooKie]

Hard to say about the filtering without seeing your table structure i'm afraid.

Are your relationships ok between your tables ?

It depends what you want to do to what you want to put in the AfterUpdate event really.

[Kwahuboy]

Can I email you my mde file?

K

[KnooKie]

sure, you'll need to zip it first though or our firewall will block it.

pnockolds@iiresearchgroup.co.uk

[cvandemaele]

> This is a reply to your original question. The following syntax is "Oracle", but can easily be modified to comply with other databases.

> Assumption : costctr + category is a unique value.

create table categorytab (costctr integer, category varchar2(20));

insert into CategoryTab values (200500, 'Office');
insert into CategoryTab values (200500, 'Meetings');
insert into CategoryTab values (200500, 'Agency');
insert into CategoryTab values (200500, 'Revenue Offset');
insert into CategoryTab values (200502, 'Meetings');
insert into CategoryTab values (200502, 'Office');
insert into CategoryTab values (200502, 'Agency');
insert into CategoryTab values (200502, 'Travel');

select
trim(nvl(to_char(t2.costctr),' ') || ' ' || t1.category) as MyColumn
from CategoryTab t1
left outer join
(select
t3.costctr,
t3.category
from CategoryTab t3
where t3.category = (select min(t4.category) from CategoryTab t4 where t4.costctr = t3.costctr)) t2
on t2.costctr = t1.costctr
and t2.category = t1.category
order by t1.costctr, t1.category;

MYCOLUMN
-------------------------------------------------------------
200500 Agency
Meetings
Office
Revenue Offset
200502 Agency
Meetings
Office
Travel

8 rows selected

> Personally, I would never implement such a code in an application. You should definetely go for the "2 combo box-solutions", the last combo box filtering out the categories according to the selected coststr in the first combo box.

> Good luck

[Kwahuboy]

Thanks a lot. I see the 2 combo solution better as Knookie mentioned. Thanks very much.

K

[BMan111]

ok, KoonKie, I'm really glad you post on here. I am trying to basicly this exact same thing, and I have it sorting, somewhat. How do I get it to update everytime i reselect a sorting catagory?

In my case (to be a little more specific), I have a drop down list of years, then a second drop down menu of all the projects that were created during those years. When I first start the form, it works fine once, which ever year i select it displays all the projects for that year. When I reselect a new year, the project list stays the same and wont update.

I'm sure its simple, but if you could help me out I would appreiate it.

Brandon

[KnooKie]

You need to requery the projects listbox from within the afterUpdate event of the years combobox. Thus everytime you select a year the projects combobox requeries.

So in the afterupdate event of the first combobox put something similar to....

Me.combobox2Name.Requery

[BMan111]

We love you KoonKie, you so much smarter than we are.

That worked, but when I select the year, and then go to pull down the project list, it brings up a message box that wants the value of Expr1 (the name of the field in the query where the calculation is done). If I hit ok it goes away and figures the list, and if i hit cancle then it remains the same. Any thoughts on getting rid of the box?

Brandon

[Kwahuboy]

The problem with mine was that the second combo box was requerying alright but when I pick a selection on the second combo box list it stays the same it will not change to the selected item.

Thanks

[BMan111]

Try unlocking the field maybe? I tried to lock it so they couldnt change the numbers they were getting, but it didnt allow me to make any selections.

Just a thought.

Brandon

Nevermind, check that, when I closed my form, and then opened it again the damnedest thing happened, it wont show up in form view. Its there in datasheet, and in design view, but not form view...other than this, i believe it was working correctly...

thoughts on what happend? anyone?