Cross tab query by week

[Mabid]

Hello, I want to create a cross tab query. Based on column customer, collection $, transaction date.
How I would set up cross tab to show collection data by week. Week starts Thursday. And end Friday

Cross tab should always show columns header for all weeks fall in a month

Thanks in advance

[June7]

Format() function can calculate a week number for the month or the year.

Format(Date(), "w", vbThursday)

Format(Date(), "ww", vbThursday)

So for a yr-mo-wk value: Format(Date(), "yyyy-mm-w", vbThursday)

Use PARAMETERS to restrict the output to a single year or year-month.

[Mabid]

I get a message, access2007 does not recognize vb Thursday,

[June7]

Right, I was testing in VBA immediate window. Use 5 in query instead.

[Mabid]

Trandate: Format(Date(),"yyyy-mm-w",5)

is this right? result comes only for Oct 4

[Mabid]

Customer	Total Of Collection	2017-10-4
aaa	25	25
ABC	100	100
efg	250	250
fff	150	150
ggf	150	150
ggg	50	50

[June7]

I was only giving an example. Use your date field in place of Date().

Trandate: Format([transaction date], "yyyy-mm-w", 5)

BTW, advise no spaces nor punctuation/special characters (underscore only exception) in naming convention. Better would be TransDate or Trans_Date or TxnDate, Collection_Dollars or CollDollars, etc.

[Mabid]

Very much grateful for your help,

I get following result, may be I am doing some step error, Because the cross tab result is by date ,not by week.

So Much for your help

Customer	10/3/2017	10/12/2017	10/17/2017	10/18/2017	10/21/2017
aaa					25
ABC	100
efg	250
fff		150
ggf			150
ggg				50

[June7]

Then you are not using the calculated field as the ColumnHeader in the CROSSTAB.

Start with a normal SELECT to pull in the fields and create the calculated field. Then switch to CROSSTAB and make the CROSSTAB assignments.

I seldom use the query wizards.

[Mabid]

I used following and this worked.I really appreciate your help to take me there.

Regards

TRANSFORM Sum(Table1.Collection) AS SumOfCollection
SELECT Table1.Customer
FROM Table1
GROUP BY Table1.Customer, Table1.Trandate
PIVOT [TranDate]-DatePart("w",[TranDate])+2;