473,320 Members | 1,921 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Relationships and a form - Am I doing this the right way?

Hi, all.

Using Access 2K. I think what I'm trying to do is quite simple, but
I've been out of the loop for a few years so I've forgotten much.

I have a table of records each of which can belong to multiple
categories. I have a table of all the categories available.

I have a crossreference table which links records to categories.

What I want to do is have a form for the records, with an unlinked
subform which displays _all_ the categories, with a check mark next to
the ones which have a crossreference record. If a checkbox is checked,
a new crossreference records is created. If a checkbox is cleared, the
crossreference record is deleted.

I'm not entirely sure that the way I'm planning to do it is
particularly efficient anyway, but at some point I want to enable
"triple state" for the checkbox, the "grayed out" state indicating
where the category was added after the record, so the system "doesn't
know" if the record belongs to the category.

Does anyone have any comments or examples of how I can better do this,
assuming it's not already being done appropriately.

Dec 5 '06 #1
2 1180
Assuming you are refering to gramaphone records as opposed to records in a
table, the normal way of doing it is to have a table of rRecords, a table of
Categories abd a JoinRecordCategory table consisting of RecordID and
CategoryID as joint keys.

You then have a Record form and a continuous subform based on the
JoinRecordCategory table and linked to the main form on the RecordID

The subform has on it a combo box for selecting the category and the
ControlSource will be JoinRecordCategory!CategoryID

HTH

Phil

"Gorf" <g.*******@gmail.comwrote in message
news:11**********************@j72g2000cwa.googlegr oups.com...
Hi, all.

Using Access 2K. I think what I'm trying to do is quite simple, but
I've been out of the loop for a few years so I've forgotten much.

I have a table of records each of which can belong to multiple
categories. I have a table of all the categories available.

I have a crossreference table which links records to categories.

What I want to do is have a form for the records, with an unlinked
subform which displays _all_ the categories, with a check mark next to
the ones which have a crossreference record. If a checkbox is checked,
a new crossreference records is created. If a checkbox is cleared, the
crossreference record is deleted.

I'm not entirely sure that the way I'm planning to do it is
particularly efficient anyway, but at some point I want to enable
"triple state" for the checkbox, the "grayed out" state indicating
where the category was added after the record, so the system "doesn't
know" if the record belongs to the category.

Does anyone have any comments or examples of how I can better do this,
assuming it's not already being done appropriately.

Dec 5 '06 #2
Assuming you are refering to gramaphone records as opposed to records in a
table, the normal way of doing it is to have a table of rRecords, a table of
Categories abd a JoinRecordCategory table consisting of RecordID and
CategoryID as joint keys.

You then have a Record form and a continuous subform based on the
JoinRecordCategory table and linked to the main form on the RecordID

The subform has on it a combo box for selecting the category and the
ControlSource will be JoinRecordCategory!CategoryID
Thanks for your reply, Phil. I'm sorry it's taken so long to get back
to you.

It's not a gramophone database, that's my fault for using archaic terms
such as "files", "fields" and "records" when I should be using
"tables", "columns" and "rows". It just shows you how far out of touch
I've got!

Anyway, it's not a bad analogy so I'll stick with it. Let's say I have
categories of "Rock", "Punk", "Pop" etc. On frmRecords I want a
continuous subform of categories where all of them are displayed as
text, and only the relevant ones are checked (so a song can be both
"Pop" and "Rock" for example). Where a category is added _after_ the
song, the triplestate of the checkbox is enabled so that it's plain to
see where songs need attention because new categories may apply.

I think I'll have to do it by populating the xref table with all
current categories when the song is added, then deal with the absence
of a category at a later stage.

If it sounds a bit ridiculous, it's because it's actually a FAQ
database, and we certainly will be adding categories as the business
moves into new areas, but some existing FAQs may well apply to new
categories. I'd like an easy way of seeing which FAQs predate the new
categories.

Jan 17 '07 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: mark | last post by:
I've been working on an Access 2000 database for a couple of weeks now. I took a course in access about a year ago, a crash course, and I learned a ton, but I didn't touch Access for the year since...
2
by: Megan | last post by:
hello everybody, i know this is a very long post, but i wanted to provide as much detail as possible. quick overview- i want to create a couple of many to many relationships and am wondering...
0
by: Megan | last post by:
Hi Everybody- I know that this is a really, really long post, but I wanted to try to give you as much background as possible. So here's a quick overview of the issues I'm asking for help with:...
3
by: Nicolae Fieraru | last post by:
Hi All, I am working on a database which has over 30 tables and they have relationships between them. Sometimes I notice that in the forms I get the information which I don't need. For example,...
45
by: salad | last post by:
I'm curious about your opinion on setting relationships. When I designed my first app in Access I'd go to Tools/Relationships and set the relationships. Over time I'd go into the window and see...
1
by: Ron | last post by:
Hi All, I have relationships on a database built in the back end, but I'm wondering if that's where they should be. Can they be built in the front end but apply to all the back end tables? Can...
1
by: cricket7 | last post by:
I think I figured out my tables and relationships. Tables: Table 1 tblTowedVehicles fldTowID fldReportDate fldReportTime fldlReportNumber
13
by: ARC | last post by:
Hello all, Prior to going live with my app, I have questions on relationships theory. My prior app was done in Access 97, and I did NOT use relationships at all. I have 65 tables in my...
2
by: John Google | last post by:
Hi, Access 2002. I import tables from another database where I only copy the definition and not the data. I select the Import Relationships option on the import dialog. After I do the...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.