parent/child - subforms

[WimDC]

Hello,

is it possible to create a 2-column subform, where the first column shows ALL the records of a table or query and in the second column the user can choose (dropdown) values from another table.
I also get an error in my summary-query.

SITUATION (see attached dummy-database):
- each transaction records the date, the vendor and the product (only one product!)
- BUT for each transaction, the vendor can give zero, one or more gifts to his client. For each given gift, he has to choose the color. (the colors are the same for all gifts)
- in the gifts-table, there's a year-field. I need a filter (query) so only the gifts for this year are shown (next year, the vendor can choose between other gifts).

Can someone help me in the right direction?

Wim

 

[GanzPopp]

You are going into the right direction. But: check the relationships of your database: transactions.id should not be connected to gifts.id, but to transaction_gifts.T_id instead.

As for your second question, just add a transaction_gifts row for each type of gift and color for each transaction and add a NumberOfGifts field to it.

The answer to the third I cannot answer strictly, because you are not very clear in what you want. A straight answer to your current formulation is straightforward:

SELECT * FROM gifts WHERE G_year="2013"

To improve speed of your database, set field types to numbers if they only contain numbers. So G_year should be a number, now it is a text field.

 

[WimDC]

Thanks for your suggestion and I changed some forms / relations. (see new attachment)

But not want I want.

As you can see (frm_transactions2), the 3 possible products are in the left column. I did this manually, just to show how I want it.

I mean: in the left column AND FOR EACH RECORD, I want a LIST OF ALL THE POSSIBLE PRODUCT (from the products-tabel) WITHOUT the possibility to change those items.
The user may only choose the color in the second column for each given product.

The point is, that I don't know if this really possible. Maybe I ase something that can't be realized in Access...

 

[GanzPopp]

That certainly is possible!

Create a form where you display the transaction_gifts table and make sure only the colour of the gifts can be edited. Right now you display the table directly (and linked correctly), but you want to display it only for the given ID and to restrict the user of editing certain data. Therefore you need this form and display it as a subform on the frm_transactions2 form.

It is a lot to explain, so if you want to know more, search for subform data display concepts and work your way through it. This takes time, but it will be worth it, because once you get the hang of it, you can make very nice database applications.