How to query without using the query wizard (1 Viewer)

[jerrywinger]

Hi, I'm very very new to Microsoft Access with no background in computer programming and am now learning how to use query. I have 2 tables and both contains lookup values from each tables. There are also multiple values in some of the fields.

The problem is when I use the simple query wizard, the query shows all the results properly. But when I query using the design view, it only shows a few data.

Is there something wrong with my table ?

 

[Alansidman]

Jerry;
Without seeing your SQL statements, it would be difficult to determine what the issues are. Post the statements here and let us have a chance to help you.
Alan

 

[jerrywinger]

Alan,
I don't know programming and am new to Microsoft Access. In microsoft Access, I used the simple query wizard as well as entered the query in the query design view.

I have 2 tables - 1 for Individual contact details and another is Organisation contact details.
Both have fields that contain lookup values. I have a field called Mail_to which contains value such as Home or Organisation. It is to tell me whether to send the mails to house address or office address.

In the Individual contact table, for the company name, you have to look it up from the Organisation contact table.

In the design query view, when I select these 2 tables and run a simple query, i.e. first_name and mobile no., I only get 2 results. But if I use the simple query wizard to query from the 2 tables, I get a complete results.

I'm not sure what to do as I want to do more complex queries that the simple query wizard won't allow me to but I can't even get results from a simple query at the query design window right now.

 

[Alansidman]

Ok Jerry, Open each of your queries in design view. In the upper left hand corner there is a triangle (looks like what carpenters use). Next to it is a drop down, click on the arrow and select SQL. This is Structured Query Language and is what drives your queries. Copy the code for each query and paste them into a response in the forum. This will then tell us what the differences are between your queries? How you have them set up and what fields you are querying.

Alan

 

[MSAccessRookie]

Hi, I'm very very new to Microsoft Access with no background in computer programming and am now learning how to use query. I have 2 tables and both contains lookup values from each tables. There are also multiple values in some of the fields.

The problem is when I use the simple query wizard, the query shows all the results properly. But when I query using the design view, it only shows a few data.

Is there something wrong with my table ?

Based on the descriptions that you provide, you are probably a victim of the type of Join that is being used.

Most Standard SQL defaults to using an Left Outer Join, which gives you all of the records from the table on Left side of the join and only the matching ones from the right side.

Microsofy Access defaults to using an Inner Join, thereby limiting the results to only rows where the join keys match in both tables.

Change the Design View to use a Left Outer Join and see if that makes any difference

 

[jerrywinger]

thank you very much for both of your advice. I also found out that my tables contained a many-to-many relationships and I have decided to change it 'cos at my level, I don't think I can query it properly.