Input form with check boxes

[p3rlend]

I have a table named Cost with two columns; CostName and CostValue.

CostName	CostValue
Cost1	150
Cost2	29
Cost3	5
Cost4	63
Cost5	13

The number of cost elements will change. I also have a table with alternatives, where the number also will change.

Alternative

Alt1

Alt2

Alt3

Each cost element can be included in any of the alternatives. So I would like to have an input form that looks like this:




The areas inside the red frame would be like Yes/No check boxes. A box that is checked could result in an record in a many-to-many relationship table between the Cost and Alternative table.

I don't know how to make such a form, don't even know if its possible. But I would really appreciate it if someone could help me out

Thanks in advance!

[CJ_London]

you need to clarify - you say Each cost element can be included in any of the alternatives - if so, you need a link between the two tables - otherwise how does access know that a tick in alt1 relates only to costs1,2 and 4?

next, to create the view you have suggested for your input form would require a query (such as a crosstab) which is not editable.

It would be helpful if you can provide some background to your question - what are you actually trying to do in the real world? i.e. we make widgets, each widget can be made in one of 3 ways and we want to be able to compare the different costs of each way of making a widget.

It can be done, but not easily and will require vba coding and most likely using what are called ado disconnected recordsets. However what that coding looks like depends on your answers and the real world explanation.

[p3rlend]

Thought that I wouldn't bother you with too lengthy explanations, but would love to elaborate

you need to clarify - you say Each cost element can be included in any of the alternatives - if so, you need a link between the two tables - otherwise how does access know that a tick in alt1 relates only to costs1,2 and 4?

Each cost element represent a part of a possible project. This project can be put together in several ways with different combination of parts and costs. Therefore we have several concepts/alternatives for the project, and a single cost can be included or excluded in any of the alternatives. The alternatives are not setteled yet, neither are the costs. So, the Cost table will grow, and so will the Alternative table.

For a given moment in time we might have the situation above; five cost elements that represent some element that could be executed, and three alternatives that combine some of the costs (because we can't afford to do everything). The last table above represent a cross join (cartesian product) between Cost and Alternative with an extra input field of type Yes/No.

If the Cost and Alternative were given, I could add a make table query and add an input column (Yes/No) afterwards. But this table would not be dynamic if Cost and Alternative changes, as far as I can see. So I don't know how to add an input field to a query and still keep this query table dynamic.

A many-to-many relationship between the Cost and the Alternative table could be a way to go. Every "Yes" or tick would be identified with a record in this table. But if the Cost and Alternative tables grow, this seems like a slow way to specify the tick input. I might be wrong ...

next, to create the view you have suggested for your input form would require a query (such as a crosstab) which is not editable.

Yes, I thought about the crosstab query, but didn't find a way to edit it.

It would be helpful if you can provide some background to your question - what are you actually trying to do in the real world? i.e. we make widgets, each widget can be made in one of 3 ways and we want to be able to compare the different costs of each way of making a widget.

This is a construction project in the conceptual phase. So I am making a modell to add simple aggregated costs and risk, and then calculate the NPVs (net present values) to compare the alternatives.

[CJ_London]

A many-to-many relationship between the Cost and the Alternative table could be a way to go

it has to be the way to go. With a cartesian product every alt would be assigned to every cost - or the other way round if you prefer - which is not what you want.

As to the way forward, I think there are two possible solutions. For a crosstab solution, take a look at this link to create an updateable crosstab form.

http://www.access-programmers.co.uk/...d.php?t=243278

for disconnected recordsets take a look at this link

https://support.microsoft.com/en-us/kb/184397

This is for a straightforward table. In your case, again you would need a crosstab query (for ease) as your recordset, create the disconnected recordset, then assign the recordset to your (continuous or datasheet) form for editing. The update side of things would also be different. You would need some code in the afterupdate event of each alt control to update your many to many table.

You also have the problem of the number of alt columns required to show on your form. The 'easy' solution is to have enough alt controls to meet your max requirements, using code to hide those not required.

From your brief description I would expect your tables to be something like

tblProjects
ProjectPK autonumber
ProjectName text
...
...

tblCosts
CostPK autonumber
ProjectFK long indexed
CostName text
CostValue currency

tblAlternatives
AltPK autonumber
ProjectFK long indexed
AltNo integer
AltDescription text

tblAltChoices
CostFK long indexed - plus multifield PK with AltFK to ensure no duplicates
AltFK long indexed

Your tick box then is based on whether a record in tblAltChoices exists or not

[p3rlend]

Thanks for a thorough reply

These are some new techniques I have to look into before I will be able to produce the result. I have one comment and question though.

it has to be the way to go. With a cartesian product every alt would be assigned to every cost - or the other way round if you prefer - which is not what you want.

Yes, a cartesian product table A and B is the smallest set which has a relation to every element in both tables. But if we introduce a new relation or attribute from the cartesian product table (i.e. a new field), we can introduce values to every combination of elements in the two tables.

Let's say we have table A

#A	Name
1	A1
2	A2

and table B

#B	Name
1	B1
2	B2
3	B3

The cartesian product with an extra field would f.ex. look like this:

#	#A	#B	Value
1	1	1	v1
2	1	2	v2
3	1	3	v3
4	2	1	v4
5	2	2	v5
6	2	3	v6

This would be a valuable tool, to create the cartesian product and add new fields to it, and keep the new table dynamic if something changes in table A og B. Don't you agree? And if yes, is this possible?

It just happens that I often want to create such a table, but I don't know how.

[CJ_London]

Don't you agree?

Cartesian queries have their uses - not convinced this is one of them

And if yes, is this possible?

using queries keeps things dynamic so not sure what you point is

Since you are experimenting, suggest try it and see if you does what you actually want.

Either way, I'm now signing off for a couple of weeks so regret I cannot continue the discussion. Good luck