CAN I SOLVE THIS QUERY PROBLEM WITHOUT CODE? 1

[chubby]

I need a query that’s gets data from to different tables and joins prospective gains personnel data with personnel already present at the command data. My problem is, I can’t get the prospective gains information in a query with personnel already present, its either one or the other and I try changing query joins.

My goal is to have the both pieces of information in a query I can sort by BC. (Billet Code= job title and status) Once placed in a report the end-user can then see his or her replacement.

The tables are joining by Social security numbers (I had to join the tables to query the two).
Table 1: Personal onboard (1100 personnel presently at the command)
Table 2: PG List (Prospective gain list personnel inbound in the next 3 to 6 months)
Why can’t I grab the information I need in my query to make a report showing who's replacing who?

 

[tlbroadbent]

If Table 1 contains onboard personnel and Table 2 contains the inbound personnel how can they be joined by social security number? Does Table 2 contain the SSN of the person being replaced by the inbound person? In order to join the tables they must have a common column such as BC or JobID. Please post your table schemas and relationships. The narrative you've provided thus far is too sketchy to allow decent analysis. Terry

http://members.home.net/tlbroadbent/sqlarticles.htm

"I shall try to correct errors when shown to be errors, and I shall adopt new views so fast as they shall appear to be true views." - Abraham Lincoln

 

[chubby]

Okay, I think I understand what you are saying. Let's see if I'm thinking on the same page as you. If I change my relationship to BC then I can query related information from the two tables???

 

[tlbroadbent]

Yes... If each table contains BC (or the columns that compose BC) you can JOIN on those column(s). As I said, I can't tell for sure because I don't know the content of the tables or even the table design. Terry

http://members.home.net/tlbroadbent/sqlarticles.htm

"I shall try to correct errors when shown to be errors, and I shall adopt new views so fast as they shall appear to be true views." - Abraham Lincoln

 

[chubby]

Thanks a million, I know I got it!!! After changing the relatinoship I kept getting query fail to run and would display the msg: "Query contains ambiguous outer joins", no matter how I changed the joins. So, I broke my query into two queries, and use those two queries as the basis for my final query. I got what I was asking for. I felt like I really knew what I was doing, but only for a second...
Chubby