Creating a query using two tables

[aubtiger14]

I have defined the relationship between the two tables. Should the information from the two tables be listed on my new query in the datasheet view or do i need to enter that information?

[Dal Jeanis]

A query shows you what is currently in the database for the particular way of joining the tables that you have told the database to do.

If the query is not showing you any results, then the relationship you have entered is somehow not finding any matched values.

This can be because the data in one field you are matching on is not the same type as the data in the other you are matching on - for instance if a social security number in one table was text, and in the other was a number.

Or, it can be because you are matching on fields that just don't have related data.

to help you more, you'll have to give us more information on the two tables, the data in them, and the query.

[June7]

The query builder should not allow linking on fields that are not same data type. If attempted, the query will error when try to open in datasheet.

But as Dal says, we need more info.

[aubtiger14]

CU #	Customer Name	Street	City	State	Postal Code	Amount Paid	Balance	SR #
AS24	Ashley's Salon	223 Johnson Ave.	Oxford	TN	37021	$1,789.65	$236.99	34
BA35	Beauty for All	1939 Jackson St.	Lowton	TN	37084	$0.00	$275.75	41
BL15	Blondie's on Main	3294 Main St.	Oxford	TN	37021	$1,350.00	$555.00	39
CL09	Casual Looks	3140 Halsted Dr.	Ashton	VA	20123	$1,245.45	$297.95	41
CY12	Curlin Yoga Studio	1632 Clark Ln.	Georgetown	NC	28794	$740.25	$175.86	39
DB14	Della's Beauty Place	312 Gilham St.	Granger	NC	27036	$859.89	$341.78	34
EY07	Environmentally Yours	1805 Broad St.	Pineville	VA	22503	$1,765.00	$0.00	34
FN19	Fitness Counts	675 Main St.	Oxford	TN	37021	$1,976.76	$349.95	41
JN34	Just Natural	2200 Lawrence Ave.	Ashton	VA	20123	$810.78	$450.99	39
LB20	Le Beauty	13 Devon Pl.	Lowton	TN	37084	$1,467.24	$215.99	39
NC25	Nancy's Place	1027 Wells Ave.	Walburg	NC	28819	$675.89	$345.89	34
RD03	Rose's Day Spa	787 Monroe St.	Pineville	VA	22503	$1,024.56	$0.00	41
TT21	Tan and Tone	1939 Congress Dr.	Ashton	VA	20123	$925.75	$265.85	34
TW56	The Workout Place	34 Gilham St.	Granger	NC	27036	$154.95	$870.78	41
UR23	U R Beautiful	578 Central Ave.	Walburg	NC	28819	$0.00	$1,235.00	39

[aubtiger14]

SR #	Last Name	First Name	Street	City	State	Postal Code	Salary YTD	Comm Rate
34	Johnson	Bobbi	43 Third St.	Oxford	TN	37021	$15,250.50	0.05
39	Gutaz	Nicholas	878 Hill Dr.	Georgetown	NC	28794	$17,345.00	0.06
41	Orlon	Jake	982 Victor St.	Ashton	VA	20123	$14,575.00	0.05
55	Sinson	Terry	45 Elm St.	Walburg	NC	28819	$1,240.00	0.04

[aubtiger14]

The query is for the two tables. I've added the SR#,last name, first name fields from the sales rep table and CU#,customer name fields from the customer table. How should the relationship be set up? i thought i did it right but i guess not.

[June7]

Should avoid use of spaces and special characters/punctuation (underscore is exception) in names.

SR # should be primary key in employees table. This value is saved as foreign key in customer table. The query should link on those two fields. You should see records in the query.

You did not post the sql statement of the attempted query.

[Dal Jeanis]

These tables are not "normalized", but I'm going to ignore that for the moment.

When you build a query, you should leave off all the information you don't need. For instance, I can think of no possible reason you'd want a single query to return the address of a client and the annual salary of a salesman. Either one of them might belong in a query that uses both tables, but not both of them. No business need for it, right?

Let's suppose you wanted to see what the salesman's commissions would be on the balance not yet paid by each customer.

1) Open a new query in design view

2) Add the two tables.

3) Make sure there is exactly one line between them, linking the SR# from the customer table to the SR# from the salesman table.
If there are any other lines, then right-click them and delete them. If that line is missing, then click the SR# on one table and drag a line to the other table.

4) Now, right-click the customer table and open the properties pane. Change the name at the top of the properties pane to "T1".

5) Do the same for the saleman table, but change the name to "T2".

6) From the Customer table, select the fields CU#, Customer name, balance, and SR#. (Double-click them to make them show up in the bottom.)

7) From the Saleman table, select lastname, firstname, and comm rate. (Double-click them to make them show up in the bottom.)

8) In the next empty column to the right, paste this in -

CommAmount:  T1.[Balance]*T2.[Comm Rate]

Now, run the query (click the red excamation point) and see what it looks like. It should give you sensible information.

If you switch to SQL view, (at the top left of the screen), then the system should show you some SQL that looks roughly like this (although it will not be laid out so pretty, and the names Table1 and Table2 will be replaced with the names of your customer and salesman tables):

SELECT
  T1.[CU #],
  T1.[Customer Name],
  T1.[Balance],
  T1.[SR #],
  T2.[Last Name],
  T2.[First Name],
  T2.[Comm Rate],
  T1.[Balance]*T2.[Comm Rate] AS CommAmount
FROM
  Table1 AS T1
  INNER JOIN 
  Table2 AS T2
  ON T1.[SR #] = T2.[SR #];