-1

I searched forum for 1h and didn't find nothing similar.

I have this problem: I want to compare two colums ID and DATE if they are the same in both tables i want to put number from table 2 next to it. But if it is not the same i want to fill yearly quota on the date. I am working in Access.

table1

id|date|state_on_date 
1|30.12.2013|23 
1|31.12.2013|25 
1|1.1.2014|35
1|2.1.2014|12 
2|30.12.2013|34 
2|31.12.2013|65 
2|1.1.2014|43

table2 

id|date|year_quantity 
1|31.12.2013|100 
1|31.12.2014|150 
2|31.12.2013|200 
2|31.12.2014|300

I want to get:

table 3

id|date|state_on_date|year_quantity
1|30.12.2013|23|100
1|31.12.2013|25|100 
1|1.1.2014|35|150 
1|2.1.2014|12|150 
2|30.12.2013|34|200 
2|31.12.2013|65|200 
2|1.1.2014|43|300

I tried joins and reading forums but didn't find solution.

Zane
  • 4,129
  • 1
  • 21
  • 35
Chaler
  • 13
  • 3

1 Answers1

0

Are you looking for this?

SELECT id, date, state_on_date,
(
  SELECT TOP 1 year_quantity
    FROM table2
   WHERE id = t.id
     AND date >= t.date
   ORDER BY date
) AS year_quantity
  FROM table1 t

Output:

| ID |       DATE | STATE_ON_DATE | YEAR_QUANTITY |
|----|------------|---------------|---------------|
|  1 | 2013-12-30 |            23 |           100 |
|  1 | 2013-12-31 |            25 |           100 |
|  1 | 2014-01-01 |            35 |           150 |
|  1 | 2014-01-02 |            12 |           150 |
|  2 | 2013-12-30 |            34 |           200 |
|  2 | 2013-12-31 |            65 |           200 |
|  2 | 2014-01-01 |            43 |           300 |

Here is SQLFiddle demo It's for SQL Server but should work just fine in MS Accesss.

peterm
  • 91,357
  • 15
  • 148
  • 157
  • It's `AS` before the alias that gives the error. MS Access doesn't like column aliases without `AS` unlike MySQL or SQL Server. It should be `AS year_quantity`. See updated answer. It has been tested and working just fine. – peterm Jan 08 '14 at 04:14
  • It does it works great. Thank you very,very much. I have additional question to this table can i ask here ore i have to add another one? I would like to add 5th row to track changes in 3rd row. In 5th row i would like to have from top to bottom DIFFERENCE|23|-2(23-25)|-10(25-35)|23(35-12)|34|34-65|65-43 it is all based on ID and date. Tahnks for all your help i realy appriciated. – Chaler Jan 08 '14 at 20:52