1. Visiting from the US? Why not try DIYnot.US instead? Click here to continue to DIYnot.US.
    Dismiss Notice

Help with MS Access appreciated :-)

Discussion in 'Software' started by Brigadier, 15 Nov 2020.

  1. Brigadier

    Brigadier

    Joined:
    14 Jan 2008
    Messages:
    9,945
    Thanks Received:
    776
    Location:
    Staffordshire
    Country:
    United Kingdom
    Hello All,


    I am trying to make a fairly simple database, but am really stumped by a data entry problem.

    Illustration of situation:

    A drinker can go to any pub on any date, and buy any drink or drinks they like.
    I want to capture each visit to the pub on a database.
    I want to capture each drink bought during each visit.
    However, I do not want to have to re-input the pub name, date of visit, and the drinker's name for each drink bought during that particular visit.

    I want to be able to input:

    Bob visited The Flying Horse on 11/12/2019, and bought:
    - a pint of Landlord
    - a packet of roasted nuts
    - a pint of Pepsi

    i.e. only inputting "Bob / Flying Horse / 11/12/2019" once, and associating that visit to each of the purchases made there.

    (Each purchase would end up as a row in a table I presume, which contained a field for every aspect of that individual purchase? Bob / Flying Horse / 11/12/2019 / Pint of Landlord)


    I can't see past doing it the long-winded way:

    input 1; Bob / Flying Horse / 11/12/2019 / Pint of Landlord
    input 2; Bob / Flying Horse / 11/12/2019 / Packet of roasted nuts
    input 3; Bob / Flying Horse / 11/12/2019 / Pint of Pepsi


    I thought along the lines of creating tables for:

    - drinkers
    - pubs
    - purchases

    with an intermediate table for a visit, populated with content from the drinkers and pubs tables,

    which then (with each input purchase) populates a row for every purchase.


    I've gone so far down the rabbit hole, I have to keep scrapping the lot, and starting again!


    And, as with so many things where one is not expert, knowing the correct question to ask is most of the battle.......


    Thanks in anticipation

    :)
     
  2. Sponsored Links
  3. rossj81

    rossj81

    Joined:
    13 Nov 2019
    Messages:
    40
    Thanks Received:
    13
    Location:
    Warwickshire
    Country:
    United Kingdom
    I think you've already got the solution. Four tables:
    - drinkers (customers might be a better term)
    - pubs
    - purchases
    - visits

    Drinkers 1:* visits
    Pubs 1:* visits
    Purchases *:1 visits

    So you'd store Bob on drinkers, Flying Horse on pubs, the date on visits, and the purchase item, quantity, and amount on purchases.

    I don't know why you say you'd need to enter the date each time, unless you're storing the date on purchases instead of on visits.
     
    • Thanks Thanks x 1
  4. Chris_W

    Chris_W

    Joined:
    26 Sep 2019
    Messages:
    5,717
    Thanks Received:
    1,514
    Location:
    North
    Country:
    United Kingdom
    Last edited: 15 Nov 2020
    • Thanks Thanks x 1
  5. Brigadier

    Brigadier

    Joined:
    14 Jan 2008
    Messages:
    9,945
    Thanks Received:
    776
    Location:
    Staffordshire
    Country:
    United Kingdom
    That's my point ; I don't want to enter the date (or the pub name, or the customer's name) for each transaction, but I can't see how not to.
    Basically, I want to enter the visit details once (Bob, at the flying horse, 11/12/2019), enter each transaction he makes in that visit as a separate transaction, then when I close that visit, ensure that the visit details are tied to each transaction record.
     
    • Like Like x 1
  6. Chris_W

    Chris_W

    Joined:
    26 Sep 2019
    Messages:
    5,717
    Thanks Received:
    1,514
    Location:
    North
    Country:
    United Kingdom
    I think this is where you need the relational database. Then I think you’d create a query, which could be date specific, name specific,or even drink specific. So for instance you could create a query for who drank let’s be say Carling - so it would ask you to enter Carling and results would display Bob, and you could include dates etc. For data entry you don’t have to enter the date each time it changed, but the customer details would remain once entered, if that makes sense?

    (Last time I properly worked on/used access was around 18 years ago. So might be a little rusty).
     
    Last edited: 15 Nov 2020
    • Thanks Thanks x 1
  7. Chris_W

    Chris_W

    Joined:
    26 Sep 2019
    Messages:
    5,717
    Thanks Received:
    1,514
    Location:
    North
    Country:
    United Kingdom
    I think once your tables are setup, you could then enter it as:

    Bob, flying horse, 11/12/2019, purchased: pint of landlord, packet of roasted nuts and a pint of Pepsi - is this how you want it?
     
  8. Sponsored Links
  9. Brigadier

    Brigadier

    Joined:
    14 Jan 2008
    Messages:
    9,945
    Thanks Received:
    776
    Location:
    Staffordshire
    Country:
    United Kingdom
    Yes, but my point is that the "point of Pepsi" would effectively be on a row of the table, along with "Bob, Flying Horse", etc. Great.
    But,
    I would also have to input "Bob, etc" on the next row, when I input the "roasted nuts" transaction......

    Basically, I want to input the visit details once, but have those details automatically populate each row on which a transaction occurred during that visit.......
     
  10. Brigadier

    Brigadier

    Joined:
    14 Jan 2008
    Messages:
    9,945
    Thanks Received:
    776
    Location:
    Staffordshire
    Country:
    United Kingdom
    I was thinking the visit details on a form, with the transaction (s) on a subform?
    But I don't know how to get the visits populating each transaction though still.
     
  11. Chris_W

    Chris_W

    Joined:
    26 Sep 2019
    Messages:
    5,717
    Thanks Received:
    1,514
    Location:
    North
    Country:
    United Kingdom
    No I don’t think you would, I can’t remember how it’s done, but you can have Bob, etc on one row, then add each item, but only his details once, or access does it automatically, but when you show the form, it’s just his purchases. So in the form would be Bob, date, items purchased. Not Bob 3 times, date 3 times and each item.

    Think I’ve got access on my laptop, might be able to have a look later.
     
    • Thanks Thanks x 1
  12. rossj81

    rossj81

    Joined:
    13 Nov 2019
    Messages:
    40
    Thanks Received:
    13
    Location:
    Warwickshire
    Country:
    United Kingdom
    I think you've got an issue with the form that you're using to input the data - the database design is correct.

    As you say, you'd enter the drinker, pub, and date on the main form, and the purchases on a subform.

    When you commit the transaction, get the visit ID from the main form, and add it to each purchase record.
     
    • Thanks Thanks x 2
  13. Brigadier

    Brigadier

    Joined:
    14 Jan 2008
    Messages:
    9,945
    Thanks Received:
    776
    Location:
    Staffordshire
    Country:
    United Kingdom
    Thanks @rossj81, at least I know I'm not going further down the wrong rabbit hole:)
     
    • Like Like x 1
Loading...

Share This Page