Complicated Query-->HELP

[wannabepro]

I have a query that calculates the 'amount of credit' depending on the draw down date (which is when they started circulating the loan docs b/w two parties). Anyways,, the question is how can I make two columns of 'amount of credit' instead of one. Because one of the team member is dealing with overseas loan, while others are working on domestic. I want to make a column that would seperate his works from other people's work. And, I am kind of confused, how will I do it on a report. I have tried cross-tab query and also simple. Nothing is working.

One column should carry the amount he has to give as a loan and the other should calculate the amount from domestic side.

I don't want to use list box as it is unprofessional and also, its looking bad. If I create two queries, how can I join them together. I tried that too, but didn't work.

I need help badly

TIA

 

[dan-cat]

Can you give us an example of your sql syntax to give us a starting point?

 

[wannabepro]

Select *
From tblloan
Where [TeamPeople]<>"AP";

It gives me all the values without 'AP' which is initial of a user. But I want his values to be in second column.

Select*
From tblloan
where [TeamPeople]="AP";

I know its a very simpl query, but I am getting confused. I can't join them to make one query. Its like my brain has stop responding, I can't think of anyway I could possibily achieve this goal.

TRANSFORM Avg(tblloan.AmountofCredit) AS AvgOfAmountofCredit
SELECT tblloan.ProspectName, tblloan.DDDate, Avg(tblloan.AmountofCredit) AS [Total Of AmountofCredit]
FROM tblloan
WHERE (([TeamPeople]<>"AG"))
GROUP BY tblloan.ProspectName, tblloan.DDDate
PIVOT tblloan.TeamPeople;

This is what I did through wizard to see if cross tab will work in my situation. But unfortunately it didn't.

 

[wannabepro]

No Help

 

[mresann]

Can you show what your result should look like? You are trying to do a non-aggregate query that cannot display different name values in columns, so a crosstab won't work. However, I don't know what you are trying to show, so just show what you want the RESULT to look like, and the gurus here should be more forthcoming.

 

[wannabepro]

Here is the demo of the application I am trying to create in ms access. I don't know, how I will achieve this goal. As it looks quiet easy, but getting me stuck. If you look at amount column, it has two columns spliting to show diff values. Cross Border values are coming from "AG"'s account. I am trying to develope the exact samething.

TIA

 

[mresann]

OK, see the picture. Cross tab won't work in this case. Unless somebody pops an answer to you, I can take a gander at it a little later today, perhaps 6 hours.

 

[wannabepro]

Thanks mresann, I would really appreciate some help. I know Cross tab didn't work, nothing is working.

 

[ejstefl]

Have you tried a Union query? You can't make it in the QBE grid, but if you search help or google you should find some info...

 

[wannabepro]

ejstefl, I don't think union query will work. I tried it, but it wont. thanx for the input.

 

[wannabepro]

So what else should I try???

 

[ejstefl]

Is there a reason this needs to be separate in the query? On the report, can you simply have two columns, with the datasource saying something like:

=IIF([TeamPeople] = "AP", [AmountOfCredit],Null) for the Domestic column
=IIF([TeamPeople] <> "AP", [AmountOfCredit],Null) for the International column?

 

[wannabepro]

ejstefl, thanks a lot.