how to put into form

[JEPEDEWE]

Hi,
After running a querry I have a list of records looking like the jpg file I send.
First column, year, second month, third calculated amount
I would like to get thes figures in a nice form displaying these 3 values
I don't know how... I was told to use a pivot table but....haven't got any succes so far
Anyone an idea??
Thanks
JP, Belgium

 

[John Big Booty]

You could use the Query as the Row Source for a List box to display your results.

 

[JHB]

I would like to get thes figures in a nice form displaying these 3 values

How do you want them displayed?
"Nice form" - hmm is a broad concept.

 

[JEPEDEWE]

Use it as a recordsource.... of course
What I would like is an excel-sort of output/form

column 1 = year
column 2 = month
column 3 = value

2004 2005 2006 2007.....
1 806 922 945
2 702 752 820
3 947 947.....

even better would be to change the month numbers into month names

But,
can not figure out how
I was told to use a pivot table... but... no success :banghead:

Thank you all for your kind help
JP

 

[JHB]

Yes PIVOT is correct, try this: Remember to change the table name and field name to yours.

TRANSFORM Sum(TheYearOgMonth.Tal) AS SumOfTal
SELECT Format(DateValue("01/" & [TheMonth] & "/01"),"mmm") AS [Month], Sum(TheYearOgMonth.Tal) AS [I alt Tal]
FROM TheYearOgMonth
GROUP BY Format(DateValue("01/" & [TheMonth] & "/01"),"mmm"), TheYearOgMonth.TheMonth
ORDER BY TheYearOgMonth.TheMonth
PIVOT TheYearOgMonth.TheYear;

I think you also have to change this because in my contry we use the "day/month/year".

From: Format(DateValue("01/" & [TheMonth] & "/01"),"mmm") To: Format(DateValue([TheMonth] & "/01/01"),"mmm")

 

[JEPEDEWE]

Thanks for your kind reply
But
The initial "recordset" is the result of a querry... not a table..
Should I put the querry in a table and the launch the pivot, or can I bypass the "table-creation"
JP

 

[JHB]

I think it can be done easy.
What happen if you put in the query name instead of the table name?
If not the show the original data, maybe it can be done a Pivot query at them.

 

[JEPEDEWE]

Hi...
Sorry to bother you, but... I can't get it to work

This is how I changed your code:

TRANSFORM Sum(beurten_tellen.CountOfid) AS SumOfTal
SELECT Format(DateValue([Expr2] & "/01/01"),"mmm") AS [Expr2], Sum(beurten_tellen.Tal) AS [I alt Tal]
FROM beurten_tellen
GROUP BY Format(DateValue("01/" & [Expr2] & "/01"),"mmm"), beurten_tellen.Expr2
ORDER BY beurten_tellen.Expr2
PIVOT beurten_tellen.Expr1;

beurten_tellen is the name of the query
Year is Expr1
Month Expr2
Calculation is CountOfid

I get an errormessage:

Circular reference caused by alias 'Expr2' in query definition's SELECT list

Hope it is clearer for you then it is for me!

Thank
JP Belgum

 

[JHB]

Yes I know what it is: You are calling a output field the same name from where you are getting the value, field is [Expr2], this is called "Circular reference"

SELECT Format(DateValue([Expr2] & "/01/01"),"mmm") AS [Expr2]
correct it to:
SELECT Format(DateValue([Expr2] & "/01/01"),"mmm") AS [Expr3]

it is also wrong here, you have change it in one place but not in the other
GROUP BY Format(DateValue("01/" & [Expr2] & "/01"),"mmm"), beurten_tellen.Expr2
correct it to:
GROUP BY Format(DateValue([Expr2] & "01/01"),"mmm"), beurten_tellen.Expr2

 

[JEPEDEWE]

Problems again...

this is what I wrote:

TRANSFORM Sum(beurten_tellen.CountOfid) AS SumOfTal
SELECT Format(DateValue([Expr2] & "/01/01"),"mmm") AS Expr3, Sum(beurten_tellen.Tal) AS [I alt Tal]
FROM beurten_tellen
GROUP BY Format(DateValue([Expr2] & "/01/01"),"mmm"), beurten_tellen.Expr2
ORDER BY beurten_tellen.Expr2
PIVOT beurten_tellen.Expr1;

first I got an errormessage for:
beurten_tellen.Tal
where Tal isn't known

I changed it to Expr1....
got
"Data type mismatch in criteria expression"

Also... what is [I alt Tal]???
Must be Danisch???

 

[JHB]

The same again, you have repalced it in one place but not in the other!
Sum(beurten_tellen.Tal) AS [I alt Tal]
Correct is.
Sum(beurten_tellen.CountOfid) AS [I alt Tal]
[I alt Tal] is "Total"

 

[JEPEDEWE]

OK, changed it but "data type mismatch" error remains

I alt Taly confused

 

[JHB]

Could it be because month is a number by you?? Try CStr([Expr2]) (2 places)

 

[JEPEDEWE]

Almost perfect

What I get now is what I send in a JPG file
The month remains "jan"... doens't change

Also
When I run the pivot I see columns and rows like I send you, but, how to place this on a form
I created one and put the pivot in the recordsource... alas no way to "build" a nice form displaying the data

Any idea?

Thank you very much
JP

 

[JEPEDEWE]

Just wondering
Now you make a total per month.. setting this value at the beginning of the table. Can you also make a total per year and place this value at the bottomline?
Many thanks
(Denmark... I heard it's a very nice country....true??)

 

[JHB]

The month remains "jan"... doens't change

I think Belgium use the same date format as Denmark, (day-month-year).
So DateValue([Expr2] & "/01/01") must be - DateValue("01/" & [Expr2] & "/01")

When I run the pivot I see columns and rows like I send you, but, how to place this on a form
I created one and put the pivot in the recordsource... alas no way to "build" a nice form displaying the data

Oh yes it is - do you not have a "Form" guide which automatic creates forms for you? Choose the (Pivot) Query as recordsource.

Now you make a total per month.. setting this value at the beginning of the table. Can you also make a total per year and place this value at the bottomline?

Yes you can, but only in a form or report, not in the query. Place a text control in the footer and sum the different columns.

(Denmark... I heard it's a very nice country....true??)

Of cause it is, and not so far away from Belgium!

 

[JEPEDEWE]

True... not so far from Belgium
Been here before???

I did what you suggested
Saved the SQL in a query
Created a form with the query as rowsource

now I have data from 2004 until now
I see in fields named 2004....
What will happen when I, for example, delete data from 2004
Then the from will be looking for those dates, will not find them and return an error (I think)

Anyway... putting the data in a form returns some errors and a strange look

could I send you the data so you can take a look??
If not I understand
My emial: jp.dewinter@telenet.be... just let me know

 

[JHB]

Yes I've been in Belguim a few times, for some years ago, my wife and I, drove along the West coast, (the furthest west we were able to find roads), from Denmark to France, it took nearly 2 weeks.

I've send you my E-mail add.

What will happen when I, for example, delete data from 2004
Then the from will be looking for those dates, will not find them and return an error (I think)

Correct.

Therefore I always prefere to have the month as columns.
Else you can design the form in the "fly" with VBA-code, but then it is necessary to have some experiences as programmer, else I believe it is to hard to do it.