Not returning correct

  • Thread starter Thread starter pdehner
  • Start date Start date
P

pdehner

I have 3 tables that I wish to create a report
Table Funds
Fields - AccountID, Fund Number, Name, Department, Fed_State_Other
Table Allocations
Fields - AccountID,
TransactionID,FY,AllocationAMT,TransDate,TranDescr,TranAmt, Notes
Table FundTransactions
Fields - TransactionID, FY, ReceiptNum, TransDate,TranDescr,
TransAmt,AccountID,Notes

I hae the tables designed this way because we receive allocations for a
fund, then we request funds from those allocations then we receive funds not
only from the request but some funds we don't need to request funds.

I need a report that is grouped by Fed_State_Other, then by Department It
needs to list the allocation, the requested funds (could be numerous
requests), then the funds received. This should be compiled based on the FY
(fiscail year) entered.
I have tried joins both right, left and inner. I can not make this work.
I join funds and allocations then join (in a seperate query) transactions
but I either get duplicate transactions or some funds not even listed.

Any help is much appreicated.
Thanks
 
I think you need to first create a union query of your transactions. Then
join this union query to your funds table.
 
I did create a union query for both the transaction table and the fund
allocation table but when I did this I can't tell which is which for the
report???

SELECT [AllocationRequestAmount] AS [AMT], [AllocationRequestDate] AS
[TDate], [AllocationDescription] AS [Desc], [FY] AS [Fiscal], [AccountID] AS
[Account] FROM [FundsRequested]
UNION SELECT [TransactionAmount] AS [AMT], [TransactionDate] AS [TDate],
[TransactionDescription] AS [Desc], [FY] AS [Fiscal], [AccountID] As
[Account] FROM [FundTransactions];

I need to know which is allocation request and which is transaction. Any
Ideas?
 
Just add another column/field in your selects. You should use UNION ALL and
you can remove the "As..." from all but the first SELECT. I would also use
Descr rather than Desc since Desc is the abbrev for Descending.

SELECT "Funds Requested" as SourceTable, [AllocationRequestAmount] AS [AMT],
[AllocationRequestDate] AS [TDate], [AllocationDescription] AS [Descr], [FY]
AS [Fiscal], [AccountID] AS [Account]
FROM [FundsRequested]
UNION ALL
SELECT "Fund Transactions", [TransactionAmount], [TransactionDate],
[TransactionDescription], [FY] , [AccountID]
FROM [FundTransactions];

--
Duane Hookom
Microsoft Access MVP


pdehner said:
I did create a union query for both the transaction table and the fund
allocation table but when I did this I can't tell which is which for the
report???

SELECT [AllocationRequestAmount] AS [AMT], [AllocationRequestDate] AS
[TDate], [AllocationDescription] AS [Desc], [FY] AS [Fiscal], [AccountID] AS
[Account] FROM [FundsRequested]
UNION SELECT [TransactionAmount] AS [AMT], [TransactionDate] AS [TDate],
[TransactionDescription] AS [Desc], [FY] AS [Fiscal], [AccountID] As
[Account] FROM [FundTransactions];

I need to know which is allocation request and which is transaction. Any
Ideas?

Duane Hookom said:
I think you need to first create a union query of your transactions. Then
join this union query to your funds table.
Click to expand...
Click to expand...
 
Thanks and I started working on this and realized I had a major problem
because I had allocations and requests in the same table. So now I have
three tables.
allocations
requests
transactions

can I create a union query on three tabales? Never have done it before?
then my next concern is how when I create a new type field (with the union
query) for either a, r, or t ... how do I take the results and total them on
a report.??
I need it show
FUNDNumber FUNDNAME
Allocations Date
amount
Requests Date
amount
Transactions (receipts) Date
Amount
then total each before going to the next fund.
any thoughts.
thanks for pointing me in the direction to create another table.
Duane Hookom said:
Just add another column/field in your selects. You should use UNION ALL and
you can remove the "As..." from all but the first SELECT. I would also use
Descr rather than Desc since Desc is the abbrev for Descending.

SELECT "Funds Requested" as SourceTable, [AllocationRequestAmount] AS [AMT],
[AllocationRequestDate] AS [TDate], [AllocationDescription] AS [Descr], [FY]
AS [Fiscal], [AccountID] AS [Account]
FROM [FundsRequested]
UNION ALL
SELECT "Fund Transactions", [TransactionAmount], [TransactionDate],
[TransactionDescription], [FY] , [AccountID]
FROM [FundTransactions];

--
Duane Hookom
Microsoft Access MVP


pdehner said:
I did create a union query for both the transaction table and the fund
allocation table but when I did this I can't tell which is which for the
report???

