Help with MS Access appreciated :-)

[Brigadier]

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

 

[rossj81]

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.

 

[CBW]

Entity relationships isn’t it? (Been a while since using access). One to many and key fields.

Edited: https://support.microsoft.com/en-us...n-basics-eb2159cf-1e30-401a-8084-bd4f9c9ca1f5

 

[Brigadier]

I don't know why you say you'd need to enter the date each time,

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.

 

[CBW]

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.

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).

 

[CBW]

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?

 

[Brigadier]

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?

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.......

 

[Brigadier]

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.

 

[CBW]

I would also have to input "Bob, etc" on the next row, when I input the "roasted nuts" transaction......

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.

 

[rossj81]

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.

 

[Brigadier]

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 @rossj81, at least I know I'm not going further down the wrong rabbit hole