Access or Excell for my needs

[hollies]

Hello, novice question as I am not sure if an Access database is right for my needs or if I would be better served with a spreadsheet.



I visit 20-25 different venues each year with my club for either 5, 7 or more days duration and have done so for many years.

As i will be arranging each years events, for the foreseeable future, I want to create something that will let me record all of these visits with start/finish date, name of venue, location, organisers etc. Added to this i want to record details of each venue with contacts, GPS data, phone details and notes etc. so that I can quickly get the details to contact them.

We will return to some venues many times over the years and others just the once.

The ability to be able to see/search for a particular venue and when we last visited, or how many times we have visited would be a must, as would what date did we visit a certain venue.

I have had a quick look at an Events database and i can see the possibilities but it it not quite right for me but I can perhaps edit it slightly with my very basic knowledge. However, I would need to add the contact details etc but not sure how I would go about this.

Hopefully the above make some sense

Is Access my best option or should I go with Excel.

Many thanks for any assistance.

Rob

[CarlettoFed]

I would definitely say Access.

[Welshgasman]

I would also say Access, but that takes a different way of thinking, if you are used to Excel.

I have created Excel workbooks in the past using dB principles as much as I could.
You could have one sheet for locations and a new sheet for each year.
I have something similar for my community work.
I have a monthly sheet that holds passengers and destinations, and a master workbook, where every monthly workbook is added to. Then my monthly sheet is cleared for the next month. The reason for Excel is because I can use it on my android phone,where I update Miles etc.

If android could run access dbs, then I would have done it in Access, however, with some vba, all this works fine for.monthly sheets of trips and passengers.

[Salty Mariner]

How much time do you have, or are you willing to spend to learn something new like Access? This is something you should ask yourself before diving into this. Also, don't limit yourself. Since you work on the road do you need to work on a mobile device? While I have not used the platform MS PowerApps contain some database capability, with the ability to work on mobile devices and are "supposed" to be easy to develop. I can't vouch for the "easy" part just repeating marketing speak. Consider broadening your search and figure out how much time and effort you have to devote to this project. There is no one size fits all approach.

[ArviLaanemets]

I would also say Access, but that takes a different way of thinking, if you are used to Excel.

I have created Excel workbooks in the past using dB principles as much as I could.
You could have one sheet for locations and a new sheet for each year.
I have something similar for my community work.
I have a monthly sheet that holds passengers and destinations, and a master workbook, where every monthly workbook is added to. Then my monthly sheet is cleared for the next month.

Maybe all this is needed when you use your Excel DB from phone, but generally:
Never put same kind of information on separate sheets;
Never put same kind of information into separate workbook, unless you want several users to enter user-specific data simultaneously (except the case where you want to keep historical data when you delete them from main database).

So when OP goes wit Excel, then he needs:
A sheet with table, where general information of all events is registered , like EventName, [EventLocation], ... (EventLocation is in this table, when same event is allways in same location);
A sheet with table, where event occurrences are registered, like EventName, EventStartDate, [EventEndDate], [EventLocation], ..., EventID (EventLocation is entered into this table, when at least one event happens in different locations, otherwise it is read from Events table. EventID is a calculated field, e.g. in format "EventName: EventStartDate");
As sheet with table, where all locations are registered;
A sheet with table, where all contacts at specific event at specific time are registered, like EventID, ContactName, ContactAddress, ContactMail, ..., [EventName], [EventStartDate] (any detailed info about event is read from event occurrences table);
A sheet with table, where all visitations are registered, like EventID, VisitDate, VisitComment, ...
etc.

All tables are either Defined Tables, or they have autofilter activated.
OP can design any number of reports sheets, where he can determine some report conditions (like year/month, EventName, whatever). The info matching for select conditions is returned from proper data entry sheets in any format OP has designed.

[NoellaG]

I think the most important question is : how do you want to work? I can imagine that most data would be entered/changed on location and you don't want to take your laptop everywhere you go. If that is the case, I would go for a web-based application which rules Access out. Excel can be used as a database, but large Excel files with a lot of sheets aren't that easy to work with and keep up to date. As said in message #4 there are a lot of existing web based apps that keeps track of that kind of information.

[hollies]

Good morning everyone.

Many thanks for all of the replies.

I've been fully retired for 12 years, so this is for leisure activities, ie Caravan Rallies. I am in a Caravanning group and I now organize the 20 plus events each year around the UK and Europe.

Having done 108 events since 2019, i want a record of where we have been and when but going forward, i also want the contact details for each venue and location for organising future events.

Some very good food for thought in the replies, particularly with using Excel which I am far more familiar with than Access.

Although i know a small amount about Access, it would be very time consuming and a sharp learning curve to get a DB somewhere like I would need.

Thanks again

Rob

[mike60smart]

Hi Rob
Are you able to upload a sample copy of your current Excel file without any confidential data?

[hollies]

Hi Rob
Are you able to upload a sample copy of your current Excel file without any confidential data?

Here you go. i hope it works ok. Please let me know if not.

It is my first rough attempt at it so it's not quite right yet.

i also need to input the details of each Venue, address, telephone, email, contact name etc.

Many thanks

Rob

[madpiet]

I'd use PowerQuery to basically append each set of {Marshals, Site, Nights, End, Start} so you get one table with those columns and lots of rows. Then eliminate all the blanks. Except for the selection of the repeating sets, this is all super easy pointy-clicky stuff. Then import that into Access. (I'm wondering if Goodly has a good tutorial on this... I'd bet he does. Might require converting the multiple column groups into lists, appending all the lists to one another, removing the rows that don't belong etc etc)

[Welshgasman]

Maybe all this is needed when you use your Excel DB from phone, but generally:
Never put same kind of information on separate sheets; I am emulating a table, so look up relevant info to bring to the main sheet
Never put same kind of information into separate workbook, unless you want several users to enter user-specific data simultaneously (except the case where you want to keep historical data when you delete them from main database) I do not want a huge excel workbook and have to traverse to the bottom all the time. The monthly sheet might hold 50 rows of trips. The master is now on 1498 rows. I am the only person using this. It just makes my life easier for completing the monthly sheet for all the trips, mile and amounts.

[madpiet]

I'd use PowerQuery to basically append each set of {Marshals, Site, Nights, End, Start} so you get one table with those columns and lots of rows. Then eliminate all the blanks. Except for the selection of the repeating sets, this is all super easy pointy-clicky stuff. Then import that into Access. (I'm wondering if Goodly has a good tutorial on this... I'd bet he does. Might require converting the multiple column groups into lists, appending all the lists to one another, removing the rows that don't belong etc etc)

I do not want a huge excel workbook and have to traverse to the bottom all the time.

This is a problem because what you basically have is nested tables (one table per year) with what would be repeated groups of {Marshals, Site, Nights, End, Start}, which will make summarizing this a nightmare. You can derive the month and year from the {End, Start} columns, and then this becomes a really simple question -- so simple SQL statement.
If your table in Excel has like 5-6 columns above, this is a super simple problem to solve.

[Welshgasman]

@madpiet
The comment in bold was mine?

[madpiet]

Honestly, I'm not sure. I was looking at the Excel file, and it has a bunch of repeating groups. I could combine it using queries in Access, but it would depend a lot on how wide the table gets. (Would rather do it in PowerQuery)

[hollies]

Many thanks for your comments everyone.

I don't understand all of them, but I'm steadily going through them.

Thanks again

Rob