PLEASE HELP! so confused with blank query...

[allen_jr]

Hello! i'm new, obviously

I have 3 books open on my desk, and have searched online, and I cant figure this out... I am trying to do something so simple, but it isnt working...

I have 2 tables... one with a list of companies, addresses, ph#'s, etc.. (customers) and their CompanyID (which I made the primary key). The other table is a list of orders, which includes their company name and companyID (not all the other related info)

These tables are linked via CompanyID field relationship

I am trying to make a simple query that shows the Company Name, First Name, Last Name and a few other fields from each table, but when I make the query, it shows up with no fields populated. I can successfully make a query work if I don't involve both tables, so I know the Query function works

I cant figure out why this isnt working... plese help!!

Thanks so much!

Allen

 

[MSAccessRookie]

Hello! i'm new, obviously

I have 3 books open on my desk, and have searched online, and I cant figure this out... I am trying to do something so simple, but it isnt working...

I have 2 tables... one with a list of companies, addresses, ph#'s, etc.. (customers) and their CompanyID (which I made the primary key). The other table is a list of orders, which includes their company name and companyID (not all the other related info)

These tables are linked via CompanyID field relationship

I am trying to make a simple query that shows the Company Name, First Name, Last Name and a few other fields from each table, but when I make the query, it shows up with no fields populated. I can successfully make a query work if I don't involve both tables, so I know the Query function works

I cant figure out why this isnt working... plese help!!

Thanks so much!

Allen

Did you make the query by hand, or with the query wizard? Using the query wizard, you would select BOTH tables, and join them via dragging one join item on top of the other. Then you right click thejoin line to set the join type. I suggest you start with inner (all must be in both to show up). If no records show up, there may not be any matches, so try left and right joins to verify.

 

[allen_jr]

Thanks! I don't understand exactly what you are telling me (Im using Access 2007, if that matters)

I don't have the ability to "drag" the tables on top of each other. I have used the wizard and the design view, neither works. They both allow me to select fields form both tables and form a query. When I click "run" I basically just get what looks like a brand new, unpopulated database with the fields that I am trying to query.

Both tables do not have the same exact fields, but they both share a common field (CompanyID) which is set as the primary key in 1 of the tables... the tables are linked via a relationship, in which I "dragged" Company from Table 1 on top of CompanyID in table 2

Thanks again, sorry this is so naive sounding...

 

[MSAccessRookie]

Thanks! I don't understand exactly what you are telling me (Im using Access 2007, if that matters)

I don't have the ability to "drag" the tables on top of each other. I have used the wizard and the design view, neither works. They both allow me to select fields form both tables and form a query. When I click "run" I basically just get what looks like a brand new, unpopulated database with the fields that I am trying to query.

Both tables do not have the same exact fields, but they both share a common field (CompanyID) which is set as the primary key in 1 of the tables... the tables are linked via a relationship, in which I "dragged" Company from Table 1 on top of CompanyID in table 2

Thanks again, sorry this is so naive sounding...

In the design view, do the following:

1. Select BOTH tables to use for input.
2. Locate the CompanyID Field from the Companies Table, and the CompanyID Field from the Orders Table.
3. Left Click on the CompanyID Field in the Companies Table, and drag the mouse to the location of the CompanyID Field from the Orders Table.
4. When you let go of the mouse button, a line should form between the two tables, linking the CompanyID Fields. Right click on the line and choose "Table Join Properties" to get a Table Join Options form. On the form, choose "Only include rows where the joined fields from both tables are equal.", and then click OK.
5. Execute the query and all records common to both tables should be displayed.

 

[allen_jr]

Thanks again...

I tried your process, step by step. twice.

This is why I am so frustrated. I have followed your instructions, and instructions from all 3 books around me, and I don't get it. I'm not even that dumb

Seems like I am doing everything right. I can get a query to work if I don't use linked tables, maybe the problem is in there somewhere? If i try a query from just 1 table, I can make it work... BUT, I want to pull the Company Name, First Name, Last Name and CompanyID from table one and combine it with ItemsPurchased, Total Due, Deposit, and Balance Due from Table 2 (all numbers inserted manually, no calculated fields - dont event know how to do that yet)

I just dont get it, I am so frustrated....

Thanks again!

Allen

 

[MSAccessRookie]

Thanks again...

I tried your process, step by step. twice.

This is why I am so frustrated. I have followed your instructions, and instructions from all 3 books around me, and I don't get it. I'm not even that dumb

Seems like I am doing everything right. I can get a query to work if I don't use linked tables, maybe the problem is in there somewhere? If i try a query from just 1 table, I can make it work... BUT, I want to pull the Company Name, First Name, Last Name and CompanyID from table one and combine it with ItemsPurchased, Total Due, Deposit, and Balance Due from Table 2 (all numbers inserted manually, no calculated fields - dont event know how to do that yet)

I just dont get it, I am so frustrated....

Thanks again!

Allen

As the teacher said in the movie "The Karate Kid": "First Learn to Walk, then Learn to Fly"

The only thing I wanted you to do was to get the tables joined and have ANY data on the datasheet. We can fine tune it as we go. Did you get any data? If not, please restate the steps that you took.

 

[allen_jr]

Yes, the tables are joined by the CompanyID field. I can see the line. When I right click the line, and choose relationship type, it is just as you requested...

No, my query datasheet does not have any information in it... It comes up with the fields that I want in there, but the cells do not have any data, it looks like a new sheet... the upper left corner cell has "(NEW)" in it, as if I was about to start typing a new sheet...

Thanks again, this is so frustrating for me, I cant imagine how hard it must be to try to decipher based on my writings...

 

[MSAccessRookie]

Yes, the tables are joined by the CompanyID field. I can see the line. When I right click the line, and choose relationship type, it is just as you requested...

No, my query datasheet does not have any information in it... It comes up with the fields that I want in there, but the cells do not have any data, it looks like a new sheet... the upper left corner cell has "(NEW)" in it, as if I was about to start typing a new sheet...

Thanks again, this is so frustrating for me, I cant imagine how hard it must be to try to decipher based on my writings...

OK. This means that there are no exact matches found based on the selected columns.

Next thing to try is to open the tables and verify that there are matches that are being missed. If any records are being missed, then do the following:

1. Open up the Design View again, right click on the line joining the two tables, and choose "Table Join Properties" to get a Table Join Options form. On the form, choose "Include ALL Records from the Companies Table, and only those records from the Orders Table where the joined fields from both tables are equal.", and then click OK. Execute the query and all records from the Companies Table, as well as any records in common with the Orders Table should be displayed.
2. If nothing still shows, then repeat step 1, switching the table names, and all records from the Orders Table, as well as any records in common with the Companies Table should be displayed.

Let us know what happens

 

[allen_jr]

GOT IT!!!

I figured it out before your post, just now... I dont have time to explain, bt I will later... basically, I didnt want autonumber to start at "1" for companyID, so I created some kind of "mask" or format rule by using "\10000" in the design view... but, in the 2nd table, i actually typed in "10001", "10002" for companyID's (because I couldnt use autofill)...

So, the fields werent matching...

THANKS SO MUCH!

Gotta get this stuff printed and off to UPS!!!

THANKS FOR HELPING!!!