Better using Access or Excel

[hollies]

My question is can I achieve what i want to using Access or would I be better using Excel.



I follow a music band who have toured for many years and I would like to record and analyse every venue they have played at, the dates, the amount of seats sold, costs, profit etc.

So, over the last 10 years, we may have played in one particular venue, 6 times.

I want to be able to find out how many times we have been there, the dates, the sales records etc and the cumulative number of seats sold at that venue over the period.

Many thanks, Rob

[June7]

I vote for Access. This is exactly the sort of data management and analysis Access is designed for.

[JoeM]

I would say probably Access.

It is important to understand the difference between Access and Excel. Access is a relational database program. So if you have a bunch of tables interacting with each other, you have a database (using a lot of VLOOKUP or INDEX/MATCH functions in Excel is often a sign that you really have a relational database structure). While using brute force methods, you can create a database in Excel, it is typically clunky and cumbersome to do so.

If designed properly, a database is much easier to work with in Access. However, the database structure is crucial.
Here is a link that may be helpful:
https://support.office.com/en-us/art...6-d2331f158280

[aytee111]

Access is data storage, Excel is reporting. Do you want to store data for a period of time? Yes!

[hollies]

I would say probably Access.

It is important to understand the difference between Access and Excel. Access is a relational database program. So if you have a bunch of tables interacting with each other, you have a database (using a lot of VLOOKUP or INDEX/MATCH functions in Excel is often a sign that you really have a relational database structure). While using brute force methods, you can create a database in Excel, it is typically clunky and cumbersome to do so.

If designed properly, a database is much easier to work with in Access. However, the database structure is crucial.
Here is a link that may be helpful:
https://support.office.com/en-us/art...6-d2331f158280

Apologies for not responding sooner but I had to go away for two weeks at short notice.

My Excel table has the following 12 columns and in the last few years the band have performed 816 gigs.

Day - Date - Venue - City - Country - Capacity - Sold - Fee - % - Gross - Net - Note


I regularly need to find when we last played at a place and how it performed sales wise, or how many times have we played a particular venue and has it been profitable etc. Is access the right tool or should I be learning Excel a little better. I have never used VLOOKUP or INDEX/MATCH functions so it is all a steep learning curve.

I have imported the table into access and have been playing with it, but, unfortunately, I cannot fathom how to get it to work.

Once I had the table in Access, I ran the Analyse Table feature and let the prog do the work. However it split the table in to three separate tables which just do not make sense to me.

Table 2 had Date, Capacity, Sold, Fee, %, Gross, Net, ID, Note and Lookup to Table 3.

Table 3 had City, Venue, ID, Lookup to Table 4.

Table 4 had Day, Country, ID

The first column in each of the new table has a + sign to drop down list.

Is this set up correct or have I got it totally wrong.

If it is correct, what is my next step to be able to add new entries and to be able to gather the information that I need. I presume it is to build forms and some queries.

Thanks for your help.

Rob

[pbaldy]

Post 5 was moderated, posting to trigger email notification.

[cask2010]

I'm not sure why Access split your data up in that manner. You understand your data and what you want to use it for best, so I recommend separating the data into discrete tables on your own. If you're interested in learning about the general philosophy for separating data into tables I recommend researching "data normalization".

From what I can tell, what you should try to do is this:

1) Create a table named tblVenues. This table will hold only information that pertains to the specific venue (not different gigs the band has played there)
- Venue ID, Venue name, City, Country, Capacity

2) Create a table named tblGigs. This table will hold only information that pertains to a specific gig that the band played in the past.
- Gig ID, Venue ID_FK, Date, Tickets Sold, Fee, (Idk what you meant by %, Net, or Gross in your description).

Some general notes about this: Each table needs to have what is called a Primary Key. This is a column in the table that will serve as an ID number to distinguish each entry into the table (VenueID, GigID are examples I've provided). The next step would be to link the tables together (Notice I included VenueID_FK in tblGigs... that FK stands for foreign key. This is essentially a link to the table tblVenues by way of VenueID. What this allows you to do is be able to compile multiple Gigs at a single Venue without having to repeatedly enter all of the data for that specific venue into your datasheet each time you create a new Gig. You're using a single number 'VenueID' to tell the computer which venue the gig was at, and it can just look up the rest of the information about that venue if you happen to need it.

Unfortunately this is only the start of your journey mate. What you're ultimately trying to do doesn't sound like it would be all that difficult if you had a firm foundation in how relational databases operate, but it seems you have some learning to do on that front. Hopefully this will get your feet wet. I recommend watching some of these videos. The guy is describing how to build a booking system for a hotel, which doesn't sound extremely helpful, but he walks through data types and some of the basics of normalization in the first few videos. Stumbling upon them was an absolute godsend for me (I'm pretty new to Access as well) so I think they'll do you some good. Good luck!

