Columns in Query don't match

[WillM]

Hi, I am a bit of a novice to Access, so please forgive me ahead of time. I have scoured the internet looking for a solution to my problem and I can't seem to find one (or at the very least I do not understand it if I see it).

I have several tables that I need to join together to create a single form for multiple entries.

Job Table, OPR Table, Organization, Program List Table, Program Notes, Rank, Reference Table, and Status Table.

Ultimately, I need to be able to display all of these in one form and allow for adding/editing notes from the Program Notes section. I also need to be able to let the user look up all info by selecting the Program Name and have the other field populate correctly.

The issue I am running into is that I cannot get the Program Notes table to join to the Program List table correctly.

This is what I attempted to use in SQL:
SELECT [Program Name], [Status], [Reference], [Self-Inspection], [IG Checklist], [Continuity Book], [Bragging Paper], [Program Strengths], [ORI Reports], [Best Practices]
FROM [Program List Table]
UNION
SELECT [Log Date], [Log Entry]
FROM [Program Notes]
;

However, I keep getting an error. I have read that it is because I don't have the same amount of columns, but the tables don't have the same information.

I have also tried to do it through a normal query and through a third table, however I get errors about ambiguous outer joins and I can't seem to make that work either.

I am lost and stumped. Any help would be appreciated.

Thank you.

 

[namliam]

What you are trying to do, i believe, is a JOIN not a UNION..... however to do so you need to have a common column in the two tables, a Key and Foreign Key
Something along the lines of
select ...
from [Program List Table]
join [Program Notes] on Key = ForeignKey

FYI
It is "good practice" to not have any spaces in your table or column names...
Also to PRE fix your tables, not postfix....
i.e. tblProgramList instead of Program List Table

 

[jdraw]

Here's a tutorial that will lead you from a description of your business issue/opportunity to a proper set of tables and relationships. Before you get to forms etc, you should ensure your table design and relationships support your business.

Getting your tables designed is critical to having access to your data.

Also, using a naming convention that does not allow embedded spaces in table or object names will save you a lot of debugging time.

Good luck.

 

[WillM]

What you are trying to do, i believe, is a JOIN not a UNION..... however to do so you need to have a common column in the two tables, a Key and Foreign Key
Something along the lines of
select ...
from [Program List Table]
join [Program Notes] on Key = ForeignKey

FYI
It is "good practice" to not have any spaces in your table or column names...
Also to PRE fix your tables, not postfix....
i.e. tblProgramList instead of Program List Table

Thank you, I do have the "Program Name" as the PK for the Program List table and it is a FK in the Program Notes table. However, I get the ambiguous joins error when I try to combine them through a query.

This is what the two tables look like:

Program List Table:
Program Name - Text (PK)
Status - Text (drop down box from another query)
Reference - Text (drop down box from another query)
Self-Inspection (Yes/No)
IG Checklist (Yes/No)
Continuity Book (Yes/No)
Bragging Paper (Yes/No)
Program Strengths (Yes/No)
ORI Reports (Yes/No)
Best Practices (Yes/No)

Program Notes:
PNID - AutoNumber (PK)
Log Date -Date/Time
Log Entry - Text
Program Name - Text

However, when I use the regular query wizard or query design I get ambiguous outer join errors or when I use the SQL part it gave me the columns error.

If I read what you are saying correctly, I need to code it like:

select [Progam Name] through [Best Practices]
from [Program List Table]
join [Program Notes] on Program Name = Program Name

Is that correct? Sorry if I seem a little dense...I have been banging my head against this for two weeks trying to work it out on my own.

 

[namliam]

Much like your naming convention problem, having a TEXT field as a PK is a "bad idea" and/or a major breach of "best practice", I strongly suggest you use an autonumber field for the relationships between tables

Your query would be right, bar the fact that you have spaces in your column names....

select AnyNumberOfFieldsHere
from [Program List Table]
join [Program Notes] on [Program Name] = [Program Name]

That should be a valid query.... Just this sample goes to show the best practice rule also stated by JDraw to not use spaces or other "special characters" in your column and table names.

When joining two tables, it should be IMPOSSIBLE to get the "ambigious join" error that requires a minimum of 3 tables unless you have a simular problem with the spaces

 

[Galaxiom]

select AnyNumberOfFieldsHere
from [Program List Table]
join [Program Notes] on [Program Name] = [Program Name]

The table names would need to be specified in the join ON clause.

select AnyNumberOfFieldsHere
from [Program List Table]
join [Program Notes] 
on [Program List Table].[Program Name] = [Program Notes].[Program Name]

 

[namliam]

Still wouldnt produce the Ambigious join, but will have caused Ambigious definition (or something along those lines)

Perhaps the OP got them mixed up, thanks for spotting that Galaxiom

 

[WillM]

Thank you...I went in and renamed all the tables and set autonumber for PK.

Just as an FYI, when I tried the snippet of code posted above (before table name changes), it gave me a syntax error in FROM.

Also, the error I received originally was the ambiguous join error, not definition. Perhaps changing the names will fix it.

Thank you all for your help.

 

[WillM]

I am completely reworking the database from the ground up. Thank you again folks, I appreciate your help. I will let you all know how it goes.