Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    mike02 is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jun 2012
    Posts
    245

    adding/updating numbers, defualt value of Zero

    Hey all,

    I currently have a split form set up. I can filter on the combo box i have set up, CompanyID is the Filter, Numeric.

    Ok so here is what i am wanting to do.

    I want to create a form that allows me up have a table setup like this:
    ........JAN...FEB...MAR...APR...MAI...JUN...JUL... AUG...SEPT...OCT...NOV...DEC...Total.
    2013
    2012
    2011
    2010
    2009
    2008..........(Input in each cell like excel).
    2007
    2006
    2005
    2004

    The table i would have the data entering is set up as the following:



    ID(PK)...CompanyID(FK)....MonthDate....NumberofSal es...

    for month date i have it set up as ending day of each month. eg ( 31/07/2004) It starts Sept 04, and goes till present. If i do not input data, thats fine, but i would like it to auto matically delete the ones prior. meaning if it starts in 2008, I want it to delete all 2004-2007 Records. and The days past IE (Future). The total column has no input, I use it for reference when entering data, it is just a formula setup to cross reference the numbers!

    I dont know how i can start this, but thought id see if someone would, Thanks!

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    What you're asking for is called a crosstab query, in order to get the column headers to be in the order you want though you'll likely have to change the labels to something like '01 (Jan)', '02 (Feb)'

    As long as your setup query is showing you want you want (Year, Month of that Year and a numeric value for each data point, even if it's 0) you can set up a crosstab query showing you exactly what you've laid out.

  3. #3
    mike02 is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jun 2012
    Posts
    245
    I already have a cross tab query set up in this format, how do i use this to then make it an input form? thanks

  4. #4
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    You can't use a crosstab as a data entry method, if you're attempting to create a matrix via a form you'd have to do that with code if you wanted it to visually look like what you displayed during data entry.

  5. #5
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    P.S. Here's an example database I wrote some time ago to demonstrate how you'd go about entering a matrix in a normalized table but visually be able to see the matrix at the time of data entry.

    coolhandphil.zip

  6. #6
    mike02 is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jun 2012
    Posts
    245
    Thanks, I am taking a look at it now, will post with any questions etc.

  7. #7
    mike02 is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jun 2012
    Posts
    245
    Hey, Im working with your matrix form,

    I have it working with the T, and F values, but the X values keep saving as zero, is there away to allow negative values and decimals? Also, How can I set it up to ad An ID, based on a combo box? My thinking is to have the combo box value (Boundcolumn ID) to set it up like the F valueHeading, and have it be = to that, I can follow your code, I just dont know how to manipulate it to what Im wanting.

    Cheers,

  8. #8
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    I do not understand your description of what you want to do. If you are trying to store values like .123 you won't be able to unless you alter the column properties of the X value. Right now it's long integer you'd have to change that to Double with a number of decimal places pertinent to your business. And yes, you can have combo boxes for your F values, just make sure you prevent someone from entering the same F value twice (in your case having the same year on two separate rows).

  9. #9
    mike02 is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jun 2012
    Posts
    245
    Hey, so that worked, I was able to get the numbers to save. Now, click the clear button I get an error, at:

    ctl = Null

    and when I click any other button nothing runs. Would this be because I have default values as 0, and have the years and months (F values, and T Values as defaults as well, so know one can edit those values?

    Also,

    I want to add in another field. ID:

    This value will be the same for every record save. This is because I will be appending the data to my other table after. This will then allow me to search on my other form.
    How would I add in another data for the sql, to save it as well? The number will be selected from a combo box. The combobox shows the name in the second column and holds a numeric value in the first column. I want to have the first column then bound to the data. eg:


    ID....F.....T.....X
    1...2012..2.....0.014
    1...2012..3.....0.0158
    And so on.
    Note: ID remains the same.

    Thanks,

  10. #10
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    You'll have to be more specific about how you are producing your error and what the error message is. the ctl=null (if I remember correctly) is inside a loop so it's choking on attempting to clear the contents of a control that is likely locked, invisible or where the cursor is already in the control. You'll have to be very specific about how you are producing the error.

    The ID field is in the table because I put a PK on every table I create, if you don't want it, don't append it when you append the other data.

    As far as the rest of what you're asking, I don't understand.

    If you're trying to find the X value for any pair of F/T values all you have to do is link the F/T values to your matrix table and add the X field to your query.

    For instance if you had

    Code:
    tblTest
    Test_ID  Customer_ID  PO_Number  F_Value  T_Value
    1        1            10000      2012     3
    You'd just link the F_Value and T_Value back to your matrix table to retrieve .0158

  11. #11
    mike02 is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jun 2012
    Posts
    245
    So im confused on how to set this up. I have attached an example.

    So i have modified your matrix frm for another DB I am working on as well to suit my needs. On the Form I want to select the company, Input all the Returns necesary, and then have it update into My Monthly_Returns Table. When seleceting The company It will update the ID into Company ID. and so on.

    Thanks

  12. #12
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    if your sample is in access 2010 I can not read it. If you can convert it (preferably to access 2003 but I can view 2007 files as well on a limited basis) I can take a look.

  13. #13
    mike02 is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jun 2012
    Posts
    245
    This is saved as proper format for access 2003

  14. #14
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Ok I have it.

    Are you intending to add the companyID to the table tbl_Matrix in the FUND_ID field and is that FUND_ID supposed to be the COMPANYID from the combo box? At first look you haven't modified any of the code to handle the additional data point. What does the table Monthly_Returns got to do with the problem?

  15. #15
    mike02 is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jun 2012
    Posts
    245
    FundID should be CompanyID, and Monthly returns is where i am wanting the data to end up. do i need an indirect route like i have set up with the append query, or can I run it straight into Monthly_Returns. Yes, if CompanyID ran into tbl_matrix it would allow me to append the data into Monthly_Returns easier.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 07-03-2012, 12:27 PM
  2. To set a defualt value for a query input
    By mercapto in forum Queries
    Replies: 1
    Last Post: 06-06-2012, 09:54 AM
  3. Adding numbers
    By imtiaz703 in forum Access
    Replies: 1
    Last Post: 02-14-2012, 09:50 AM
  4. Adding line numbers to a purchase order report
    By jordanturner in forum Reports
    Replies: 1
    Last Post: 12-08-2010, 08:25 AM
  5. Adding numbers to dates
    By MFS in forum Programming
    Replies: 5
    Last Post: 11-24-2010, 12:06 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