[hollies]

I'm not sure why Access split your data up in that manner. You understand your data and what you want to use it for best, so I recommend separating the data into discrete tables on your own. If you're interested in learning about the general philosophy for separating data into tables I recommend researching "data normalization".

From what I can tell, what you should try to do is this:

1) Create a table named tblVenues. This table will hold only information that pertains to the specific venue (not different gigs the band has played there)
- Venue ID, Venue name, City, Country, Capacity

2) Create a table named tblGigs. This table will hold only information that pertains to a specific gig that the band played in the past.
- Gig ID, Venue ID_FK, Date, Tickets Sold, Fee, (Idk what you meant by %, Net, or Gross in your description).

Some general notes about this: Each table needs to have what is called a Primary Key. This is a column in the table that will serve as an ID number to distinguish each entry into the table (VenueID, GigID are examples I've provided). The next step would be to link the tables together (Notice I included VenueID_FK in tblGigs... that FK stands for foreign key. This is essentially a link to the table tblVenues by way of VenueID. What this allows you to do is be able to compile multiple Gigs at a single Venue without having to repeatedly enter all of the data for that specific venue into your datasheet each time you create a new Gig. You're using a single number 'VenueID' to tell the computer which venue the gig was at, and it can just look up the rest of the information about that venue if you happen to need it.

Unfortunately this is only the start of your journey mate. What you're ultimately trying to do doesn't sound like it would be all that difficult if you had a firm foundation in how relational databases operate, but it seems you have some learning to do on that front. Hopefully this will get your feet wet. I recommend watching some of these videos. The guy is describing how to build a booking system for a hotel, which doesn't sound extremely helpful, but he walks through data types and some of the basics of normalization in the first few videos. Stumbling upon them was an absolute godsend for me (I'm pretty new to Access as well) so I think they'll do you some good. Good luck!

Very many thanks for your comprehensive reply Cask2010. You are right about needing to learn a lot but I have made a start. I'll give your suggestions a go and have a play around. Thanks also for the link. I'll take a look.

Rob

[ssanfu]

"Old Programmer's Rule" is this: If you can't do it on paper then you can't do it in Access. Until you can draw out your tables AND rules for how they change for each function you want to perform, you are going nowhere fast.
===============================================

Work through the tutorials here: http://www.rogersaccesslibrary.com/forum/forum46.html
Actually follow the tutorials - it will help you a lot.


Steps:
-------
Design your tables
Design your queries
Design your forms
Design your reports


Some suggestions:
Use only letters and numbers (exception is the underscore) for object names.
Do not use spaces, punctuation or special characters in object names.
Do not use look up FIELDS, multi-value fields or calculated fields in tables.
Do not begin object names with a number.


Some reading for you:
--------------
Normalization Terms and Concepts http://www.utteraccess.com/wiki/Norm...s_and_Concepts

What is Normalization?
=======================
What Is Normalization, Part I: Why Normalization? http://rogersaccessblog.blogspot.com...on-part-i.html
What Is Normalization, Part II: Break it up. http://rogersaccessblog.blogspot.com...n-part-ii.html
What Is Normalization: Part III: Putting It Back Together http://rogersaccessblog.blogspot.com...-part-iii.html
What is Normalization: Part IV: More Relationships http://rogersaccessblog.blogspot.com...n-part-iv.html
What Is Normalization: Part V: Many-to-Many Relationships http://rogersaccessblog.blogspot.com...on-part-v.html

[ArviLaanemets]

I edited your Excel file.

Added a sheet Main with data from your table. At top of the sheet is statistics table, which calculates various stats for any set of filtered data in main table. This is most flexible way.

Added a sheet Report1. This reads data and statistics from Main sheet. Country, city and venue info is read from topmost row of Main table - so this report is working only, when Country, City and Venue columns eachhave only single value filtered.

Added sheets Countries2, Cities2, Venues2 and Report2. On sheet Main the dropdown to select a combination Country : City : Venue was added (you can move it to Report2 ifit is more convenient for you) - this combination sets conditions for Report2. On sheet Report2 info and statistics for selected venue is displayed.
NB! The formula to calculate LastDate is an array formula.
NB!! Report2 doesn't depend on autofilter on Main sheet.

[ArviLaanemets]

Sorry! I posted previous reply to wrong forum!

[hollies]

Wow, so much useful information from everyone. Many thanks for your help. I have learnt so much information this last week on both Access and Excel.

I am going through trying all the advice given and hope to work out for myself the best way forward.

It's just when you first see so much on the screen and have no idea what it is all about it can be mind boggling.

Thanks again.

Rob