Database design query

[Zzumbouk]

I am new to databases, and have spent about a week working through a self-tuition book. I am now trying to construct my first database, the aim of which is to enable me to manage URLs that contain interesting information about store automation technology in the retail industry (an area I research for my job).

Each URL might contain information that relates to one or more countries, one or more products, etc. I wish to be able to search the database to find all the articles which relate to (e.g.) a particular country.

From what I have read, good database design advises against having a table in which each possible product and country is a separate field [with the relevance to each indicated by a simple Yes/No].

As far as I can see, I should create an ID for each URL "URLID", and then have a table which contains with fields for URLID, Country, Product etc. There can then be multiple records attached to each URLID, which indicate the relevance of that URLID.

However, it seems to me that this would make the process of creating records (using a form) very time-consuming, as forms seem to only let you edit/create one record at a time. For example, a URL that is relevant to 1 product and 3 countries, would require me to create 3 records. For a URL that is relevant to 2 products and 4 countries, I would need to enter 8 records. What would be ideal would be to have a form which allows me to create multiple records simultaneously for each new URL. This would ideally be a form which would contain check boxes for each product type (probably only 3 or 4), each country, etc.

I've read and re-read the book I've been using ("Microsoft Office Access 2003: Step-by-Step"), which definitely doesn't answer the question, and I've looked on this site for similar questions, also without success.

Am I missing something obvious? Can anyone help?

Thanks very much,

Tom

 

[KenHigg]

Did you read up on subforms? Maybe that is what you need to use -

 

[Zzumbouk]

Ken,

Thanks for your message - I had already read about sub-forms, and I re-read the material I have, after seeing your message!

It seems that sub-forms could be used to enable me to use a single form to (a) create a record for the URL and (b) create records (tied to the primary key "URLID" created for each URL) to denote the countries/products/etc. that are relevant to each URLID. However, I am looking at 40 countries and 4 products, plus various other dimensions that I have not mentioned here in order to keep things as simple as possible (e.g. 25 different vendors, etc. etc.). For any given URL, the number of records that need to be created would seem to be the number of countries multipled by the number of products (multiplied by the number of vendors, etc. etc.) - this would quickly become impractical.

It may be that I am missing something very obvious here. What I have imagined as a solution, is to have a section (sub-form) for each field, which has the relevant number of check boxes (40 for countries, 4 for products, etc.) to indicate the relevance of the URL. This would then be used to create multiple records (e.g. for a URL that relates to 8 countries and 3 products, all the user would need to do is check the relevant boxes (3 + 8 = 11 in total). [The alternative seems to be to create the entries manually (3 * 8 = 24 in total), each of which would need information in two fields - this would of course take far longer and be very prone to errors].

So, I'm afraid I'm still not sure *how* sub-forms can be used to address the main question: is it possible to eliminate the requirement to create all these records manually?

Thanks,

Tom

 

[Rabbie]

It is certainly not difficult to write VBA code that will create your 24 records using the information in your check boxes. You would, of course, need to be clear how the records were to be formatted etc.