Linking pieces in two tables to 1 querry

  • Thread starter Thread starter amy
  • Start date Start date
A

amy

I created the linkages and it tells me that something is
wrong and can only open in design view. Here is the
expression. It is long, but something in it is wrong.
Can you help?

SELECT [RH Accounts].[Name of Account], [RH Accounts].
[Account Number], [RH Accounts].[Budget Amount for
FY'04], [Rural Health Master Budget Table].[Date
Processed], [Rural Health Master Budget Table].[Account],
[Rural Health Master Budget Table].[Name of Vendor],
[Rural Health Master Budget Table].[Amount], [Rural Health
Master Budget Table].[Reason for Expense/Income], [Rural
Health Master Budget Table].[Reconciled], [Rural Health
Master Budget Table].[Person Entering Information]
FROM [RH Accounts] INNER JOIN [Rural Health Master Budget
Table] ON ([RH Accounts].[Budget Amount for FY'04] =
[Rural Health Master Budget Table].[Account]) AND (([RH
Accounts].[Name of Account] =[Rural Health Master Budget
Table].[Account]) AND ([RH Accounts].[Account Number] =
[Rural Health Master Budget Table].[Account]));


Thanks,
Amy
 
Your joins don't look like they're correct. There is no way for me to know
that for sure cuz I don't know what your tables look like, but the following
doesn't look correct:

The first join is on [Budget Amount for FY'04] and [Account]. Are those
fields the same type? One looks like a currency field and the other looks
like an ID field, probably a long int.

The second join is on [Name of Account] and [Account]. Are those fields the
same type? Again, one looks like a string field with the actual name of the
account, the other looks like the account number (identifier). Example:
Account #: 100101, Account Name: Accounts Receivable.

Then you join [Account Number] to [Account].

Now that I look at it, you're also going to have a problem cuz you're trying
to link 3 different fields to the same field. This is not an illegal
statement to do so, however, I doubt that that is your intent. It looks
like you're just trying to join the Master Budget table to your table of
accounts. You should only need the join on the Account Number to do that.
You can get rid of the other two.

RJ
 
Look at the ON clause ...
ON ([RH Accounts].[Budget Amount for FY'04] =
[Rural Health Master Budget Table].[Account]) AND (([RH
Accounts].[Name of Account] =[Rural Health Master Budget
Table].[Account]) AND ([RH Accounts].[Account Number] =
[Rural Health Master Budget Table].[Account]));
Click to expand...

What this is saying is, show me all records where the fields named budget
amount, name of account, and account number in one table all contain the
same value as the field named account in the other table. This would also
imply that the four fields were of the same data type, which would be highly
unusual. Normally, one would expect a field with a name like budget amount
to have a numeric data type, while one would expect a field with a name like
name of account to have a data type of text.

I expect the latter is probably what the error message is trying to warn you
about - you're probably trying to join on fields with different data types.
But I strongly suspect that the logic of the query is not really what you
were looking for anyway.

--
Brendan Reynolds (MVP)
(e-mail address removed)

amy said:
I created the linkages and it tells me that something is
wrong and can only open in design view. Here is the
expression. It is long, but something in it is wrong.
Can you help?

SELECT [RH Accounts].[Name of Account], [RH Accounts].
[Account Number], [RH Accounts].[Budget Amount for
FY'04], [Rural Health Master Budget Table].[Date
Processed], [Rural Health Master Budget Table].[Account],
[Rural Health Master Budget Table].[Name of Vendor],
[Rural Health Master Budget Table].[Amount], [Rural Health
Master Budget Table].[Reason for Expense/Income], [Rural
Health Master Budget Table].[Reconciled], [Rural Health
Master Budget Table].[Person Entering Information]
FROM [RH Accounts] INNER JOIN [Rural Health Master Budget
Table] ON ([RH Accounts].[Budget Amount for FY'04] =
[Rural Health Master Budget Table].[Account]) AND (([RH
Accounts].[Name of Account] =[Rural Health Master Budget
Table].[Account]) AND ([RH Accounts].[Account Number] =
[Rural Health Master Budget Table].[Account]));


Thanks,
Amy
Click to expand...
 
Back
Top