I am trying to build a pos and inventory system in vb 6.0 with MS-Access database. I do not have much experties in vb 6. Ok here is the question now,
I want to build a report like income statement. I have two tables with name tblsales and tblexpense, from tblsales I wish to take two fields named Salesdate and profit, from tblexpense I wish to take Date, Type and amount fields. Putting them together I like to make a report which will show the total profit and total expense of a certain time period from which I can calculate the net profit. Sample will be like:

`"Sales Date"              "Profit"
  Date                   Sum of profit of that day
  Date                   Sum of profit of that day
  Date                   Sum of profit of that day
  Date                   Sum of profit of that day
total no. of day         total sum of profit

"Expense date"     "Type of Expenses"             "Amount"
  Date             Type of Expense          Sum of expense of that day
  Date             Type of Expense          Sum of expense of that day
  Date             Type of Expense          Sum of expense of that day
  Date             Type of Expense          Sum of expense of that day
 total no. of day                              total sum of Expense

Total sum of Profit      Amount
Total sum of Expense     Amount
Net Profit               Amount

well this is the report will be printed. It could be in datareport or in crystal report. Better If make it in datareport.

I have asked many question in this forum and got very good answers quickly. Hope to get an quick and better answer for this problem very soon.


Recommended Answers

All 9 Replies

So, is there an actual questions here? Some specific thing you're stuck on? Or are you just asking someone to build a report for you? If that's what you're expecting, I think you will be terribly disappointed.

Thanks for the reply. I used dataenvironment and datareport to produce stock report and sales report for the program. I just stuck on this report. I just don't know how to make the report in this way. I mean I can make a query in MS-Access from those two table and show the report from that query, that is a alternative solution. But that type of report doesn't give me the detail picture as I wanted. I tried to make a Command under dataenvironment with the two table then take only those fields from the two table and try to group them in my desired order, but the problem is, although there is One record in the tblexpense under type field, the report shows repetedly the same data as many times the Sales date of the tblsales have. Do I make it clear ? I am not expecting to anyone to make the report for me, I just want to know in where I got stuck ? What should be the way ? thats all
Thanks again


Good to know...always happy to help those who try hard first.

Okay, so here's the deal. When you're building something like what you're trying, it's not really one report...it's TWO reports that print on the same page. So what winds up happening when you try to combine them is a mess.

One technique I've used in the past is to create each report separately, then use them as sub-reports tied to THIRD report that has either a common key or just a "placeholder" where you can aggregate totals. If necessary, you can get the two subreports to tie out up to the parent report, and that will resolve getting any necessary keys to match up. In your example I didn't see that there was a need for matching of keys, but it would be useful if you were breaking the report up into, for instance accounting months.

I hope that makes sense to you, and that I've explained it with enough clarity. Good luck!

Thanks again BitBit. I have made the two different report in datareport with the required fields, but how can I tied those two report in to a third report ? I have no idea about it. Is it possible in datreport or did u use the technique through crystal report ?

Thanks again

I've used this technique in both Access reports and Crystal. It's much easier in Access, and once it's built you can instantiate an MSAccess object to get to the report characteristics to run it. If you want to do it in a DataReport, the technique is kind of different. There, you have to make use of relation parent/child in the DataEnvironment to get the various pieces of data to relate, then use group headers/footers with no detail to get the right groupings to display what you need. It's tricky, I'll have to search my archives to see if I can find it.

Thanks again for quick reply. As I did not make any other report is Crystal, If its possible in MSAccess I don't want to run after crystal for only one report. I will try it through MSAccess and wait for your response if you can find anything in your archive.
Thanks a lot.

well it seems notbody is interested in answering this question anymore. So what should I do? I can't mark this question as solved as it is not. so then ????

You can try this code this will copy the text of an textbox to the data report

DataReport1.Sections("Section4").Controls("Text1").Text = Form1.Text1.text

thank for the reply. but I do not have the data in any text box, I m tring to extract it from the database table.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, learning, and sharing knowledge.