[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 283
  • Last Modified:

Use of scenarios

First of all I am a new user/novice of MS Access.

I am building an Access 2010 database that will hold the input data for different scenarios.  I will be using another program that reads the data from the database. but I need to ensure it only reads data for a specified scenario.

I am considering how best to achive this and would appreciate advice.

Should I set up a backend database that holds the data from all scenarios and a frontend database that retrieves the data for a scenario based on a scenario ID within the backend database?

Is this a good approach or would you suggest another approach?

Many thanks in advance
Alison
0
alisonthom
Asked:
alisonthom
  • 3
  • 3
  • 2
2 Solutions
 
Arthur_WoodCommented:
>>I will be using another program that reads the data from the database

Is that other 'program' another instance of  Access?  If not, then you do not need to create two Access databases.  One, which holds the data, will be sufficient.  

What does the data for a 'scenario' consist of?

AW
0
 
alisonthomAuthor Commented:
Hi, thank you for the response.

The other program is not another instance of Access.

The database will hold data for different clients, the data for each client being referred to as a scenario.  So my need is to retrieve the data for a given client.  The tables would have over 50 fields and contain a variable number of records (that could be in the range 20 - 100).

Thanks
Alison
0
 
Arthur_WoodCommented:
As long as You have the Scenario ID is each record, you can retrieve all of the reocrds for a single scenario ID with a single SQL statement.  YOu only need a single database to accomplish this.

I might suggest that you may want to take a look at the design of you table, as 50 columns in a single table seems a bit high (but not unheard of).  For instance, if you have repeating data in each record for a single scanrio (such as Client Name, or address, etc.), then you might want to split that out into a separate table, which you can then link to the main table.

AW
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
alisonthomAuthor Commented:
Hi, thank you for the follow-up.

I am very new to this, but thanks to your your advice I have been able to create a query that uses the scenario ID to determine which records to display.

The 50 fields are included in approx 10 tables, which does make it more manageable.  This does lead on to another question.  The SQL statement I have constructed has a hard coded value for a scenario e.g. "4".  Could you suggest how I could make this automated that avoids having to enter a scenario in the SQL statement?  Could I have a table with records for the scenario IDs, each record having a Boolean field to indicate if it should be used?

Many thanks
Alison
0
 
Richard DanekeCommented:
When you use a field that is not defined in SQL (like [enter scenario], Access will prompt for the value.    There are other ways to do this, but this creates what is known as a parameter query.   When you run the query, a parmeter box will open, display the field name ('enter scenario') that is does not understand.   You type the value you want (4) and the query continues to retrieve the appropriate records.

The boolean field you suggest can be used, but queries can select records based on boolean values, matching values, and range values.
0
 
alisonthomAuthor Commented:
Many thanks to both of you.  You have been very helpful indeed!

Alison
0
 
Arthur_WoodCommented:
Glad to be of assistance

AW
0
 
Richard DanekeCommented:
Thank you for the comments.  

DD
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now