creating a sublist within a list- is this possible?

[jackman1]

really really sorry to be a pain here. not sure if tis possible, but can you create a sublist within a list in a table? i'm doing a musical databaseand i want to create a field which contains different categories/families of instruments, such as woodwind, brass, etc. is there any possible way to create a sublist within this list box, so in other words, say you select woodwind, another list box comes of the option, with all the individual instruments for each family? thanks anyone for your help.

 

[daveUK]

You'll need to do this with 2 tables. One for the category of instrument and one for the actual instrument. The tables will have a one - many relationship i.e. one category can have many instruments.

HTH

 

[jackman1]

ok, so i have my five different musical categories/families, which are woodwind, brass, strings, vocal and percussion. i take it i'd make a field for each category, then another table would hold the actual instruments?
Would this work? i mean to say, as i plan to create a form from these tables, say i was to click on woodwind as the category of instrument, would only the woodwind instruments come up when selecting the instrument in the instrument field? or does this take something far more complicated?

 

[razorking]

An example that may give you some ideas

Here is a small sample database I whipped up that might be close to what you are looking for.

There are many ways to accomplish something like you describe. I rather like the method in the attached .mdb.

It works very well with a wheel mouse.

Good Luck!

 

[trucktime]

Instruments

Jackman1, I played a bit with the mdb that Razorking posted, and made a few changes. Now the records on the subform are related to the record on the mainform. When you select an instrument form the listbox, the subform will show all the instruments in that group.
Look at the code in the AfterUpdate Event of the listbox, that basically shows how it works.

Good Luck

 

[trucktime]

File

Oops, forgot to attach the file.
Here it is.

 

[razorking]

Another Way

Here is another version you might like.

 

[jackman1]

Hey guys sorry to bother you again, but im running into problems yet again with my database project. i've finished constructing the tables for my project, now i just need to do the queries and im more or less finished. just as an example- my database consists of many members-
one form that i have consists of the following fields:
memberNumber (dups ok)
Member names
lesson category
lesson category fees
payment1
payment2
date payment1 paid
sum broughtforward
refunds
TotalDue
This set of fields is unique to the term code- in other words, each member has to pay fees every term- therefore i also have a term code for each term. i wanted each terms details to come up on 1 screen which i've managed, but i also came across a major problem.
For the sumbroughtforward field, i need it to link back to the field entitled "TotalDue" from the previous term. i have no idea how to do this, is there any way? I have a feeling it may be do to with putting the info in a query and using Dlookup?
Sorry again, and thanks anyone for your help!

 

[Rich]

If the fields you've posted are table fields then your structure is incorrect.
If a member can make more than one payment then payments belong in a separate table.
The sumbrought foward and balances should be calculated either at form/report level or in a query

 

[jackman1]

Can someone tell me how do do IIF formulae? For example, i want to put IF Category=I-45, then £400.00, if Category=D3, then £120.00, if Category=D2, then £200.00
thanks for your help.