Query Problem, Show only records from previous Qtr

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
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"
 
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
 
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
 
Both of these solutions sound a bit too complicated for me, can you please
rephrase it in an easier way to follow?
 
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...
 
guess, ill trust my gut feeling next time before letting and "expert" confuse
me with their non sense
 
Back
Top