Full Version: Selection From Query Records
UtterAccess Forums > MicrosoftŪ Access > Access Forms
DaveT
Hi All
I have created a query based on related tables that are linked externally. I want to be able to present the query data (I thought on a form) and use a check box to select records from it, and then create a file from the selected records. Would somebody please advise how I could do this.
Many thanks. David.
GroverParkGeorge
Hi. Multiple steps, lots of possible ways to go about it. None of them particularly difficult in themselves, but describing them all in sufficient detail might take some effort.
irst up. "Presenting the query data". Yes, definitely in a form. Not many other options there. However, the devil is in the details. You could present the records in a list box on a form. You could make the form a continuous view or datasheet view and present the records that way. Depending on WHY you need to select records from it, you might lean to one method over another. Also a consideration is whether you have to use the checkbox or whether a different selection method, such as clicking items in the list box, works for you. Also, do you need to EDIT the records before doing something else with them?
Next up, and the real stumper here, is what you mean by "create a file from the selected records". What is the goal here? What will that file look like? Where will it be located? What kind of file do you need?
DaveT
Hi George, & thanks.
want to present the records to a particular user to authorise (by checking) individual records for payment.
Ideally, the checked records could be used to create a report or file as confirmation of those records as authorised for payment. My dream would be to create a file that I could import into MYOB (the external data source) but for now, a basic report would suffice.
Cheers.
GroverParkGeorge
That is all quite doable, with the possible exception of the data into a different application (MYOB), which may well be possible as well. That depends on what sort of input files MYOB requires.
lso, I missed the significance of the reference to "related tables that are linked externally". Are you saying that the tables in this query are actually located in MYOB? If so, why not just handle this process from within MYOB?
DaveT
Thanks George
My idea was not to have to train the management in using myob but to create a 'package' of management reports and controls that I create from its data, that they can 'access'? Am I being too ambitious with my limited knowledge? Also, I really need a good reference to how to use the expression builder and writing formulas?
Thanks. David.
GroverParkGeorge
Then you are confirming that the answer to my previous question, "Are you saying that the tables in this query are actually located in MYOB?", is "Yes"? Is that correct? The tables are located in MYOB and you can link to them from Access?
I am afraid that what you describe is not all that simple to implement, but it is certainly doable.
If, as I now assume, you are able to link to tables in MYOB (what does that stand for, by the way? I think it is an accounting package, isn't it?) then you can create any query you need to select, filter and sort data from those tables and present it in forms or reports in Access. It's just a matter of including the right tables from your source package and selecting the right fields from them for your queries. Which tables you link into Access, and how you use them, of course, depends on your specific requirements.
Also, you mention both reports and "controls". What would those controls be? Reports can be highly standardized, so that non-technical users can be trained to select criteria from options you make available, so that should be somewhat straightforward. However, the rest of the requirement needs clarification before we could offer suggestions.
The Access Help system itself does a pretty good job of walking you through expressions you can use in the Expression Builder. Also, just searching here should be a good way to find discussions of various expressions you might want to use. I don't know of any reference books dedicated to the Expression Builder, though. Many good Access books do include some discussion of using it. I just don't know that it's a large enough topic by itself to have been presented in a dedicated book.
Finally, what is the context in which you want to learn about "writing formulas"? Are you talking about using accounting formulas in reports?
Finally, we have not yet addressed this point. "My dream would be to create a file that I could import into MYOB (the external data source)"
Access can create files in many formats, including .csv and .txt files. My guess would be that your software, MYOB, could import either of those two formats.
Again, the way you create such files and the specific contents of them depends on what data you need to move to MYOB.
Keeping in mind that all of your data orginates in MYOB and remains in MYOB, I am not quite clear on what data you would actually have to transfer from Access to MYOB. Are you contemplating additional tables in Access in which additional data would be stored and merged with the MYOB data by users in preparation for export to MYOB?
DaveT
Hi George.
Yes, I have created linked tables in my database which are stored in the MYOB accounting application. MYOB stands for Mind Your Own Business, and is a very popular accounting package for small business here in Oz.
The package itself come with 200+ reports but I want to create (if I can do it) a little package of reports that will include accounting formulas as well as a couple of graphs. I imagine the management kind of 'flicking through pages' that are these reports and graphs like moving back and forth through different forms. Maybe like different tabs for each page on a form?
The controls I refer to would be eg. for the user to select a particular month for a P&L report, and have those user options for criteria that you mention.
So far I have created a query that presents standard information about an unpaid invoice, Date, Inv No., Amount etc. I would like to present each invoice detail on a row in a form, and have a check box next to each one that can be checked as one that is authorised for payment. I think I'll forget the idea of creating a file to import into myob for now, but would like to produce a simple report with the details of those invoices that have been checked. This is one task on the accounts payable side of things and there are others that I will tackle further down the line on the receivables, but when I get clever enough, I'd like to work with both to establish a good (but simple) cash flow model within the database?
Another problem I have is compiling my profit and loss report. So far I have a query that lists expense account names and amounts. I have another query that lists income accounts and amounts. My problem is that I need to calculate a total for each list, and then subtract one total from the other total. How can I get more than one query and totals in the same report? Also, I want this report to be part of the package and present it on a form?
I've been fairly used to writing formulas in Excel but in Access? I know fields should be surrounded in square brackets but I need a much better understanding of the basic rules in writing formulas.
Essentially George, I need to come up with a set of easily read reports for management who don't want anything too conveluded or technical.
Hope this gives a better idea on where I'm at and hope you can help.
GroverParkGeorge
I see. Well, all of this is quite doable.
The problem is that you have described the scope of a project which will take many hours of development to accomplish. Many hours. Far too much for anyone to really offer you more than general suggestions about how to proceed. For example, you describe "a little package of reports that will include accounting formulas as well as a couple of graphs." Let's say each such report takes a half day to create. If you have 10 of them, you're talking a full 5 days of work. Plus you need the user interface to support "paging" through them. Again, all quite doable, but between the reports and this interface, which really is just an idea at this point, you're looking at least one full week of work, IMO.
Another example of where you're going to need to do more work to get started: " I would like to present each invoice detail on a row in a form, and have a check box next to each one that can be checked as one that is authorised for payment."
That would require a continuous subform based on a query that retrieves the required data from the relevant tables. Adding the checkbox, though, is going to take some additional functionality. That's because the checkbox won't be bound to a field in your original table unless it contains such a field natively, which I doubt is going to be the case. Is there a field in a table in MYOB where you can click "select for payment" for certain records to be paid? Could you include that field, if it exists, in such a query on the Access side? That would make this requirement possible with a minimum of new functionality in your Access database.
If that is not available, you'll probably need a temp table on the Access side into which you'll insert a set of records to review. That temp table can include this "select for payment" field to use in the form. But then, how do you tell MYOB about that selection? I guess one way would be to print out the list and use it to go back to MYOB to make the payments. Without knowing a good deal more than we do about MYOB, we couldn't even begin to offer guidance on automating the program to do that. It might not even be feasible.
Formulas in Access are not created the same as they are in Excel, but you should be able to create equivalents if you know what they are. I don't know that a P&L statement would have many advanced formulas, but you will need to do basic math (add, subtract, multiply and divide) which are, of course, available. They'll mostly be handled in queries which provide the recordsources for your reports. Access reports also do allow you to create calculated controls into which you can insert formulas, such as a total over a particular value.
The best way to proceed is to just start with the simplest of the reports you think you need in this management package. Link to the tables that provide the relevant data for that report. Then use those tables to create a query which returns the relevant data. Then, use the built-in Report Wizard in Access to start developing a report.
HAs you get into the process, you will encounter specific issues. Post questions about those issues. We can address them with you to help you get that first report built. Then move on to the next one. As you accomplish each step your understanding of Access will grow. Within a few weeks you should be able to create an initial version of this management report package so that you can begin to get feedback from your users.
DaveT
Thanks George
realise step by step is very important, and first things first but I don't want to create a monster that I can't control or get any value out of after all the hours put in. I'm prepared to (and management is happy for me to) spend the time required to develop this. I won't be back to it until tomorrow but would really like to understand initially how to present the list of income and expense accounts for my P&L report with their totals and use those totals in a formula eg total income minus total expenses?
Cheers. David.
GroverParkGeorge
As I've suggested a couple of times, we can't see your MYOB tables so it is just not quite possible to give you specific directions here. I can offer a path to follow in general terms.
ou'll create a query that lists the income and expense accounts. That query will be drawn from a table or tables in which those account names are stored. I assume your MYOB program does have such tables.
You'll need to include in that query the field(s) which have the current totals for those accounts. Again, this is based on the assumption that such fields exist in your tables and that you have linked to the tables in which those fields appear.
HAs far as income totals are concerned, you can use the SUM() function to create totals (sums) for those fields. THen, when you have the total for income and the total for expenses, the formula to create the net is rather straightforward subtraction: e.g. Sum(IncomeField) -Sum(ExpenseField)
That calculation can be done either in the query or as a calculated control on a form or in a report.
From your description, I am thinking it will be on a form. It sounds like a continuous view form bound to this query will work best.
DaveT
I don't have a single query. I have one each for income and expenses which lists the individual income and expense accounts with their amounts, and then another one from each of those queries to total the amounts listed so 4 queries in total? Are you saying that I will need to try and establish just the one query for the form or report?
GroverParkGeorge
Once again, I'm not able to see ANY of the tables or queries involved, so I've been speaking in generalities. Unless I were to see the actual tables, then anything I say is going to remain a generality. That's just how it is.
"m not following your statements very well. You said, "I don't have a single query." Then you go on to describe the queries you DO have: One each for income and expenses (that's two so far), plus two more queries to total the amounts. So that is four queries you already have created. But you end that with a question mark, suggesting something else might be the case.
You should be able to list all transactions against all accounts, both income and expense in a single query (hint, I still can't see those tables so I have no way to know how accurate that statement is). I'm assuming here that you have a table of transactions which lists transaction amounts and the accounts against which they are charged. I can't believe that the acounting package lists expense transactions and income transactions in separate tables, but, since I can't see the the package, I don't know for sure.
Oassume assume that a SUM() on the "Amount" field for each account should be sufficient. However, that assumes that your accounting package enters all transaction amounts in a single column as either a positive or negative amount, and also records the account for that transaction, which permits a summing of that field. But, since I can't see the table, that is still just an assumption.
I really don't know what additional queries you need. Perhaps you do need a second query, based on the first query, that aggregates all expense accounts and all income accounts. In that query, should you decide to create it, you could also include a calculated field to obtain the net by subtracting the sum of expenses from the sum of income. However, if you use this query to provide records to a form or report, you could also put the net income calculation directly in a control. And, depending on the form you create, you could use calculated controls to sum expenses and income, eliminating the need for the second level, aggregated query.
At this point, I think your most productive approach is going to be putting some work into creating queries that return what you think you need in your report and seeing h
DaveT
Sorry George, I should have attached this in the first place. I have imported the tables without linking them and there are only really two of them.
You should be able to see the queries I'm talking about and the messes I've been making trying to nut out the P&L report.
David.
GroverParkGeorge
I see that you've made some progress with creating queries to aggregate amounts from accounts for different purposes. And, of course, you've started the reports.
Is I said earlier in this thread, this is HOURS of work to sort out. If you have ONE specific question, please ask it and maybe I can focus on that one thing.
DaveT
For now I just need to understand how to present those accounts and do the arithmatic with the totals as in a P&L report into the one report or form. I've got a little bit of experience with code writing and believe that this is what I might need to do, but wondered if there is a way that I can get the fields from the multiple queries into the one report without code?
GroverParkGeorge
I can address one question regarding the insertion of a query into a form at a time. In looking at what you have now, I can see that you've done a lot of the work already. Which of the reports in the sample database do you want to work on? What is the problem with THAT one report? What does it show NOW? What SHOULD it show? How do you want to organize the data in it? What summaries are needed? Be as specific as you can. Thanks.
DaveT
If I look at rptMthP&LAccounts, all I have is groups of accounts and their totals in the group footer. ie income accounts (and total 11637.51), expense accounts (and total 8172.47).
need to present this information in a standard profit and loss format that will;
1. List all of the income accounts first, and total them (11637.51)
2. Then list expenses with a total of all expenses (8172.47)
3. Show the net profit figure (calculated at 3465.04)
My question is simply how to go about organising this information to present it in a standard P&L format. There are other groups of accounts in the report such as Cost of Sales (COS), Other Income (OI) and Other Expense (OE) which I can use to give me different subtotals like gross profit, but I am just trying to get the technique correct in doing the calculations and producing a report.
Thanks. David.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.