Linking sales/costs in time

[Guest]

Is there a way to link income (in 1 table) and cost (in another table) so a
report can show them both but on the same timescale.

Like

Jan 2006

Sales 10000
costs 5000

Profit/loss

Feb 2006 etc

I have tried summerising each table in seperate queries but either it
reffueses to link the 2 queries or when it does has multiple dates (ie 2 sets
of jan 2006 ) or the values go crazy and end up in the billions.

 

[Guest]

Hi Clive -- Is there currently any link between the two tables? I recently
worked on similar problem for db for balancing a checkbook. The problem
involved two tables, 1 for deposits and 1 for expenses. To get the balance at
a point in time, I used 3 queries: (1) Sum of deposits for given time, (2)
Sum of expenses for given time, and (3) Balance, which is equal to the
difference between the first two queries.

Without knowing more of your table structure and relationships, it sounds as
if the above design may solve your problem. Try it and group on the month in
the report.

 

[Guest]

Thanks mate thats worked a treat. I have them summed by month in the queries
which gives th right totals but on the report but its doing it like this :

April 2004
April 2005
April 2006
Feb 2004
Alphabetically rather than by date

Any ideas

 

[Guest]

Is this a text field? If so, the sort is working correctly. The only way I
know around this is to convert the text field to a date/time field and start
capturing the actual date. Then, in the query you can use the format
function to format the date in the format you want and the sort will work
correctly.

 

[Guest]

That makes sense. In the original tables they were date/time fields, but its
when i grouped them in the queries that it changed to the format of " January
2006". Just having problems working out how to convert the feild to
date/time in the queries.

 

[Guest]

here is the expression from the querry

Year(Expenditure.[Date Paid])*12+DatePart('m',Expenditure.[Date Paid])-1

which bit is converting it to text ??