SELECT [AllocationRequestAmount] AS [AMT], [AllocationRequestDate] AS
[TDate], [AllocationDescription] AS [Desc], [FY] AS [Fiscal], [AccountID] AS
[Account] FROM [FundsRequested]
UNION SELECT [TransactionAmount] AS [AMT], [TransactionDate] AS [TDate],
[TransactionDescription] AS [Desc], [FY] AS [Fiscal], [AccountID] As
[Account] FROM [FundTransactions];

I need to know which is allocation request and which is transaction. Any
Ideas?

Duane Hookom said:
I think you need to first create a union query of your transactions. Then
join this union query to your funds table.

--
Duane Hookom
Microsoft Access MVP


:

I have 3 tables that I wish to create a report
Table Funds
Fields - AccountID, Fund Number, Name, Department, Fed_State_Other
Table Allocations
Fields - AccountID,
TransactionID,FY,AllocationAMT,TransDate,TranDescr,TranAmt, Notes
Table FundTransactions
Fields - TransactionID, FY, ReceiptNum, TransDate,TranDescr,
TransAmt,AccountID,Notes

I hae the tables designed this way because we receive allocations for a
fund, then we request funds from those allocations then we receive funds not
only from the request but some funds we don't need to request funds.

I need a report that is grouped by Fed_State_Other, then by Department It
needs to list the allocation, the requested funds (could be numerous
requests), then the funds received. This should be compiled based on the FY
(fiscail year) entered.
I have tried joins both right, left and inner. I can not make this work.
I join funds and allocations then join (in a seperate query) transactions
but I either get duplicate transactions or some funds not even listed.

Any help is much appreicated.
Thanks
Click to expand...
Click to expand...
Click to expand...
 
I would seriously look at using one table with all transactions. If you can't
or don't, you can UNION quite a few tables together to get a single
recordset.

I'm not sure why you have indents on A, R, and Ts. If you want this, you
would have been better off creating a report based only on the funds
table/query and then using subreports for the other sections.

--
Duane Hookom
Microsoft Access MVP


pdehner said:
Thanks and I started working on this and realized I had a major problem
because I had allocations and requests in the same table. So now I have
three tables.
allocations
requests
transactions

can I create a union query on three tabales? Never have done it before?
then my next concern is how when I create a new type field (with the union
query) for either a, r, or t ... how do I take the results and total them on
a report.??
I need it show
FUNDNumber FUNDNAME
Allocations Date
amount
Requests Date
amount
Transactions (receipts) Date
Amount
then total each before going to the next fund.
any thoughts.
thanks for pointing me in the direction to create another table.
Duane Hookom said:
Just add another column/field in your selects. You should use UNION ALL and
you can remove the "As..." from all but the first SELECT. I would also use
Descr rather than Desc since Desc is the abbrev for Descending.

SELECT "Funds Requested" as SourceTable, [AllocationRequestAmount] AS [AMT],
[AllocationRequestDate] AS [TDate], [AllocationDescription] AS [Descr], [FY]
AS [Fiscal], [AccountID] AS [Account]
FROM [FundsRequested]
UNION ALL
SELECT "Fund Transactions", [TransactionAmount], [TransactionDate],
[TransactionDescription], [FY] , [AccountID]
FROM [FundTransactions];

--
Duane Hookom
Microsoft Access MVP


pdehner said:
I did create a union query for both the transaction table and the fund
allocation table but when I did this I can't tell which is which for the
report???

SELECT [AllocationRequestAmount] AS [AMT], [AllocationRequestDate] AS
[TDate], [AllocationDescription] AS [Desc], [FY] AS [Fiscal], [AccountID] AS
[Account] FROM [FundsRequested]
UNION SELECT [TransactionAmount] AS [AMT], [TransactionDate] AS [TDate],
[TransactionDescription] AS [Desc], [FY] AS [Fiscal], [AccountID] As
[Account] FROM [FundTransactions];

I need to know which is allocation request and which is transaction. Any
Ideas?

:

I think you need to first create a union query of your transactions. Then
join this union query to your funds table.

--
Duane Hookom
Microsoft Access MVP


:

I have 3 tables that I wish to create a report
Table Funds
Fields - AccountID, Fund Number, Name, Department, Fed_State_Other
Table Allocations
Fields - AccountID,
TransactionID,FY,AllocationAMT,TransDate,TranDescr,TranAmt, Notes
Table FundTransactions
Fields - TransactionID, FY, ReceiptNum, TransDate,TranDescr,
TransAmt,AccountID,Notes

I hae the tables designed this way because we receive allocations for a
fund, then we request funds from those allocations then we receive funds not
only from the request but some funds we don't need to request funds.

