Access Query Question

[juanmatos]

Good afternoon everyone.



I'm sooo stuck here at my new job and I hope somone here can help me. I have a report to do in Crystal off of an Access 2010 database and the requirement seems impossible. I need to be able to prompt for a quarter and return the output. We don't store quarters such as 1,2,3,4. Instead we store periods which are really months. So we store 1,2,3....12. I've tried just about every possible angle in CR and I just simply need to try a different approach. What I want to try now is a query that somehow brings back just the periods I need. Is this possible? Suppose I have a table named invhdr and it has fields InvNum, billname, invPer, InvYr. InvPer is the periods column. I would have to join this table to the invDetail table on invNum. Any chance someone can help me with this?

[recyan]

Post some dummy data of the 2 tables involved & some one should be able to help.
Meanwhile, till some one comes along,
Check out if you can use something like IIf , in the source query, something like
If (InvPer = 1 or 2 or 3) then
Quarter = Q1
else if (InvPer = 4 or 5 or 6) then
Quarter = Q2
etc

Thanks

[June7]

Sounds like you need to calculate the quarter based on the period (month).

Create field in query with expression:
InvQtr: Choose([InvPer]<=3,1, [InvPer]<=6,2, [InvPer]<=9,3, [InvPer]<=12,4)

Apply quarter filter criteria to that constructed field.