 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Reports / Printing / January 2007report between 2 table sets
I am still stuck with this so, I will explain it in more detail: I have 4 tables. 2 are for expenses 2 are for income: Tables: 1) Invoices 2) Invoice Details ===> One-to-many relation 3) Expenses 4) ExpenseDetails ===> One-to-many relation There is no relationship between Invoices and Expenses tables because they are irrelevant. Income from invoices tables can be summarized in a query and the same for expenses so, I can have 2 queries, one for expenses and 1 for income. Requirement: Design a report that will include all expenses and all income, add the total of expenses and total of income, and then subtract the expenses total from the income total in order to find profit or loss. What I have tried so far is to use subform as previously suggested but that will just show another report that is totally separeted from the other. I haven't managed to find a way to create a query between the 4 tables as this would mix expenses with income. Also, I don't see any reason why to make a relationship between expenses and income as they have no relationship. What is the proper way to do this? Use a UNION query to combine your 2 queries. Then use the result as the recordsource of your report. SELECT "Invoice" as Type, InvoiceID As ID, ItemDescription As Description, ItemAmount As Amount FROM qryInvoices UNION SELECT "Expense" as Type, ExpenseID, ExpenseDescription, ExpenseAmount FROM qryExpenses Group By Type UNION queries 1) when using the query designer, you have to use View>SQL & enter the text "long-hand". 2) use Alias/FieldNames from the 1st Select in its Output 3) require that you have the same # of fields in the tables you are combining (add Placeholder fields if you have 4 fields you want to show overall, but one table only has 3 fields: "Null as SomeField") HTH, >I am still stuck with this so, I will explain it in more detail: > I have 4 tables. 2 are for expenses 2 are for income: [quoted text clipped - 26 lines] > > What is the proper way to do this? Ok, and in this way I get a unified recordset where the expenses will have "Expense" type and income will have "Invoice" type. Then, at the report how can I separate the 2 since they both have the same descriptor/field name? I can imagine that my calculations can be made based on a condition so I could sum up only those records with "Income" but will I be able to place separate them within the report as expenses and as income ? How? > Use a UNION query to combine your 2 queries. Then use the result as the > recordsource of your report. [quoted text clipped - 45 lines] >> >> What is the proper way to do this? In your report, Group on "Type". Include a Footer for that group & add subtotals on "Amount". That will give you separate totals for Invoices & Expenses. In your query, change ExpenseAmount to ExpenseAmount* -1 as ExpenseAmount. That will show all Income as positive, Expenses as negative & a grand/report total of the "Amount" field will give you the combined "difference". Alternatively, on your report you could split "Amount" into 2 side-by-side columns with the following as ControlSources: =iif([Type] = "Invoice",[Amount],0) and =iif([Type] = "Expense",[Amount],0) Wrap the entire expressions above in Sum() to get any Subtotals or Report totals for the individual columns. Note: this might slow down the report considerably if you have a lot of data. HTH, > Ok, and in this way I get a unified recordset where the expenses will have > "Expense" type and income will have "Invoice" type. Then, at the report [quoted text clipped - 55 lines] >>> >>> What is the proper way to do this? Is it possible to have different header for expenses and different for invoicesor should I set the caption name based on condition (IIF)? > In your report, Group on "Type". Include a Footer for that group & add > subtotals on "Amount". That will give you separate totals for Invoices & [quoted text clipped - 73 lines] >>>> >>>> What is the proper way to do this? |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.