Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Search results for query: *

  1. straatlang

    compare ?

    Hi, I hope someone can help me out? I have two columns A 1 2 3 4 5 6 10 and B 1 2 4 5 6 I would like to compare the two columns. The result I would like to see is 3,10
  2. straatlang

    Select from where

    Thank you very much it works perfectly
  3. straatlang

    Select from where

    Hi, Maybe someone can help me out with the following. I have to tables I need to know the missing numbers. Table A NRA 1 1 2 2 2 3 3 4 4 5 5 7 7 7 7 Table B NRB 2 2 2 3 3 3 3 5 I would like to see which number is not present in B the result should be shown ones so the result should...
  4. straatlang

    select from securities

    I change ID to ID_name and this what I copy and paste from Access with some small adjustments like b.nr but it does not give me the expected result I also removed '!' from the query so this is what I have now. SELECT b.NR, b.ID, b.Quantity, b.SEC, b.Name_ID FROM bookings AS b, [names] AS n...
  5. straatlang

    select from securities

    I hope someone can help me with the next problem. I have a table bookings NR ID Quantity SECNAME ID 1001 1 1000 abc 5 1002 2 2000 def 6 1003 3 3000 ghj 7 1004 4 4000 klm 8 and a table names ID Type country 1 Client US 2...
  6. straatlang

    Select from where max(date) valuation in EUR

    Thank you very much it works SELECT M.Account, Abs(Sum(Amount*IIf(DRCR='CR',1,-1))) AS Balance, M.Cur, IIf(Sum(Amount*IIf(DRCR='CR',1,-1))>0,'CR','DR') AS FLAG, SelectedDate, R.Erate, N.Name, Erate*Balance AS V_EURO FROM (Movements AS M INNER JOIN [Names] AS N ON M.Account=N.Account) INNER JOIN...
  7. straatlang

    Select from where max(date) valuation in EUR

    Hi Rudy, Do you mean that I have to remove one prompt or that the two prompts should be the same. I have tried this: SELECT M.Account, Abs(Sum(Amount*IIf(DRCR='CR',1,-1))) AS Balance, M.Cur, IIf(Sum(Amount*IIf(DRCR='CR',1,-1))>0,'CR','DR') AS FLAG, [Enter Value Date] AS SelectedDate, R.Erate...
  8. straatlang

    Select from where max(date) valuation in EUR

    Update I have changed the query into: SELECT M.Account, Abs(Sum(Amount*IIf(DRCR='CR',1,-1))) AS Balance, M.Cur, IIf(Sum(Amount*IIf(DRCR='CR',1,-1))>0,'CR','DR') AS FLAG, [Enter Value Date] AS SelectedDate, R.Erate, N.Name, Erate*Balance AS V_EURO FROM (Movements AS M INNER JOIN [Names] AS N...
  9. straatlang

    Select from where max(date) valuation in EUR

    I hope somebody can help me with the next problem. There are three tables involved: Table: movements. Value Date Account Amount Cur DRCR 1-1-2003 123 2000 EUR CR 1-2-2003 456 1500 EUR CR 1-2-2003 123 1450 USD CR 1-4-2003 456 1250 EUR CR 1-7-2003...
  10. straatlang

    Select max(date) ???

    Sorry here are all the details which I get when entering the Value date as 31-01-2003. So now Account 123 his rate for EUR/USD is 0.98 456 gets 0.90 and 789 get 0.95. Account Balance Cur FLAG SelectedDate Erate Name V_EURO 123 7050 EUR DR 1 ABC 7050 123 2250 USD CR 0.98 ABC 2205 456 2750...
  11. straatlang

    Select max(date) ???

    Almost correct. If I enter 31-01-2003 as the valuedate the result is: Cur FLAG SelectedDate Erate Name V_EURO EUR DR 1 ABC 7050 USD CR 0.98 ABC 2205 EUR CR 1 DEF 2750 USD CR 0.9 DEF 7200 USD CR 0.95 GHJ 4750 The rate table is: ID CurID Erate RateDate 1 EUR 1 1-1-2003 2 USD 0.98...
  12. straatlang

    Select max(date) ???

    I hope somebody can help me with the next problem. Table: movements. Value Date Account Amount Cur DRCR 1-1-2003 123 2000 EUR CR 1-2-2003 456 1500 EUR CR 1-2-2003 123 1450 USD CR 1-4-2003 456 1250 EUR CR 1-7-2003 123 800 USD CR...
  13. straatlang

    Select from an other table and ........

    I have tried your suggestion and it worked SELECT Movements.Account, Abs(Sum(Amount*IIf(DRCR='CR',1,-1))) AS Balance, Movements.Cur, IIf(Sum(Amount*IIf(DRCR='CR',1,-1))>0,'CR','DR') AS FLAG, [Enter Value Date] AS SelectedDate, Rates.Erate, Names.Name, [Erate]*[Balance] AS V_EURO FROM (Movements...
  14. straatlang

    Select from an other table and ........

    Dear Trendsetter, I will send you more information. Thanks Regards
  15. straatlang

    Select from an other table and ........

    Hi, I want to extend the query and join some tables to be able to have a report with the name of each account and the exchange rate of the currency. And finally I want to see the total value in EUR in a seperate column. This is table movements. Value Date Account Amount Cur DRCR 1-1-2003 123...
  16. straatlang

    Select From ? Account Balance

    You are great just one small adjustment. SELECT Account, abs(sum(Amount*IIf(DRCR='CR',1,-1))) AS Balance, IIf(sum(Amount*IIf(DRCR='CR',1,-1))>0,'CR','DR') AS FLAG, [Enter Cur] AS Cur, [Enter Value Date] AS SelectedDate FROM Movements WHERE [Value Date]<=DateValue([Enter Value Date]) And...
  17. straatlang

    Select From ? Account Balance

    This works but what if I also need to see the DR or CR flag and the value date and Currency in the report Account Balance CUR DRCR Per Valudate 123 1950 EUR CR 31-01-2003 456 2750 EUR CR 31-01-2003 Any suggestions.
  18. straatlang

    Select From ? Account Balance

    Thanks you very much rudy. SELECT A.Account, A.Cur, Sum(Amount*IIf(DRCR='CR',1,-1)) AS Balance FROM Movements AS A WHERE A.[Value Date]<=DateValue([Enter Value Date]) GROUP BY A.Account, A.Cur; But what if I want to see the balance in XXX currency per <= Valuedate Per Account. Then I want...
  19. straatlang

    Select From ? Account Balance

    Sorry there is still one other problem with this Query The result is that there are now two balance for account 123 one DR and one CR balance This the result. Account Balance Cur DRCR 123 2950 EUR CR 123 1000 EUR DT 123 2250 USD CR 456 2750 EUR CR 456 8000 USD CR 789 5000 USD CR the balance...
  20. straatlang

    Select From ? Account Balance

    Dear Rudy, You were correct I needed <=. Do you also have an Idea when I want to see the total balance in EUR on a particular Valuedate. I want to enter the currency and value date The result should be: Balance in XXX on Valuedate Account Balance 123 XXXXX 456 XXXXX 789...

Part and Inventory Search

Back
Top