Replies

For a simpler approach using one main form and one subform check out this thread. There is also an attachment with a sample of a many-to-many relationship with main form and subform. Just replace courses with contacts and criteria with addresses and you should be able to fill in the rest from there.

The newest version is named course2k.zip and can be found in my original reply to this thread.

That would explain why I couldn’t see it. I guess the post is removed temporarily while you’re editing it. Makes sense but it was confusing when I first opened the thread.

OK I’ve uploaded a new version with a form and subform which is usually the way I deal with these situations (this version is in A2k). I used a main form for the course and a subform for the link. As you move from course record to record you can then designate in the subform which criteria the course supports.

The 2 methods you originally posted may be simpler for data entry but I think you will find it more difficult to create a useable report. If you do some reading on normalization principles one thing you should come across says if your tables contain fields with null values repeatedly something may be amiss with the design (your first method). Another principle in normalization is not repeating the same type of information over and over across many fields (your second method having 10 criteria fields).

Of course these are guidelines and you as the developer must decide what is best for the design of your application. Once you have a solid design everything else shold fall into place.

It sounds like what you have is a many-to-many relationship between the criteria and courses. This is difficult but not impossible to model in Access. It usually requires 3 tables. In your case you would have a course table, a criteria table and a linking table which at the least would contain the primary keys from the first two tables. The course and criteria tables will have a one-to-many relationship to the link or detail table. This will not prevent a course showing up more than once on the report however. Attached is a small db I whipped up. Ignore the descriptions as they don’t make sense but I hope the structure is helpful.

ps – It’s in Access97. Didn’t have access to my 2k machine.

Well, this is odd. I got your new post via email but now when I come here it doesn’t show up. I wonder what’s going on?

Your first question about how does tblLink know the course Math supports 3 criteria. I entered those myself in the table. I used the lookup wizard when I created the two fields in tblLink so if you go to create a new record in tblLink you get a drop down list for each field representing the values in the other 2 tables. Therefore each record in tblLink represents a relationship between a course and a criteria and vice versa.

Your second question dealt with the creation of tblLink. A linking table such as this should contain at least the primary key fields from the related tables as foreign keys. The primary key for the tblLink consists of both fields – in other words you can only have one instance when any one course supports one criteria i.e. Math can support Trust only once. This is called a compound key when more than one field comprises the primary key. This type of key is not that uncommon so it is a good concept to learn. When in design view, select both fields and click on the primary key button in the toolbar. This will make the combination of the 2 fields the primary key for the table.

If anything else is not unclear post again – it may or may not show up here but it looks like I will get an email.

I would add to Hans post to use the Is Null expression in the criteria row in the columns which contain missing data. This way you will only replace the missing data and not the existing data.

I’m not sure what you mean. I’ve uploaded another version – this one has the relationships defined in the relationship window. There should be 3 tables as discussed earlier:

tblCourse
tblCriteria
tblLink

tblCourse is related to tblLink in a one-to-many relationship as is tblCriteria to tblLink. So whenever a course is related or supports a criteria there should be a record in tblLink. One advantage of doing it this way verses having a field for each criteria in the course table is if you should add another criteria you don’t need to create an additional field just create a new record in tblCriteria. The same thing holds true if you add an additional course. Hope this clears the fog (which I helped create, sorry).

Looks like you forgot to set the database object.

Sorry about that I forgot I was in a secure workgroup files when I created the first file. Download it again and you should be able to open it now.

Take a look at the form picture property. This may give you what you need.

A couple of things. Try rebuilding the structure of the offending table in the new db then import from the old db and/or export from old db to an ASCII text file then import into new. If there is a way you could exclude the offending record from the export that would be desireable, i.e. use a query as the source for the export.

One problem I see right away is that the rows in the subform are already “filtered” – they represent only the contents of the current box on the main form. Not sure how you’re going to do this without using a separate form.

Can you store the name of the document file in your table?

Your questions are not simplistic but rather basic ones. Table design and normalization are the basis for good database design. Here is a link to a document you should download from the microsoft site. It will help you decide which information should go into which table. Having more than one table containing customer information is not considered good design unless special circumstances (privacy issues e.g.) exist. Take a look at the document – I could go on for days about good design structures.

Click here for the document

As for your report is the report’s record source a table or a query? If it’s a table consider using a query in which you can substitute the customer name for the primary key.