Hello!

I'm creating an MS Access DB. I have two tables linked to each other so that one record in one table can relate to several records in the second table.

When I open the main table (the first one) as a datasheet and fill with some records, I get a very narrow additional column on the left with plus signs in it. If I click on one, a box opens with all the rows from the second table that are related to the particular record.

The problem is, I want to use the queries, but that plus sign doesn't appear for queries, only for tables.

Can someone, please, explain, how can I get them on my query, as they're very convenient?

Thanks!
Janis

janise

The Plus signs you see is showing the relationship between the associated tables.

if you were to create a new query add the two relational tables you will see an automatic link between the two.

another note: when you want to graphically see how you are constructing your tables, queries and views

click on the menu bar and relationships. here you can see and or create your database by adding/creating tables and its relationships, queries and views. It is very useful when you need to see your table structure.

Jaime E. Maccou

Short Answer: You can't.

If you have a single record in the main table and two records in the other table, for example, the query will return two rows, so no need for the plus signs.

mmcdonal

Ow, that's bad. :(

I didn't put any columns from the other table, as I was hoping for the plus sign to show them when needed. It's not so convenient to have a separate row for each record from the other table, as all the data from the first table is doubled then. And if you enter the data in this view, you must enter it twice all the time; it's quite disturbing, even if you use [Ctrl]+['].

Thanks, Jaime, for the reply! Unfortunately simply adding the other table in the query's design view didn't help either.

Janis

Oh, in case you're in the habit of filling data directly into tables, this practice is considered a no-no. You should create forms to accomplish this task.

Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

And why is it considered so? It's very convenient. Easy to overview the data, you can see/edit the previous records, what you've entered recently, and if needed can easily scroll through the records.

AND there's that convenient plus sign. Can you get that in a form?

Janis

Yes, you can if you program the form correctly. If you're the only person using the database and you intend on keeping the number of records small, writing into a table is convenient and probably won't hurt anything. But for something shared or where there is lots of data, it's not good.

• You can't do effective error trapping.
• Validation of data is limited.
• Formatting of data is limited.
• You can't offer help to people readily.
• Opening other tables and reports based on criteria is difficult.

Data integrity and reliability suffer. And that "convenient plus sign" is correctly handled using form and subforms.

It's just considered bad practice. Anyone else want to chime in?

Well, there's planned to be quite a lot of data, but it's very simple (as you can see, only two tables necessarry (at least, I think so currently) with only a few fields in each), and it will supposedly filled (and used) by one person at a time.

But I'll take a look at the subform feature. I'm quite a beginner. ;)

Thanks,
Janis

Um... "one person AT A TIME"?! This means you're letting a group of people enter data. Yikes! It doesn't matter than they're doing it one at at time. You're asking for major data integrity loss.

I think you better get some training on forms and subforms... and quickly!

Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division