microsoft/access/training/courses - Isolating data query

Resolved · Low Priority · Version Standard

Wed 15 Nov 2006, 21:30 Edited on Thu 16 Nov 2006, 16:25 Delegate Rebecca said...

Rebecca has attended:
Access Intermediate course
Access Introduction course

Isolating data query

I am trying to work out what query I need for the following:

I am building a database to keep track of stock. I have designed it to have one table for all different types of stock (there are 8 different types which I have listed in a Lookup). One of these types of stock is FABRICS. FABRICS are then broken down into 10 different types, which I have listed in a second Lookup, however, I only want to be able to select a fabric type if I have selected FABRIC and not when I select one of the other categories. How do I do this?

Thu 16 Nov 2006, 10:08 Trainer Richard said...

RE: Isolating data query

Hi Rebecca,

Before answering your question directly, I want to make sure I understand your requirement.

I think I understand what you have created so far, and want clarity on your end point, to give the best answer.

1.How will you be capturing the data? Via a form, or directly into the table.

2. How many people will use this database? Many or just yourself.

If you are using a form, you could perhaps limit the second field to only be required / shown when the first field = FABRICS.

Let me know your thoughts,and we can progress it from there.

Richard

Fri 17 Nov 2006, 20:15 Delegate Rebecca said...

RE: Isolating data query

Richard,

I am inputting the data directly into the table at present, but once it is set up I intend to use a form. Can I do what I am asking so that it works in both or do I have to choose one or the other?

I am going to be the only on using this database.

Rebecca

Thu 23 Nov 2006, 20:39 Delegate Rebecca said...

RE: Isolating data query

Richard,

Have you replied? I can't seem to access it if you have?

Rebecca

Fri 24 Nov 2006, 16:14 Trainer Richard said...

RE: Isolating data query

Ok, I would suggest the following:

TABLE
If you were entering the data in a table, I would simply leave the Second field empty, unless you were entering a FABRIC. Databases can be designed like this, as you will simply record a blank field.

FORM
You could follow the same thinking using the Forms, however, you could program the object on the form (in this case the second field), to only appear, or contain values when you select fabrics. This does require knowledge of SQL (query language), so that the object can know if the First field contains a specific value.

A simple way of saying this is something like:

<b>Show "lookup list" only IF "Field1" = FABRICS</b>
The SQL code would look very different, as it would contain the names of your fields and lookups.

Richard