473,699 Members | 2,801 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

queries between multiple tables

I'm relatively new when it comes to access but I think I have a grasp
on the basics. I have a very specific problem that requires a little
help from someone more experienced.

I'm trying to help someone in the field of consulting create a more
comprehensive database for their data they collected from surveys.
Here's the background:

Three surveys are sent a year, each with similar questions, but the
surveys are continually revised (new questions are added, and old
questions that haven't been helpful are taken out)

I have a table that holds all the questions (an ID #, the question,
the 5 multiple choice answers, the number (1-5) of the correct answer,
and a 'section' column that marks the topic the question aims at
asking)

My friend would also like to store the test statistics that he
generates for each survey he hands out. So I'm planning to set up one
table for every survey he sent out (which amounts to about 20 tables
so far). The leftmost column holds the question's ID number, and the
other columns hold several significant statistics - the percentage of
respondents who got the question right, e.g. Note: I don't understand
all the statistics behind this, I'm just trying to set up the database
so it will be easier for him while he expands it.

So I have two questions at this point:

1) is there a more efficient way to store the data of each survey,
rather than just making a new table? The reason I did that is because
each survey is slightly different, and i can't conceive of making one
table that can efficiently store all of it. However, if anybody has a
better way, I would be willing to hear it.

2) when i run a query, I want to be able to track each question's
statistics over time. So, when i run the query i use the criteria row
to zero in on one question (by typing in the question's ID). Now my
problem is that since I have 20 tables for each survey, there is no
easy way I know of doing this. What I want is this:

I want to plug in a specific question's ID number and have access pull
up a table that looks something like this:

Survey / P value / R value ....

(date) (double) (double)
1/1/02 .82 .31
6/1/02 .59 .29
1/1/03 .73 .25
....

Sorry if this is all confusing. The basis of the problem is that there
are 3 variables (question ID #, date of the survey, and the statistics
of the question) while there is only a 2 variables that can be
represented in a table (to my knowledge).

Thanks
Jul 3 '08 #1
2 1218
On 3 Jul, 20:49, alex.william... @gmail.com wrote:
I'm relatively new when it comes to access but I think I have a grasp
on the basics. I have a very specific problem that requires a little
help from someone more experienced.

I'm trying to help someone in the field of consulting create a more
comprehensive database for their data they collected from surveys.
Here's the background:

Three surveys are sent a year, each with similar questions, but the
surveys are continually revised (new questions are added, and old
questions that haven't been helpful are taken out)

I have a table that holds all the questions (an ID #, the question,
the 5 multiple choice answers, the number (1-5) of the correct answer,
and a 'section' column that marks the topic the question aims at
asking)

My friend would also like to store the test statistics that he
generates for each survey he hands out. So I'm planning to set up one
table for every survey he sent out (which amounts to about 20 tables
so far). The leftmost column holds the question's ID number, and the
other columns hold several significant statistics - the percentage of
respondents who got the question right, e.g. Note: I don't understand
all the statistics behind this, I'm just trying to set up the database
so it will be easier for him while he expands it.

So I have two questions at this point:

1) is there a more efficient way to store the data of each survey,
rather than just making a new table? The reason I did that is because
each survey is slightly different, and i can't conceive of making one
table that can efficiently store all of it. However, if anybody has a
better way, I would be willing to hear it.

2) when i run a query, I want to be able to track each question's
statistics over time. So, when i run the query i use the criteria row
to zero in on one question (by typing in the question's ID). Now my
problem is that since I have 20 tables for each survey, there is no
easy way I know of doing this. What I want is this:

I want to plug in a specific question's ID number and have access pull
up a table that looks something like this:

Survey * * / * *P value * / * R value ....

(date) * * * * (double) * * * * (double)
1/1/02 * * * * .82 * * * * * * * * .31
6/1/02 * * * * .59 * * * * * * * * .29
1/1/03 * * * * .73 * * * * * * * * .25
...

Sorry if this is all confusing. The basis of the problem is that there
are 3 variables (question ID #, date of the survey, and the statistics
of the question) while there is only a 2 variables that can be
represented in a table (to my knowledge).

Thanks
For the first part try three tables!

table 1 = tblSurvey (surveyID(Prima ryKey), SurveyDate, Survey_details
etc)

table 2 = tblQuestions (QuestionID(Pri maryKey), SurveyID(Foreig nKey),
Question, AnswerGiven, AnswerExpected etc)

table 3 = tblStats (StatID(PK), QuestionID(FK), StatisticType,
StatisticValue) - each row has ony 1 statistic in it
This way you can store all questions associated with Surveys
efficiently
Jul 4 '08 #2
On Jul 3, 8:15*pm, purpleflash <k...@bgs.ac.uk wrote:
On 3 Jul, 20:49, alex.william... @gmail.com wrote:
I'm relatively new when it comes to access but I think I have a grasp
on the basics. I have a very specific problem that requires a little
help from someone more experienced.
I'm trying to help someone in the field of consulting create a more
comprehensive database for their data they collected from surveys.
Here's the background:
Three surveys are sent a year, each with similar questions, but the
surveys are continually revised (new questions are added, and old
questions that haven't been helpful are taken out)
I have a table that holds all the questions (an ID #, the question,
the 5 multiple choice answers, the number (1-5) of the correct answer,
and a 'section' column that marks the topic the question aims at
asking)
My friend would also like to store the test statistics that he
generates for each survey he hands out. So I'm planning to set up one
table for every survey he sent out (which amounts to about 20 tables
so far). The leftmost column holds the question's ID number, and the
other columns hold several significant statistics - the percentage of
respondents who got the question right, e.g. Note: I don't understand
all the statistics behind this, I'm just trying to set up the database
so it will be easier for him while he expands it.
So I have two questions at this point:
1) is there a more efficient way to store the data of each survey,
rather than just making a new table? The reason I did that is because
each survey is slightly different, and i can't conceive of making one
table that can efficiently store all of it. However, if anybody has a
better way, I would be willing to hear it.
2) when i run a query, I want to be able to track each question's
statistics over time. So, when i run the query i use the criteria row
to zero in on one question (by typing in the question's ID). Now my
problem is that since I have 20 tables for each survey, there is no
easy way I know of doing this. What I want is this:
I want to plug in a specific question's ID number and have access pull
up a table that looks something like this:
Survey * * / * *P value * / * R value ....
(date) * * * * (double) * * * * (double)
1/1/02 * * * * .82 * * * * * * * * .31
6/1/02 * * * * .59 * * * * * * * * .29
1/1/03 * * * * .73 * * * * * * * * .25
...
Sorry if this is all confusing. The basis of the problem is that there
are 3 variables (question ID #, date of the survey, and the statistics
of the question) while there is only a 2 variables that can be
represented in a table (to my knowledge).
Thanks

For the first part try three tables!

table 1 = tblSurvey (surveyID(Prima ryKey), SurveyDate, Survey_details
etc)

table 2 = tblQuestions (QuestionID(Pri maryKey), SurveyID(Foreig nKey),
Question, AnswerGiven, AnswerExpected etc)

table 3 = tblStats (StatID(PK), QuestionID(FK), StatisticType,
StatisticValue) - each row has ony 1 statistic in it

This way you can store all questions associated with Surveys
efficiently

Thank you thats a very good way to do it!
Jul 4 '08 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

8
15231
by: Mike N. | last post by:
Hello: I am new to T-SQL programing, and relativly new to SQL statements in general, although I have a good understanding of database theory. I'm a little confused as to the fundamental differences between a view and a query, or rather, when it is more appropriate to use one vs. the other. It seems to me that most select queries can be implemented as views, and I can't see the downside to doing so.
1
3513
by: Andreas Lauffer | last post by:
I changed from Access97 to AccessXP and I have immense performance problems. Details: - Access XP MDB with Jet 4.0 ( no ADP-Project ) - Linked Tables to SQL-Server 2000 over ODBC I used the SQL Profile to watch the T-SQL-Command which Access ( who creates the commands?) creates and noticed:
3
2328
by: NeilAnderson | last post by:
I'm a fairly new user of access & I've never had any training, so I'm wondering if I'm doing the right thing here, or if it matter at all. I'm building a database for room booking purposes and I'm trying to do all record changing using queries. From what I can glean from this (excellent!) group this is the right way to go about it. At present what I'm doing is building & trouble-shooting the queries with query-builder then using code to...
3
1308
by: Kenneth | last post by:
I have 3 different tables that I want Queries to sum up, but I do not get all the information into my Queriy-file. The 1st table have 9 different products. The 2nd table have 3 of those 9 products. The 3rd table have 3 of those 9 products, one of those 3 products is similar to one of those products in table 2. My problem is that I do not get all the information into the Queries. I just get 1 product, which is represented in all tables.
5
2164
by: Martin Lacoste | last post by:
There's likely not a simple answer for this, I know, but, I thought I'd try anyways... Background.. I've been racking my brain with some queries that I thought were straightforward, but have been giving me errors, etc.. and am wondering if I shouldn't be looking more towards using VBA. I have used it in the past to do more complex stuff, but I would consider myself just a little above a newbie in that department, since I seem to have...
4
3751
by: Dave Edwards | last post by:
I understand that I can fill a datagrid with multiple queries, but I cannot figure out how to fill a dataset with the same query but run against multiple SQL servers, the query , table structure and username, password etc will be exactly the same for each server, the only thing that will change is the server name. Idealy I would like to get the server names from a seperate dataset so there could be any number of servers, allthough in...
7
2720
by: Daz | last post by:
Hi. I am trying to select data from two separate MySQL tables, where I cannot use join, but when I put the two select queries into a single query, I get an error telling me to check my syntax. Both of the queries work fine when I use them to query the MySQL server directly. My guess is that the MySQL extension only expects a single resource back from the database, but get's several, or that it just checks the statement first, and decides...
8
3183
by: beretta819 | last post by:
Ok, so I apologize in advance for the wordiness of what follows... (I am not looking for someone to make this for me, but to point me in the right direction for the steps I need to take.) I was nominated at work to design a "system" that would allow us to track the performance of our employees. So I thought Access. Should be simple enough because I have made simple DB's with it before. I was wrong. This has turned into a bigger deal than I...
8
6101
by: Sheldon | last post by:
I just received a 2nd (configured as a dual) monitor but, for Access only, I can't seem to figure out, if it's possible, to view, say, Table1 on one monitor and Table2 on the other monitor. If I open Table1, then Table2, Table2 apparently sits directly over Table1 and seems anchored so that I cannot move it "out of the way". Is this bahavior a restriction with Access as I do not have this problem with Excel or Word? Sheldon Potolsky
2
5572
by: jaffar.kazi | last post by:
Hi All. This might be the wrong group to post this query, since it is ADO.NET related, but I couldn't find any ADO.NET group. My problem is that I want to write some queries, which use temp table because of their complexity. My final results are in the temp tables. How can I return a single result set from these multiple queries? Regards,
0
8686
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9033
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
8911
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8882
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
4375
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4627
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3057
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2345
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2009
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.