Query Problem, Show only records from previous Qtr

J

Juan

hi,

Thank you in advance, I have a query that is composed of two tables, one
table contains basic information records for all the companies we do
accounting for (name, tel, fax, address). The second table contains a year
column, and a column for each month of the year. Both tables are linked in
this query using a client id number. Now what i want is for this query to
only show me the month columns that correspond to the previous qtr. Ex: if
we are in the second qtr of the year, i want it to show me only the records
for Jan, feb, and march. Is this possible? or am going at it the wrong way?

thanks
 
A

Amy Blankenship

Could you go into more detail about why you have a column for each month and
year instead of a record with clientID & date? I suspect what you want
would be easier if you structured it the latter way.

-Amy
 
O

OfficeDev18 via AccessMonster.com

Juan said:
hi,

Thank you in advance, I have a query that is composed of two tables, one
table contains basic information records for all the companies we do
accounting for (name, tel, fax, address). The second table contains a year
column, and a column for each month of the year. Both tables are linked in
this query using a client id number. Now what i want is for this query to
only show me the month columns that correspond to the previous qtr. Ex: if
we are in the second qtr of the year, i want it to show me only the records
for Jan, feb, and march. Is this possible? or am going at it the wrong way?

thanks
Click to expand...

Here's a two-step approach:

LastQtrStrt = Month(Date()) - 5 + (Month(Date()) mod 3) 'This can result
between -3 and 7
If LastQtrStrt < 0 Then LastQtrStrt = 10 'Test for negative number, which
is illegal and fix the negative number with the only possible alternative

LastQtrEnd = LastQtrStrt + 2
DoCmd.OpenReport "YourReportName", , ,"Month(DateField) Between LastQtrStrt
And LastQtrEnd"
 
J

Juan

Well I need a column for each month, because im trying to keep track of the
monthly deposits that each company makes, but i understand what u r saying
about the structure of my query, so if you could help me structure it the
right way.

thanks
 
A

Amy Blankenship

You can have the information available for a query, but really the table
structure should have one row per transaction.

So

Deposits:

DepositID (Autonumber)
ClientID
DepositDate
DepositAmount

Then,

Select DepositDate, DepositAmount
FROM Deposits
WHERE ClientID=[Enter a Client ID] AND DepositDate > DateAdd("q", -1, Date)

For more information on refining the results of the query so you see each
month in a column:
http://www.datapigtechnologies.com/flashfiles/crosstab.html

HTH;

Amy
 
J

Juan

Both of these solutions sound a bit too complicated for me, can you please
rephrase it in an easier way to follow?
 
A

Amy Blankenship

Hire a consultant to do it for you?

Juan said:
Both of these solutions sound a bit too complicated for me, can you please
rephrase it in an easier way to follow?
Click to expand...
 
J

Juan

guess, ill trust my gut feeling next time before letting and "expert" confuse
me with their non sense
 
Top