Results 1 to 15 of 15
  1. #1
    kcollop is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jul 2012
    Posts
    18

    Table with variable data

    I have one table and want to create a report on fuel costs by community



    gas price diesel price heating oil price
    town 1 3.00 3.50 3.75
    town 2 2.00 2.50 2.75
    town 3 1.00 1.50 1.75


    I have 1000's of meter tickets that I want to add into a table or report by ticket number based on product sold:

    AA12345 gas 50 gal town 1
    AA12346 gas 75 gal town 3
    AA12347 diesel 80 gal town 2
    AA12234 gas 20 gal town 3
    AA12254 heating 400 gal town 1

    I want a report that gives me the answer:

    AA12345 gas 50 gal town 1.....50 x 3.00 = $150.00
    AA12346 gas 75 gal town 3.....75 x 1.00 = $ 75.00
    AA12347 diesel 80 gal town 2.....80 x 2.50 = $200.00 etc
    AA12234 gas 20 gal town 3
    AA12254 heating 400 gal town 1


    I have 25 communities and 3 prices for each community.

    How best can I approach this, without writing a lot of If statements.
    I am an intermediate Excel user but a pre-basic user in Access.

    Thanks

    Steve

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    The proper normalized design for the table is 3 fields, town fuel type and price:

    Town1 Gas 3.00
    Town1 Diesel 3.50

    and so on. That makes looking up the price easy.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    kcollop is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jul 2012
    Posts
    18
    That makes sense.
    I will try this and see where it goes.
    Thanks

    Steve



    Quote Originally Posted by pbaldy View Post
    The proper normalized design for the table is 3 fields, town fuel type and price:

    Town1 Gas 3.00
    Town1 Diesel 3.50

    and so on. That makes looking up the price easy.

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    No problem. Sometimes it's actually a liability to have Excel knowledge when starting to work with Access. The proper table layout of a relational database is typically nothing like how you would do it in Excel. Here's one link among many:

    Fundamentals of Relational Database Design -- r937.com
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    kcollop is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jul 2012
    Posts
    18
    Quote Originally Posted by pbaldy View Post
    The proper normalized design for the table is 3 fields, town fuel type and price:

    Town1 Gas 3.00
    Town1 Diesel 3.50

    and so on. That makes looking up the price easy.
    I tried this but I think I need to make "Town 1 Gas 3.00" a numeric field rather than a text field.

    Do I need to somehow split this or make an association to a numeric field so that I can multiply my quantity field with the 3.00 amount?

    Thx

    Steve

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You must have misunderstood me. I said that was three fields. The price would certainly be a numeric field, probably currency.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    kcollop is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jul 2012
    Posts
    18
    I have to stop thinking in Excel !!

    Ok...what I want is to be able to input a fuel ticket in gallons.......pull down a drop down menu to give me the choice of town, another drop down for the choice of product (gas, deisel, heating oil) with the corresponding price for that town ( i.e 3.00, 3.25, 3.50). Then I want the fuel ticket value (fuel unit price for that town x quantity).

    Then, I input another ticket.

    I have created a towns/product/price table...but I need the "price" part to be in decimal (to 1 place) format. I figured this out (double option) if the price is in one field, however I can't seem to make a drop down with the price as a decimal and showing the associated town and fuel type.

    Any easy way....?

    thx

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Can you post the db here? I'm not sure we're on the same page.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    kcollop is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jul 2012
    Posts
    18
    I posted a pdf of the two tables with some notes

    I could not upload the db.

    Steve
    Attached Files Attached Files

  10. #10
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Your problem is the lookup field. The extended cost is using the ID from the price table rather than the price. Is extended calculated by you or is that the new calculated field? Rather than a lookup field, I'd use the second technique here to place the actual price into the field:

    BaldyWeb - Autofill

    I probably wouldn't have both the Price and Price1 fields. I'd just store the price.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  11. #11
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Should have added this, if you're using the lookup field:

    http://access.mvps.org/access/lookupfields.htm
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  12. #12
    kcollop is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jul 2012
    Posts
    18
    I think I found my problem.

    Is this true: In Access 2010 you can create a calculated field in a table (which I can do)....but the calculation cannot include fields from other tables or queries AND the result of the calculation is read-only.

    This defeats what I was trying to do.

    I might have to copy and paste data into a spreadsheet...but I don't want to go this route.

    Steve




    Quote Originally Posted by pbaldy View Post
    Your problem is the lookup field. The extended cost is using the ID from the price table rather than the price. Is extended calculated by you or is that the new calculated field? Rather than a lookup field, I'd use the second technique here to place the actual price into the field:

    BaldyWeb - Autofill

    I probably wouldn't have both the Price and Price1 fields. I'd just store the price.

  13. #13
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Yes, 2010 has a calculated field. I haven't used it, but your description sounds about right. I think you can do what you're doing, but you have to drop in the actual price rather than use the lookup field. The link I posted would allow you to do that. You actually want to anyway, as next month when prices change, you want the price associated with a particular transaction to remain static. If you use the lookup table, you'll get the new price applied to old transactions.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  14. #14
    kcollop is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jul 2012
    Posts
    18
    Well I have a temporary fix. I have the look up value going to Price1 and then I type in the amount to Price2 so as to multiply with the quantity field to get the dollar amount of the purchase. Its a work-around for now.

  15. #15
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    The method using a combo in my link didn't work for you?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. data type variable constants
    By markjkubicki in forum Programming
    Replies: 2
    Last Post: 06-20-2012, 09:57 PM
  2. Fill data entry textbox with variable
    By broecher in forum Forms
    Replies: 6
    Last Post: 11-18-2011, 07:54 PM
  3. Define Table with a Variable
    By Bedsingar in forum Queries
    Replies: 3
    Last Post: 07-18-2011, 07:07 AM
  4. SELECT INTO variable table name
    By Ian P in forum Queries
    Replies: 2
    Last Post: 05-29-2010, 12:49 AM
  5. Replies: 2
    Last Post: 01-25-2010, 09:23 AM

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