Month columns in query

[pablavo]

Hi folks.

I've been looking for an easy way to have 12 columns in my query which are each of the 12 months and are all using the same date field "dateSent". So i'd want to just use the month part. within the actual columns there will be a disbursal field which will tell a user how much money has been sent (if any) on that month. then If done correclty, it could be analysed in Excel.

I've attached an example of what is required of me. I'm expected to have the same format but i'm unsure how I'm going to go about this.

I've attached an example. Maybe someone might know a good way to do this and perhaps point me in the right direction

I appreciate all help

Thanks.

 

[neileg]

What you propose is bad design. Access is not a spreadsheet. You should not have 12 columns, you should have up to 12 records in a related table. If this is coursework, then either your teacher needs shooting, or what you have been given is intended to be shown in a report and is not the intended table structure.

 

[Jurjen]

extract the month from a date

I did not understand your whole story but the DatePart function might help you out:
DatePart(<interval>, <date>)

Interval can be any of:
yyyy (Year)
q (Quarter)
m (Month)
y (Day of year)
d (Day)
w (Weekday)
ww (Week)
h (Hour)
n (Minute)
s (Second)

 

[pablavo]

Thanks folks for your help.

neileg, No, this is not course work, I'm simply looking for a way to do this query so that I can use it as the record source of a report. Perhaps what I propose is "bad design". however, I'm looking for ways to produce the report so that it can be analysed within Excel. Maybe as a pivot table would be better.

One more thing, I didn't get you when you said there should be up to 12 records within a related table. what does that mean?

thanks

 

[neileg]

As I understand this, you have a series of transactions that have a project ID, an amount, a date and some other data (perhaps). You should hold this in a table that reflects those items as fields. You can then summarise this data in an aggregate query, by month, and to display it maybe use a cross tab query.

Having said that, I do some reporting that is similar and I use an Excel pivot table.

 

[pablavo]

thanks Neil for getting back. I've been thinking of using a Pivot table within Excel. Probably better that way than to have a pivot in Access. I'm going to set up a cross tab query to see if that will work.

cheers