Hi all
I have a very large amount of data in an Excel spreadsheet (dummy version attached) that I am hoping to import into access. It relates to the provision of meeting facilities by external organisations. The data is arranged in an unusual way as it has been exported into Excel from an archaic in house database system.
In the spreadsheet there are 4 columns. The first is an Event Identifier (6 digit code), the second is the meeting venue, the third column records which facilities were used for each event (i.e. room hire, Equipment etc). The 4th column is blank and is to record a price for each event. This cost calculation is being done manually at the moment based on existing prices that each different venue charges for equipment and room hire. I want to import the data into Access and hopefully allow Access to make the cost calculation for me.
I have been able to import the data into Access and append the venue column into a venue table (by asking it to select unique records). I have also appended an Event ID table, a Facilities table and an events table which basically replicates what is in the spreadsheet.
What I have in mind is a separate table (called say VenuePrice) which records a room hire and facility price for each venue. It would look something like this:
VenuePriceID- PK (autonumber)
VenueID- FK to the Venue table
FacilityID – FK to the Facility table
Price- (currency- to be entered manually by the end user)
The end user could then fill out the details in the prices table.
What I cant figure out is how to query the data so that Access will look at the combination of venueID and FacilityID in the ‘Events’ table and retrieve the correct price from the VenuePrice table.
I know I can use cascading comboboxes to do this for entering future events. But I am not sure if it possible for Access to perform this calculation using existing data in a table?
Many thanks and apologies for the long explanation.
I have a very large amount of data in an Excel spreadsheet (dummy version attached) that I am hoping to import into access. It relates to the provision of meeting facilities by external organisations. The data is arranged in an unusual way as it has been exported into Excel from an archaic in house database system.
In the spreadsheet there are 4 columns. The first is an Event Identifier (6 digit code), the second is the meeting venue, the third column records which facilities were used for each event (i.e. room hire, Equipment etc). The 4th column is blank and is to record a price for each event. This cost calculation is being done manually at the moment based on existing prices that each different venue charges for equipment and room hire. I want to import the data into Access and hopefully allow Access to make the cost calculation for me.
I have been able to import the data into Access and append the venue column into a venue table (by asking it to select unique records). I have also appended an Event ID table, a Facilities table and an events table which basically replicates what is in the spreadsheet.
What I have in mind is a separate table (called say VenuePrice) which records a room hire and facility price for each venue. It would look something like this:
VenuePriceID- PK (autonumber)
VenueID- FK to the Venue table
FacilityID – FK to the Facility table
Price- (currency- to be entered manually by the end user)
The end user could then fill out the details in the prices table.
What I cant figure out is how to query the data so that Access will look at the combination of venueID and FacilityID in the ‘Events’ table and retrieve the correct price from the VenuePrice table.
I know I can use cascading comboboxes to do this for entering future events. But I am not sure if it possible for Access to perform this calculation using existing data in a table?
Many thanks and apologies for the long explanation.
