Multiple field error on report run...

[cheuschober]

Anyone have an idea why I'm recieving an error to the extent of, a field I am requesting for my report supposedly exists in multiple tables??

Basically, my query/table setup is such that I have a table for enterting expense information [Expense]. On the [Expense] table can be found any number of fields including a [PayeeID] field and a [PaymentCode] field (value list).

The [PayeeID] field is related to my [Payee] table with specific [Payee] names related to the generated unique [PayeeID]. Both tables are joined on [PayeeID] with RI enforced.

I have a query, [TransactionsBanking], that basically acts as a filter for a specific [PaymentCode]. In this query I also included the [PayeeName] from the [Payee] table. Additionally I calculate an [Invoice Date] field to give each record a specific [InvoiceDate].

In another query [SubtotalsBanking] I calculate a subtotal [InvoiceSubtotal] based upon my [InvoiceDate] field.

In a third query, I combine the contents of the first and second queries (the SQL found below):

SELECT [TransactionsBanking].[InvoiceDate], [TransactionsBanking].Date, [TransactionsBanking].[ExpenseCode], [TransactionsBanking].Amount, [SubtotalsBanking].[InvoiceSubtotal], [TransactionsBanking].Payee
FROM [TransactionsBanking] INNER JOIN [SubtotalsBanking] ON [TransactionsBanking].[InvoiceDate]=[SubtotalsBanking].[InvoiceDate]
ORDER BY [TransactionsBanking].[InvoiceDate], [TransactionsBanking].Date;


Unforunately when I attempt to use this third query [TransactionsBanking II] to run a report I recieve this error:

The specified field [Payee] could refer to more than one table listed in the FROM clause of your SQL statement.

Now, according to the help file (which we all know is so reliable) this is because I haven't fully defined which field comes from which table in my select statement, however, as far as I can tell I have. Another odd occurance, however, is found that in the third query, the [Payee] field, when run, is given the heading "TransactionsBanking.Payee" instead of "Payee" as I expected, even though in the first query it seems to appropriate the field properly.

I'm just stumped on why this is happening. Any ideas?

Thanks,
~Chad

 

[cheuschober]

Solution stumbled upon...

Hi, just thought I'd mention that I seem to have stumbled upon a solution.

I decided to allow my first query to work off my [PayeeID] instead of link to my [Payee] name. So, when I put together my third query I established my relationship from my existing [PayeeID] to my [Payee] table and the subsequent payee's name.

This seems to have solved the error but I'm admittedly still a little curious as to know why this happened?

Anyone care to elaborate?

Thanks,
~Chad