I need a report that is grouped by Fed_State_Other, then by Department It
needs to list the allocation, the requested funds (could be numerous
requests), then the funds received. This should be compiled based on the FY
(fiscail year) entered.
I have tried joins both right, left and inner. I can not make this work.
I join funds and allocations then join (in a seperate query) transactions
but I either get duplicate transactions or some funds not even listed.

Any help is much appreicated.
Thanks
Click to expand...
Click to expand...
Click to expand...
 
I'm getting lost. too.
I need a report that shows all my funds.
All the allocations of those funds (allocations doesn't mean I have the
money.. just that it is promised)
It also must show all the requests I've made of funds. - Some funds I still
have to requst the funds even though they said I would get them... others
they automatically send)
Also show all funds recevied.
Then I need to show receipt of those funds ... actually received the money.
this needs to be subtotalled by federal_State_other the subtotaled by
department.

So you can see a fund can have no transaction
A fund can have allocations but no requests
A fund can have allocations, requests (numerous) and numerous receipts.

Each fund allocations change each year.

I am confused when you talk about sub reports?? I know what a subform is
not a subreport... Please show me a link to learn about subreports.
Thanks for your help.

The problem is I can have funds with


Duane Hookom said:
I would seriously look at using one table with all transactions. If you can't
or don't, you can UNION quite a few tables together to get a single
recordset.

I'm not sure why you have indents on A, R, and Ts. If you want this, you
would have been better off creating a report based only on the funds
table/query and then using subreports for the other sections.

--
Duane Hookom
Microsoft Access MVP


pdehner said:
Thanks and I started working on this and realized I had a major problem
because I had allocations and requests in the same table. So now I have
three tables.
allocations
requests
transactions

can I create a union query on three tabales? Never have done it before?
then my next concern is how when I create a new type field (with the union
query) for either a, r, or t ... how do I take the results and total them on
a report.??
I need it show
FUNDNumber FUNDNAME
Allocations Date
amount
Requests Date
amount
Transactions (receipts) Date
Amount
then total each before going to the next fund.
any thoughts.
thanks for pointing me in the direction to create another table.
Duane Hookom said:
Just add another column/field in your selects. You should use UNION ALL and
you can remove the "As..." from all but the first SELECT. I would also use
Descr rather than Desc since Desc is the abbrev for Descending.

SELECT "Funds Requested" as SourceTable, [AllocationRequestAmount] AS [AMT],
[AllocationRequestDate] AS [TDate], [AllocationDescription] AS [Descr], [FY]
AS [Fiscal], [AccountID] AS [Account]
FROM [FundsRequested]
UNION ALL
SELECT "Fund Transactions", [TransactionAmount], [TransactionDate],
[TransactionDescription], [FY] , [AccountID]
FROM [FundTransactions];

--
Duane Hookom
Microsoft Access MVP


:

I did create a union query for both the transaction table and the fund
allocation table but when I did this I can't tell which is which for the
report???

SELECT [AllocationRequestAmount] AS [AMT], [AllocationRequestDate] AS
[TDate], [AllocationDescription] AS [Desc], [FY] AS [Fiscal], [AccountID] AS
[Account] FROM [FundsRequested]
UNION SELECT [TransactionAmount] AS [AMT], [TransactionDate] AS [TDate],
[TransactionDescription] AS [Desc], [FY] AS [Fiscal], [AccountID] As
[Account] FROM [FundTransactions];

I need to know which is allocation request and which is transaction. Any
Ideas?

:

I think you need to first create a union query of your transactions. Then
join this union query to your funds table.

--
Duane Hookom
Microsoft Access MVP


:

I have 3 tables that I wish to create a report
Table Funds
Fields - AccountID, Fund Number, Name, Department, Fed_State_Other
Table Allocations
Fields - AccountID,
TransactionID,FY,AllocationAMT,TransDate,TranDescr,TranAmt, Notes
Table FundTransactions
Fields - TransactionID, FY, ReceiptNum, TransDate,TranDescr,
TransAmt,AccountID,Notes

I hae the tables designed this way because we receive allocations for a
fund, then we request funds from those allocations then we receive funds not
only from the request but some funds we don't need to request funds.

I need a report that is grouped by Fed_State_Other, then by Department It
needs to list the allocation, the requested funds (could be numerous
requests), then the funds received. This should be compiled based on the FY
(fiscail year) entered.
I have tried joins both right, left and inner. I can not make this work.
I join funds and allocations then join (in a seperate query) transactions
but I either get duplicate transactions or some funds not even listed.

Any help is much appreicated.
Thanks
Click to expand...
Click to expand...
Click to expand...
 
Back
Top