Page 4 of 4 FirstFirst 1234
Results 46 to 58 of 58
  1. #46
    JeffGeorge is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2013
    Posts
    111
    Your assumption is correct: there are accounts that will not be used for this particular report. My tblP&L is a version of the source SQL table that includes only the fields I need to create the data needed for the report. Temp.txt is a subset of those records in which I have assigned the field accountType a value, which means that is a record that could be used in the P&L report.

  2. #47
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    I'm attaching another sample database. The data is all the data you gave me, the only thing I did was create what amounts to a local table that will help with the report sorting/grouping.

    JeffGeorge.zip

  3. #48
    JeffGeorge is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2013
    Posts
    111
    Wow! That is amazing work! And you were able to come up with all that so quickly. That's what I want to be able to do. I'd like to ask a couple of question if I may:

    1) Could you describe, from a high level view, what the process is that results from the user's input of data? Does that kick off a query that starts the whole process flowing?

    2) As you can see from the sample data, sales are stored in the account as negative number. Where would I put logic to multiply that one value by -1? Or actually, if it's easier I suppose everything could be multiplied by -1 since all other values are expenses.

    Thank you so much for taking the time to show me that this can be done. I think it's fair to say that one should never underestimate the power of Access in the hands of someone that knows what they are doing with it.

  4. #49
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    2 is an easier answer yes, if you want to reverse the sign just multiply everything by -1 in the query that runs the report and sub report.

    1 I'm not sure what you're asking. From what you said the table I have listed as tblTest in my database mimics what is available in a summary table in your SQL back end. I do not know how that back end summary table is populated. or how often. The accuracy of your report is going to be based directly on how recently that summary table has been updated. If, however, your summary 'table' is actually a SQL view then it should be real time data.

  5. #50
    JeffGeorge is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2013
    Posts
    111
    The general idea of my question #1 was the sequence of steps. I think what you've done is something like the following:

    1) User inputs data into form.
    2) Form calls a report.
    3) Report executes an underlying Access query to populate data in report.

    Is that the general idea of it?

    As far as data accuracy, the SQL table gets updated in the current period (for example, we are currently working in PERIOD_6) any time someone posts a batch of transactions. This is where it's going to be a problem because the current period and current YTD can change daily. But since the source SQL table doesn't have the appropriate fields we need then there has to be a secondary source to drive the query. And this is where I get confused. Part of my confusion is because I just really don't know advanced Access procedures enough to understand what the sample you provided is doing and how it's arriving at the final result. I keep trying to sit and follow it through but the powers that be keep dragging me in four different directions since I'm leaving until next week and they don't want to allocate more hours per week for this project.

  6. #51
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    The only thing the user is inputting is the month and year they want to examine with the report. Whatever your current method of data entry is remains untouched.
    I'm using the month and year the user inputs as criteria for all the formulas for determining current month and year to date numbers (as explained in an earlier post it's easier if you cut and paste the formula to a text file and break it down so you can see what it's doing).

    As far as data accuracy, the SQL table gets updated in the current period (for example, we are currently working in PERIOD_6) any time someone posts a batch of transactions. This is where it's going to be a problem because the current period and current YTD can change daily. But since the source SQL table doesn't have the appropriate fields we need then there has to be a secondary source to drive the query.
    Stop, just stop.

    I've asked you a number of times and every time you answer I just get an explanation that doesn't make sense so I was going based on what I thought was happening.

    This is the most critical question: Is the data source I'm mimicking in my table tblTest coming from a *TABLE* or a *VIEW* in the SQL side of things?

    Yes or No only

  7. #52
    JeffGeorge is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2013
    Posts
    111
    Quote Originally Posted by rpeare View Post
    This is the most critical question: Is the data source I'm mimicking in my table tblTest coming from a *TABLE* or a *VIEW* in the SQL side of things?

    Yes or No only
    No.

    That satisfies the "Yes or No only" portion of our program. The data I posted in message #9 of this thread was an example of what the SQL data looks like. Since that by itself isn't enough to create the report shown in post #16 there has to be some intermediary action. I initially thought the Access report tools had more capabilities to write logic into them than they might. I'm used to using report design tools where each piece of data on the output can have as much code written behind it as you desire. Maybe that can be done with Visual Basic but that's a language I've never used. I'm not sure what part of the explanation you feel doesn't make sense. I've always said that the data comes from a SQL table but that the SQL table doesn't have fields that can be used for the grouping of data. That's where your first example came in. You demonstrated how to use an if clause to include only data that matched the user's input. But the data I presented as a source for that was the modified data that include an accountType, which does not exist in the SQL table. I need to be able to have the user click on a shortcut to the form, enter their month and year, and have it give them the report. I really don't know how else to explain it. If I could have the form execute a query that would build a table and categorize the accounts, etc. and then create the report I would do it, but I don't know how. I thought I could just do a free-form field placement on the report designer and put the values where I wanted, but it appears I can't. From what I can tell you had to add sort keys so that they would show in the correct order on the report. I hadn't anticipated having to do such a thing as that. I was envisioning being able to just say, "I want this field here, this field here, add these two fields, blah blah blah." That would probably work if I could just pull the data straight from the table but I can't. Maybe I'm just too wordy and that confuses the issue, but I don't know any other way to communicate.

  8. #53
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Look....

    The data you posted in post #9 is IDENTICAL in content to the text file you posted in post #26 (the text file tblP&L.txt) in terms of the fields that are critical to the report.

    The ONLY FIELDS I am using are the FY and Period_1 through Period_12, they exist in both.

    You're saying 'no' that doesn't come from a SQL table but in the very first sentence of your reply you say the data in #9 comes from SQL data, which I assume to mean it's coming from a table or a view.

    If the data in #9 is reflective of the data you have available through SQL then what I've given you works.

    My contention is that YOU DO NOT WANT TO CREATE THE REPORT DIRECTLY FROM THAT DATASOURCE, you want to have another local table with a 'conversion' from the raw GL number to something your report can use to sort and group. You are attempting to create all your sorting and grouping with formulas and that is a mistake. You will regret it and it is going to be extremely difficult to update if you ever add or change gl codes related to this report.

    In short, use a table that converts your GL codes to something usable for grouping/summing on your report.

  9. #54
    JeffGeorge is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2013
    Posts
    111
    Quote Originally Posted by rpeare View Post
    Look....

    The data you posted in post #9 is IDENTICAL in content to the text file you posted in post #26 (the text file tblP&L.txt) in terms of the fields that are critical to the report.

    The ONLY FIELDS I am using are the FY and Period_1 through Period_12, they exist in both.
    Your tblTest does use just fields that existin the SQL database. But where does your tblGLGroups come from? Is that a run-time creation or is it a stand-alone table that exists in the Access database?

    You're saying 'no' that doesn't come from a SQL table but in the very first sentence of your reply you say the data in #9 comes from SQL data, which I assume to mean it's coming from a table or a view.
    You are correct in that the original data I presented was pulled from the SQL database. When I said "no," I was referring to the data that I later provided that included the accountType field. I took that from a table that I created, as the accountType does not exist in the SQL table.

    My contention is that YOU DO NOT WANT TO CREATE THE REPORT DIRECTLY FROM THAT DATASOURCE, you want to have another local table with a 'conversion' from the raw GL number to something your report can use to sort and group. You are attempting to create all your sorting and grouping with formulas and that is a mistake. You will regret it and it is going to be extremely difficult to update if you ever add or change gl codes related to this report.

    In short, use a table that converts your GL codes to something usable for grouping/summing on your report.
    I understand this concept, in theory, but I do not understand how to do it in practice. I've tried formulas and I don't like what the results. I like what yours does. If I followed your lead, I could build a query that links the ODBC connected SQL database table to your tblGLGroups and get the results that you did? I could just add the SQL table to your database and substitute it in your query and the results be the same, correct? Am I finally getting it through my thick head?

  10. #55
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    But where does your tblGLGroups come from? Is that a run-time creation or is it a stand-alone table that exists in the Access database?
    This is what I've been saying all along. You need to create this table (or use the one I've created if it's complete) it is what's driving the subdivision of gl categories on the report

    If I followed your lead, I could build a query that links the ODBC connected SQL database table to your tblGLGroups and get the results that you did?
    Yes, exactly, the only issue you may face is that your field names will not be the same as mine, if they're different you'll have to go through the formulas and correct what's wrong.

    Theoretically if you linked the source table into Access and made the alias of that table tblTest, assuming the field names are what you displayed in post #9 you could run this report on your live data and have it work.

  11. #56
    JeffGeorge is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2013
    Posts
    111
    I'm going to try that now that everything is becoming clear. You've probably been the most helpful person I've seen on any sort of forum in the the last 20 years. I can't thank you enough for the patience you're showing.

  12. #57
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    So be sure to give rpeare some love by clicking on the little sherriff's badge under his helpful posts.

  13. #58
    JeffGeorge is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2013
    Posts
    111
    Consider it done.

Page 4 of 4 FirstFirst 1234
Please reply to this thread with any new information or opinions.

Similar Threads

  1. How do I know the data sources of a report?
    By lookingforK in forum Reports
    Replies: 3
    Last Post: 04-10-2013, 09:19 AM
  2. Replies: 21
    Last Post: 04-03-2013, 12:51 PM
  3. Mobile Reporting with Multiple Data Sources
    By ndallenaz in forum Import/Export Data
    Replies: 0
    Last Post: 02-15-2013, 08:09 PM
  4. Linking to multiple sources
    By jlfoster2 in forum Access
    Replies: 4
    Last Post: 07-02-2012, 05:47 PM
  5. Link to multiple sources
    By AquaChaos in forum Import/Export Data
    Replies: 4
    Last Post: 06-16-2010, 02:43 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums