Specific Help with First Ever Database

[faodavid]

Hi
I am building my first ever database and wondered if anyonecould help me with some specific questions and / or suggest features that Ishould explore and learn about to speed up, automate and get the best resultsfor- I can then try to find videotutorials of these features and teach myself.
As background it is for a music agency which sources venuesfor artists.
Initially I think I may need two tables:
1.Venue Table that includes location, contact,venue cost, capacity and facility info data
2.Artists Table which will track shows at venuesthroughout the country by different artists this would contain date, venue,location, sales and artist name
As I deal with some tribute artists I need to know whichother similar tribute artists have played which venues on which dates so as toleave sufficient gap before placing my tribute artist in a venue or avoidcertain towns /counties?
I believe that I will need to create a relationship betweenthe two tables?
Initial questions:
On Venue Tablewhat is the quickest way of data entry, some of the data will be the same ie


Location is broken down into Venue, postcode, address, town,county and region. So as an example if I have a venue in a town where I have aprevious record of which county and region this relates to how do I autocomplete for example Town is Burnley, county is Lancashire, region is NorthWest?
Same would apply to artist table I know the venue name – is therea way to save time completing town, county and region, there are odd caseswhere there are more than one venue of the same name?
Process / Queries I will need
1.Find shows by artist X between 2011 and 2015show me venue, town, county, region and date of show (how can this info bepulled from websites into a table to save manual input)?
2.Find me those towns / counties which have nothad a show by the above artist in the last 18 months
3.On achieiving Pt 2- As I have only just startedto enter data on venues, there are about 20 fields to complete – when I setcriteria ie show me venues over 500 capacity with minimum stage size of X x Y –when a lot of my data is missing how do I create a query to include blank infountil my database is fully complete?
4.On achieving Pt 2 is it possible that I can massemail these venues directly from Access to seek interest and availability?
5.Is it possible to take sales info from my artisttable and use this with info from my venue table ie show all the towns thathave not been played with capacities of 500 but show the top 10 selling venues forsimilar genre artists of those available?
6.Is it possible to export chosen venues fromAccess onto a map – Ive been looking at Excel Powermap and Mappoint but I’mstill currently learning?
Any help would be really appreciated as I need to startprioritising tasks and learn key features so I can start to use the databaseasap
Thanks in advance
Dave

[amrut]

Split repetitive data into pieces like Venues, Artists. Those will be your tables to hold the relevant details. Then use a junction table which will have data about the shows conducted/planned. It will have the foreign keys from tblVenues and tblArtists and the show date. If there are more than one artist involved in a show, you need another table. This is a most basic data structure that comes to my mind. Once this is set up, then explore the other things you need like mapping, pulling data from website etc.
See section for Events at http://www.databaseanswers.org/data_models/

[faodavid]

Thanks Amrut, Do you suggest having a table per artist, I will probably need to run a query which says find shows by artist A, B & C between this date and that - show me venue, town, county etc?
Dave

[amrut]

No, a query can retrieve data by A,B, between certain dates from a table.

[faodavid]

Hi Amrut
I must admit its pretty daunting as Ive never done anything like this before, here is what Ive done..

Created a Junction Table and inputted ArtistVenue ID with autonumber
Venue ID with a number
Artist ID with a number
Created relationship One to many from Artist Table (Artist ID) to Artist ID in the junction Table
and a One to many Relationship from Venue Table using Venue ID to Venue ID in the junction table

What confuses me is that I don't really know what I have done:
There are potentially several thousand venues and several hundred artists.

I have two sets of Data - approx 650 entries in the Artist Table which contains Artist ID, ShowDate, Region, County, Town, Venue, Postcode, Capacity, Brochure and Artist name
The entries for these say Artist A at Venue B with relevant town, country, region and on date X - as an artist can have played the same venue twice there are repeats in this table. I would like to copy the venue info from this table and put it in the venue table which currently only has 20 or so entries so as to populate it quickly but the venue table shouldn't have any venue duplicates in it. Are you able to advise on the above
Thanks
Dave

[recyan]

Till someone comes along, a couple of questions :
1) Is Postcode unique for a Venue or Can a single PostCode have more than one Venue ?
2) Is there a possibility, that 2 Artistes can perform on the same day at the same Venue in the same Program?
3) Is there a possibility, that 2 Artistes can perform on the same day at the same Venue at different times in different Programs?

Thanks

[faodavid]

Hi thanks for your post.
1)I think Postcodes are unique to the street of the venue so unlikely for two venues to share the same postcode
2&3) Shows contain headliner performers and support performers but I am only concerned with headline performers so only one headliner can perform on the same date at the same venue

Hope this helps

[recyan]

At a basic level, perhaps you should have something like below :
-------------------
tblArtists
ArtistID - PK
ArtistLastName
ArtistFirstName
ArtistEmailID
ArtistContactNo,
....
-------------------

tblVenues ( At a bit complicated level )
VenueID - PK
VenueName
Postcode - FK
Capacity
Brochure

tblPostCodes
Postcode - PK
Region
County
Town

I usually complicate things further with a Master table for Regions, Another for County & another for Town & then get them together in another table.

or

tblVenues ( At a simple level )
VenueID - PK
VenueName
Capacity
Brochure
Postcode
Region
County
Town
-------------------------------
tblPrograms
ProgramID - PK
ArtistID - FK
VenueID - FK
ProgramDate - Date Field

Note : Do not worry about importing from the Excel at this stage ( With 650 records, it would not be that difficult ). Get your structure right with the help of others here & then proceed.

Thanks

[amrut]

I have two sets of Data - approx 650 entries in the Artist Table which contains Artist ID, ShowDate, Region, County, Town, Venue, Postcode, Capacity, Brochure and Artist name

This table looks like junction table I have mentioned. Artist ID, showDate are OK but you need to sort out the Venues in this list/table and remove other fields so that this table becomes your junction table. Make sure you back up your data before proceeeding to add/delete/modify fields/tables. If this table has all the show dates, artists and venues (with consistent names), you can use a query using DISTINCT to pull the Artists and Venues from this table and populate the Artists and Venues table for once. recyan has given the table structures too.

[faodavid]

Hi Amrut
Thanks for your post - here is a screen grab of what the junction table. When you say sort out venues in this table and remove other fields can you explain further? I have backed up data. Could you explain "If this table has all the show dates, artists and venues (with consistent names), you can use a query using DISTINCT to pull the Artists and Venues from this table and populate the Artists and Venues table for once"Sorry to ask the basics but Im brand new to this

Dave

[amrut]

I have two sets of Data - approx 650 entries in the Artist Table which contains Artist ID, ShowDate, Region, County, Town, Venue, Postcode, Capacity, Brochure and Artist name

I was referring to above data you have to pull the required artists and venues. you can create a query like shown here http://www.techonthenet.com/sql/distinct.php and use it to create another APPEND query (http://www.techonthenet.com/sql/insert.php) to populate the artists and venues from above data set. The show date should be in the junction table and not the artists' table.