How do I do a Query with 3 joins?

  • Thread starter Thread starter David S. Calef
  • Start date Start date
D

David S. Calef

I need help creating a report that takes transactions in one table,
and looks up percents in a second table:

Table1: Account
Code
type
totalamount


Table2 Account
Code
Type
Percent1
Percent2
Percent3
Percent4

There are multiple transactions in table 1 that a report (or query) must
look up the 4 percents for that transactions Type, by Code and by
Account.... then I use the 4 percents to calculate the dollars splits I need
in the report. Each separate report will be for just one account that I
specify when I run the account.

If I could do this in a query, great. But how do I do a query with multiple
joins. I would do a join to table two on Account, plus Code, plus Type. I
can get to the Account, and have percents returned. As soon as I add a
second I get nothing.

If I need to do this in VBA....., where?

Or is an approach using Crystal easier.

I have done simple reports and simple queries for years. This I need a help
on how to accomplish.....

Thanks,

David
 
If I could do this in a query, great. But how do I do a query with multiple
joins. I would do a join to table two on Account, plus Code, plus Type. I
can get to the Account, and have percents returned. As soon as I add a
second I get nothing.
Click to expand...

In that case there is no record in Table2 which EXACTLY matches the
value of Code. What are the datatypes of Code in the two tables? Are
either of the tables polluted with Microsoft's abominable Lookup field
type (which will display one value for Code while storing a different
value, and the concealed value will be used in the linkage)?

John W. Vinson[MVP]
 
The datatypes of Code and Type in the two tables is text, both with length
of 4, both formatted to uppercase (">").

The datatype of Account in the two tables is Integer

I get a fine match on Account plus Code, but
no match on Account plus Code plus Type
no match on Account plus Type
no match on just Type

I have renamed Type to BPool. No change.

I have done all kinds of silly things, like reverse the order of fields,
removing the format to uppercase, retyping the name, retyping the
length.....

I know this is something really simple.... and I am feeling really
stupid.....

David
 
The datatypes of Code and Type in the two tables is text, both with length
of 4, both formatted to uppercase (">").

The datatype of Account in the two tables is Integer

I get a fine match on Account plus Code, but
no match on Account plus Code plus Type
no match on Account plus Type
no match on just Type

I have renamed Type to BPool. No change.

I have done all kinds of silly things, like reverse the order of fields,
removing the format to uppercase, retyping the name, retyping the
length.....

I know this is something really simple.... and I am feeling really
stupid.....

David
Click to expand...

Very odd. Please open the Query that's not working in SQL view and
post the SQL text here. The fieldname shouldn't matter especially if
you enclose it in [brackets], but I've not seen any trouble naming a
field Type.

John W. Vinson[MVP]
 
Here is the SQL:

SELECT ToteTransacts.ID, ToteTransacts.Date, ToteTransacts.Account,
ToteTransacts.Track, ToteTransacts.BPool, ToteTransacts.Type,
ToteTransacts.Wager, ToteTransacts.Winning, ToteTransacts.Cancel,
ToteTransacts.Refund, ToteTransacts.Deposit, ToteTransacts.Withdraw,
ToteTransacts.Adjust, ToteTransacts.Interest, ToteTransacts.Withhold,
ToteTransacts.Charge, ToteTransacts.Pending, ToteTransacts.Balance,
ToteTransacts.Transaction, ToteTransacts.Basis, ToteTransacts.Race,
ToteTransacts.Runners, ToteTransacts.Source, ToteTransacts.Group,
ToteTransacts.Time, CodePercents.TComm, CodePercents.PComm,
CodePercents.PBonus, CodePercents.POver, CodePercents.Host
FROM ToteTransacts LEFT JOIN CodePercents ON (ToteTransacts.BPool =
CodePercents.BPool) AND (ToteTransacts.Track = CodePercents.Track) AND
(ToteTransacts.Account = CodePercents.Account);

David

John Vinson said:
The datatypes of Code and Type in the two tables is text, both with length
of 4, both formatted to uppercase (">").

The datatype of Account in the two tables is Integer

I get a fine match on Account plus Code, but
no match on Account plus Code plus Type
no match on Account plus Type
no match on just Type

I have renamed Type to BPool. No change.

I have done all kinds of silly things, like reverse the order of fields,
removing the format to uppercase, retyping the name, retyping the
length.....

I know this is something really simple.... and I am feeling really
stupid.....

David
Click to expand...

Very odd. Please open the Query that's not working in SQL view and
post the SQL text here. The fieldname shouldn't matter especially if
you enclose it in [brackets], but I've not seen any trouble naming a
field Type.

John W. Vinson[MVP]
Click to expand...
 
Here is the SQL:

SELECT ToteTransacts.ID, ToteTransacts.Date, ToteTransacts.Account,
ToteTransacts.Track, ToteTransacts.BPool, ToteTransacts.Type,
ToteTransacts.Wager, ToteTransacts.Winning, ToteTransacts.Cancel,
ToteTransacts.Refund, ToteTransacts.Deposit, ToteTransacts.Withdraw,
ToteTransacts.Adjust, ToteTransacts.Interest, ToteTransacts.Withhold,
ToteTransacts.Charge, ToteTransacts.Pending, ToteTransacts.Balance,
ToteTransacts.Transaction, ToteTransacts.Basis, ToteTransacts.Race,
ToteTransacts.Runners, ToteTransacts.Source, ToteTransacts.Group,
ToteTransacts.Time, CodePercents.TComm, CodePercents.PComm,
CodePercents.PBonus, CodePercents.POver, CodePercents.Host
FROM ToteTransacts LEFT JOIN CodePercents ON (ToteTransacts.BPool =
CodePercents.BPool) AND (ToteTransacts.Track = CodePercents.Track) AND
(ToteTransacts.Account = CodePercents.Account);
Click to expand...

This is perplexing. You're CERTAIN that none of the tables have any
Lookup fields? Check the Lookup tab on the BPool and Track fields in
both tables to be sure that they say TextBox instead of Combo. And
you're SURE that there is in fact data in CodePercents that matches?
(Case doesn't affect the match; Access joins are insensitive to case).
And are these both local Access tables, or linked from some other
database engine?

John W. Vinson[MVP]
 
This is perplexing. You're CERTAIN that none of the tables have any
Lookup fields? Check the Lookup tab on the BPool and Track fields in
both tables to be sure that they say TextBox instead of Combo. And
you're SURE that there is in fact data in CodePercents that matches?
(Case doesn't affect the match; Access joins are insensitive to case).
And are these both local Access tables, or linked from some other
database engine?

John W. Vinson[MVP]
Click to expand...


Did I or did I not say this was something simple and I was feeling really
stupid?

Well, to make a long story short, after rebuilding another simple version of
this and seeing it work, and after loading my data from the old database and
seeing it not work.... here is what I discovered......

The lookup tabs are on textbox, and the data sure as matches....... as you
look at it.

Well, it LOOKS like it matches....... But..... There is a space before
every piece of data in the Pool. It is an import from a text file, and the
import is lightly off. A manual record works....

Thank you so much for your help, and sorry to run the needless red flags for
help....

David
 
Well, it LOOKS like it matches....... But..... There is a space before
every piece of data in the Pool. It is an import from a text file, and the
import is lightly off. A manual record works....
Click to expand...

Oh ARRGH. How frustrating!

Glad you got it figured out.


John W. Vinson[MVP]
 
Back
Top