table link problem

[NAJ]

I'd like to create something which will allow me to neatly tie some
items in one table to some items in another table by using some kind
of tick box system where i have one category of items along the top
and another category of items down the side (like one might produce
with a cross tab query). The problem is producing a tick box table in
the middle which will link the two tables. I don't know how to do it.
I had a dabble with the cross tab query stuff but I've got nowhere
with it so far.

Here's the kind of thing i'd like (but the true and false being tick
boxes):

|Item A|Item B|Item C|Item D
----------------------------------
box 1| true |false | true |false
box 2|false |true |false |true
box 3|false |true |true |true
box 4|true |false |false |true

And we'd have some kind of link table which would be related to it
which would be controlled by the grid system above:

Item | box
-----------
a |1
a |4
b |2
b |3
c |1
c |3
d |2
d |3
d |4

I hope that makes sense!
I can think of ways of linking these two items together - eg by going
to a form for item a and add in each box and then the same for item
b,..c,..d etc. But I thought some kind of cross tab type table would
make it much simpler for the user and easier to make sense of too.
I can bring the results back in the form of a cross tab by using the
"count" function to count the links and I have a 1 in the box when
there is a link, but I can't actually set up links using this or think
of a way to do it.
Can anyone offer any help or ideas please?
Cheers
NeilH

 

[Tom Ellison]

Dear Neil:

This is something we do commonly here, but it is fairly advanced.

Your design of a link table is right on. The trick is to create a
temporary table to present the data as needed. This is done from a
query like this one:

SELECT I.Item, B.box
IIf(Exists(SELECT * FROM ItemBox IB
WHERE IB.Item = I.Item AND IB.Box = B.Box), -1, 0)
FROM Item, Box

Unlike the link table you have, the above presents ALL the
combinations of Item and Box with a "boolean" column that shows what
you have as true/false. This is then ready to be represented in a
check box, if you please.

Creating a Form to operate from this data is a more complex issue,
especially if you want to create columns for values of items as you
have shown. The Form is created using a scroll bar control for
horizontal scrolling. If you have more items than you can show across
the screen (and remember, it may be very easy for users to add items
to the database) then scrolling is the answer. Be sure to keep the
left-most column for box # static (not scrolling).

The extreme difficulty occurs when the number of columns on the Form
exceeds 255, in which case the columns cannot all be simultaneously
represented on the form. To accomodate this, the query must be
written to dynamically represent only those columns necessary for the
columns currently displayed on the screen.

The above are representative of the challenges to be met, and the
techniques to be used in a solution. A complete description of all
the twists and turns necessary to implement it would be a small book.

If you are up to the challenge of some fairly advanced programming,
we'd be glad to assist you at the critical points along the way.

Or, perhaps I need to get busy and write the book.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[NAJ]

The trick is to create a

temporary table to present the data as needed. This is done from a
query like this one:

SELECT I.Item, B.box
IIf(Exists(SELECT * FROM ItemBox IB
WHERE IB.Item = I.Item AND IB.Box = B.Box), -1, 0)
FROM Item, Box
Unlike the link table you have, the above presents ALL the
combinations of Item and Box with a "boolean" column that shows what
you have as true/false. This is then ready to be represented in a
check box, if you please.

I quietly tried that route for a while thinking that if I was going to
have a check box for each option then I'd need a representation of
each option. So I'm happy that a temporary table is what is needed
here. (so I'm glad you've confirmed that I hadn't missed some obvious
solution while headed down this path)

Creating a Form to operate from this data is a more complex issue,
especially if you want to create columns for values of items as you
have shown. The Form is created using a scroll bar control for
horizontal scrolling. If you have more items than you can show across
the screen (and remember, it may be very easy for users to add items
to the database) then scrolling is the answer. Be sure to keep the
left-most column for box # static (not scrolling).

Think this is where i'm getting confused now. I'm on Access 97 by the
way - I'm not sure if that makes any difference here. I can't think of
a dynamic way of creating the columns. Is there an actual control
called a scroll bar control? Or have I got this confused with
something else?

The extreme difficulty occurs when the number of columns on the Form
exceeds 255, in which case the columns cannot all be simultaneously
represented on the form. To accomodate this, the query must be
written to dynamically represent only those columns necessary for the
columns currently displayed on the screen.

That won't be a problem. There will never be more than 255 columns -
(or rows hopefully) I reckon the maximum number of columns is likely
to be about 20 odd.

Should I be going along the lines of creating a record set of the
temporary table and dynamically creating checkbox controls and placing
them in code?
I don't really know what to do next with this method...

Thanks for the help so far though, its been most helpful!

The above are representative of the challenges to be met, and the
techniques to be used in a solution. A complete description of all
the twists and turns necessary to implement it would be a small book.

If you are up to the challenge of some fairly advanced programming,
we'd be glad to assist you at the critical points along the way.

Or, perhaps I need to get busy and write the book.

a book would be good yep! go for it.