Only turns up 1 record when recordsource is query

[ClearlyAnIdiot]

I didn't have this problem when I had used subforms, but there were little bits of thins I had to polish. There are basically 2 tables, and the form is based on the table PKing. It shows the PK and a few irrelevant fields. However, when the recordsource now inner joins the two tables based on the PK, it only turns up 1 record.There are no filters, and nothing is spelled wrong. I checked all of the control sources, and nothing's off. What am I doing wrong?:banghead:
P.S.: The one record that is shows is perfectly fine.

 

[Mihail]

Post the SQL and a pic with the relevant relationships

 

[ClearlyAnIdiot]

SELECT [Company-personID].*, Alternates.Director, Alternates.Alternate FROM Alternates LEFT JOIN [Company-personID] ON [Company-personID].[Company-personID] = Alternates.Director ORDER BY Alternates.Director;

 

[Mihail]

The query is fine but the DB setup seems to be very wrong.

I asked for the DB's relationships, showed in Relationships window (Dateabase tools -> Relationships) , not for what you see in the Query Design Window (this I can understand from the SQL)
Anyway, now, I have huge doubts that your Relationships window is displaying something

Some advices:
1) In names use only A-z chars. No spaces no other characters.
So your tables should be named something like this:
tblAlternates , tblCompanyPersons
and the filelds like CompanyPersonID etc

2) Every table should have a PK (Primary Key)
The best practice is to setup the PK to AutoNumber

3) Learn about databases normalization.
Here is a good description:
http://bytes.com/topic/access/insights/585228-database-normalization-table-structures
but you can google to find out more about this subject.
Without understanding very well the NORMALIZATION you will not be able to design a database.

Understand this concept and, for sure, you will be able to fix your actual issue and more other in the future.

Good luck !

 

[JHB]

However, when the recordsource now inner joins the two tables based on the PK, it only turns up 1 record.There are no filters, and nothing is spelled wrong. I checked all of the control sources, and nothing's off. What am I doing wrong?:banghead:
P.S.: The one record that is shows is perfectly fine.

What if you copy the form's record source to a query, how many records are returned?

 

[ClearlyAnIdiot]

@Mihail , yeah, I actually knew what normalisation was, but I guess I just didn't bother until now, when I figured out that it could mess stuff up. I just changed all of the names to normal, however, and nothing's changed.
@JHB , It only returns 1 record, strangely enough. I tried tinkering with the ORDER BY function, but it just keeps showing only 1 result.

 

[Mihail]

I just changed all of the names to normal, however, and nothing's changed.

Of course. It was just an advice to keep in mind.
Upload your DB (Access 2003 version - I use 2007) and will see how to tweak it.

 

[ClearlyAnIdiot]

Hmm, after a whole bunch of messing around, I changed the code to FROM tblCompanyPersonID LEFT JOIN tblAlternates, instead. However, now the "alternate" and "director" fields are blank on all of the other records (the first one is still perfectly fine).
EDIT Err, nevermind, I just added an OR statement so that the fields would show... Hehe. Nothing left to do here.
EDIT Actually, I've noticed that when I open this form as a subform on another form, it shows all of the existing records (I.E. John Tong is in company 1111, so his name, as well as Peter Chan's, show up when I use the record for company 1111), but when I go to a company without any people in it, it turns up blank.

In fact, the form doesn't seem to allow any edits or new records at all.