Relationship between two tables

[Dunyazada]

I have two tables, "Style" and "Color". The first has two fields, "Student" and "Type of shoes", with the names of students and their preferred type of shoes respectively. The second has two tables, "Student" and "Color of shoes", with the names of OTHER students and their preferred color of shoes respectively. I need to display only students whose preference is black >or< heels (display their name, preferred style, and preferred color). They must display on one screen. I have tried to do it through creating a select query, but I am not sure about what relationship should be between the two tables, since they are not really related. Also I don't know what criteria to use. Could anybody help?

 

[MStef]

As I can understand you, you don't need 2 tables, and
you don't need a RELATIONSHIPS between these 2 tables.
One tabe is enough.

 

[Dunyazada]

I have to have two tables, because this is an assignment

 

[toast]

If you have to use 2 tables, can you change the layout of the tables?
First table is Students
Second table is Shoes (field for type and field for color)

 

[Dunyazada]

No, I cannot And the students' names are different. The professor said that there are several ways of doing that, so, maybe, I can do it without using a query?

 

[Pat Hartman]

Welcome aboard
Relationships are defined in the relationship window and are used to declare referential integrity. Any table can be joined to any other table(or query) on any fields as long as the data types are compatable. That doesn't mean that the results of the query will make sense. It just means that the query engine will allow it.

I'm trying to figure out the purpose of the assignement and I can't unless it is to teach you about full outer joins. Does that ring a bell? To create an inner join, you would add the two tables to the QBE and draw a join line between the student columns. That will return one row of data for every record in tblA with a matching student in tblB which could result in multiple rows per student. Since these are supposed to be "favorites", your tables should contain no more than one row for each student (verify this so you will understand the resultset). Then you need to determine if all students have rows in both tables. If not, then the simple inner join will not produce the set of data the professor is looking for.

If I guessed right about the purpose of the exercise, this should get you started. You will ultimatly need a union query that includes an inner join, a left join, and a right join. This will produce what is called a "full outer join". Then you would apply the specified criteria against the union query.

 

[Dunyazada]

Thanks
The purpose, as he said, is for us to get to know Access (the course is about Excel, actually). This is the description of the assignment:
"Please enter the following two tables into an access database:

Call the first table "style"

Andrea Flats
Brianna Heels
Morgan Sneakers
Stephanie Wedges
Ana Sandals
Natalia Boots
Angelika Pumps
Josh Heels
Moises Heels

Call the second table "Color"

Brad Blue
Sebastian Red
Morgan Animal Print
Stephanie Black
Josh Beige
Angelika Black
Andrea Black
Daniela Black
Mikaylo Black

Enter the above tables.

Part II
-------

Now display only students whose preference is black >or< heels - display their name, preferred style, and preferred color. They must display on one screen.

There are several ways to do this; select one. (Hint: You do not make a table with three fields)".

Do you think I should use a query, or there is another way to do it?

 

[Pat Hartman]

Since there is no overlap in students from the two tables, the request doesn't make any sense because one student will not have a preferred style AND a preferred color. As I said, I don't know what the teacher is trying to teach you with this made up example. If this were your real life data, you would never create a query that joins the tables because it would never return any rows. Just because tables have what appear to be common keys doesn't mean that it makes sense to join them.

You could still cobble something together using a union query as I described earlier but it would make no sense.

 

[Dunyazada]

What if they don't have to have a style and a color at the same time? Is there a way to display only the students from the first table with "heels"?

 

[MSAccessRookie]

What if they don't have to have a style and a color at the same time? Is there a way to display only the students from the first table with "heels"?

It would be easy to create two separate Queries that each get the required information from one of the Tables.

A standard Query has three parts, the third of which is optional and used as required. Research the three parts and determine how to use them in your Queries. Get back to us with any further questions.

1. SELECT { Field(s) that you want to display }
2. FROM { Table that contains the Field(s) }
3. {OPTIONAL} WHERE { Condition(s) for selection }

It would be more difficult to get all of it at the same time. As Pat said, A UNION Query would be one approach, but I would think that would be a more complex solution than your teacher would expect from a beginner.

 

[Dunyazada]

Hm... I see.
Can I use a pivot table?

 

[MSAccessRookie]

Hm... I see.
Can I use a pivot table?

While a Pivot Table most certainly is a possibility to consider, it would also be more about presentation than content. It is not possible to say whether a Pivot Table would be the best approach until the required Query has been defined. You should start with a basic Query that defines your Dataset, and then worry about the presentation.

 

[Pat Hartman]

Since there is no relationship between the two tables (even though they both contain a student field), the only way to get the data returned in a single query is by using a union query. Have you investigated union queries yet? You could also attempt to get a clarification from the teacher.

Once you have the union query created, you can use the union as the basis for a crosstab (queries are based on tables or other queries) since that will make the display look the way you want.