Results 1 to 15 of 15
  1. #1
    Derrick T. Davidson is offline Competent Performer
    Windows 8 Access 2010 64bit
    Join Date
    Mar 2013
    Posts
    142

    There has to be an easier / better way

    Hi


    I have a database that is growing and this method worked ok while the accounts were only a few, to identify the account and the debits and credits I used an expression similar to below for each account but I have to go into the query and add the account name each time:

    iif([accountname]="Jones", [debitamount],0)
    iif([accountname]="Jones", [creditamount],0)

    I assume you will look upon this method with dismay but I am a self taught novice, is there a way to automate this so when I add an account the debits and credits can be stripped out automatically?

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    I am not sure what you are doing with these accounts. (no context)

    If you are clearing the acct to start... you need to build an update query.

    If you are using 'special' accounts to show, the ACCOUNT table should have a flag on the name, whether or not to use (like [ENABLED])
    Either way DO NOT HARDCODE NAMES into queries.

  3. #3
    Derrick T. Davidson is offline Competent Performer
    Windows 8 Access 2010 64bit
    Join Date
    Mar 2013
    Posts
    142
    This database is for a small (very small) business, the account name is the [client] and the [client] deposits xx.xx dollars in their account [creditamount]. As the [client] orders the deposit is taken from the account [debitamount] and each month a statement is produced showing credits minus debits = balance. Just like a mini bank account statement. The information is also used for cash flow, product inventory, historical buying, etc.

    I understand I am probably breaking all your expert rules with "HARDCODING NAMES" but it works ok the problem is adding new accounts when a whole series of queries have to have "HARDCODED NAMES" added.

    As I said I am self taught (with the help of this forum) and am learning as I go along.

  4. #4
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    I get that part but why is JONES the only account using
    iif([accountname]="Jones", [debitamount],0)

    Wouldnt every account get [debitAmount]?

  5. #5
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    if you are uninterested in changing your structure (i.e. having a primary key in each table and using that as a reference rather than a changeable text value) your formula would be more like:

    iif([accountname]=[Enter the Account Name], [debitamount],0)
    iif([accountname]=[Enter the Account Name], [creditamount],0)

    Or, more realistically, if you are opening this query for a single client at a time put

    [Enter the Account Name] in the criteria of the customer name, then you will only get data related to that client anyway so your formulas would just turn into the field name [debitamount] or [creditamount].

    Even though it may seem painful to you modifying your database with the proper structure now (while it's still small) is going to make your long term maintenance of your database a lot more easy.

  6. #6
    Derrick T. Davidson is offline Competent Performer
    Windows 8 Access 2010 64bit
    Join Date
    Mar 2013
    Posts
    142
    I am VERY open to changing the structure of any table or database at this point as you point out it is still very much in its infancy. But I do want to do it right.

    I open the database with all the accounts open and when I need client specific I have a table with the [client name] and [from] [to] this is another query that links the [client] and [dates] to show only that client, again I apologize if this is a clumsy way but it works so far.

    My account query has several [debits] and [credits] i.e.

    iif([accountname]="Jones", [debitamount],0) iif([accountname]="Jones", [creditamount],0) iif([accountname]="Smith", [debitamount],0) iif([accountname]="Smith", [creditamount],0) etc etc etc

  7. #7
    Derrick T. Davidson is offline Competent Performer
    Windows 8 Access 2010 64bit
    Join Date
    Mar 2013
    Posts
    142
    I am VERY open to changing the structure of any table or database at this point as you point out it is still very much in its infancy. But I do want to do it right.

    I open the database with all the accounts open and when I need client specific I have a table with the [client name] and [from] [to] this is another query that links the [client] and [dates] to show only that client, again I apologize if this is a clumsy way but it works so far.

    My account query has several [debits] and [credits] i.e.

    iif([accountname]="Jones", [debitamount],0) iif([accountname]="Jones", [creditamount],0) iif([accountname]="Smith", [debitamount],0) iif([accountname]="Smith", [creditamount],0) etc etc etc

    By the way all my tables have primary keys already i.e. [AcctID] auto number, ProdID auto number etc.

  8. #8
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    I think you want a query that access the ACCT the form is currently showing. (here I used: frmMain)

    Go to the client on the form..
    So it would look like:
    "select * from tAccounts where ([accountname] ='" & forms!frmMain!AcctName & "'"

    or use the primary key instead of name. (names can duplicate)
    "select * from tAccounts where ([accountID] =" & forms!frmMain!AcctID


    (am I getting in the ballpark?)

  9. #9
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    so you have a query with two columns for each client, a debit and a credit column. Doesn't that get out of hand pretty quickly? I mean you wouldn't be able to display more than about 5 or 6 clients on a single report if you're sticking to one page widths.

    It sounds like you're trying to turn your data into a spreadsheet which sort of defeats the purpose of having a database to start with.

    Maybe you can say what the purpose of the output is as well?

    I'm assuming in your payment table your transaction table you have something like this:

    PK CustomerID Debit Credit
    1 1 500
    2 1 4000
    3 1 3000
    4 2 20
    5 2 120
    6 2 40


    And your final query looks something like this:

    Code:
    JonesCredit  JonesDebit  SmithCredit  Smith Debit
    3000         4500        40           140
    but the question is why?

  10. #10
    Derrick T. Davidson is offline Competent Performer
    Windows 8 Access 2010 64bit
    Join Date
    Mar 2013
    Posts
    142
    Sorry to disappear so abruptly I’m (retired) in the Philippines and it was getting late.
    You are correct in your assumption on the layout of the [transaction] table and the [query] three entries [debit] [credit] & [balance], your initial comment ‘getting out of hand pretty quickly' is also very astute.
    However through subsequent queries the information serves as single client statements, immediate and historical product data, current and projected financial data, etc. Other queries link Vendor pricing, stocking, cash flow, etc. Plus a myriad of other reports too frivolous to mention, but valuable to the individuals receiving them.
    As I have said it all works and gives me the data and reports I need.
    My immediate basic (I’m sure you identified many others) problem is adding new accounts as after adding to the accounts table I have to encode each account name (I assume this is “HARDCODING” you frowned on earlier) into the query also, leading to typo issues and I am sure the query will be REALLY out of hand in the not too distant future.

  11. #11
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    The data structure appears to be properly normalized but you're report presents the data in a non-normalized arrangement. You are emulating a CROSSTAB query with the hardcoded expressions. Review http://allenbrowne.com/ser-67.html

    If you want to stick with the non-normalized report, doesn't matter which way you go, will have to edit at least report structure each time a new client is added.

    The 'easier way' is to not de-normalize data.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  12. #12
    Derrick T. Davidson is offline Competent Performer
    Windows 8 Access 2010 64bit
    Join Date
    Mar 2013
    Posts
    142
    Thank you, I have crosstab query which I use for profitable accounts, dormant accounts etc. That is not my problem, the only problem I have is not being able to insert a client name automatically into my query. I was just curious as, if I go to my bank online and open a new account the account shows immediately in my account summary, I guess this is for way more advanced people than my humble self.

    From your comments sir I assume it is not possible to auto insert the specific client into the query[s]

    Thank you for your patience.

  13. #13
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    It is possible to have code modify query using QueryDefs collection. Code can delete and recreate query. Not sure about code modifying query to add a calculated field. Regardless, still have to correctly type the expression for the new calculated field.

    And still have to modify the report structure.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  14. #14
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    This doesn't put it out in the layout you want, but it does give you all the columns you are asking for.

    DTD.zip

    I likely could have done it without the setup query. Linking the two crosstabs requires an artificial link which could be set up in the crosstabs themselves.

  15. #15
    Derrick T. Davidson is offline Competent Performer
    Windows 8 Access 2010 64bit
    Join Date
    Mar 2013
    Posts
    142
    COOL thank you sir

Please reply to this thread with any new information or opinions.

Similar Threads

  1. The Golden VB Script that will make your life much easier
    By pkstormy in forum Code Repository
    Replies: 55
    Last Post: 04-14-2015, 12:08 PM
  2. Easier approach to gaining a sumation
    By GraeagleBill in forum Programming
    Replies: 5
    Last Post: 08-22-2013, 10:15 AM
  3. Easier way than adding 200 queries?
    By Kamal in forum Queries
    Replies: 2
    Last Post: 02-28-2013, 07:02 AM
  4. Replies: 3
    Last Post: 05-14-2012, 01:09 PM
  5. Replies: 8
    Last Post: 08-11-2010, 09:20 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