Query Database To Tally Replies From A Questionnaire
Mar 3, 2006
To start: Running Office 20003. I have created a survey with Frontpages wizard. Basically It has a topic and the end user rates that topic on a scale of one to five like so:
1. Work Environment
a. I enjoy my work place. 12345
b. I think my office has a good reputation. 12345
1-5 are radial buttons. It takes the users input and stores it in an access database as im sure you all are aware. Looks kinda like this:
1a 1b 2a etc.
3 5 1
What i would like to do is this: When the users are done submitting i would like to go back and take results. I.e. we had this many users select option 3 for question 1a and we had _ number of users select option 2 for 1a, etc.
The thing is i was wondering if there was a way to create a query for it. I know i could just put =1 or =2 but that would require running 5 queries on each question.... not fun. Anyway, any help would be appreciated.
Hi I have designed a small questionnaire and i want to use Ms Access for data entry and storage. Here is the brief description. ------------------ A.Heading ------------------ 1.Person id 2.date of birth 3.age 4.sex 5.school ----------------------- B.Sports Information about individual ------------------------------ 1.do you play any spot ------yes/no 2.what spot do you play -----1.football 2.tennis 3.other 3.is your father still alive-----yes /no 4.what spot does he play------1.football 2.tennis 3.other 5.at what age did you start school 6.what is your political affiliation 1.liberal 2.centre . other
----------------------------------------------- C.For official use only --------------------------------------------- 1.Date of interview------- 2.How enthusiastic was the respondent----1.very 2.Average 3. Below average
How can i go about designing a relational database for the above questionnaire. I need advice on the relational schema thanks
So I have a simple table with Date, Points Earned, Points Used and Client ID number. Id like to tally the points for each client ID, after each time they either use or earn points. How do I do this?
I found the below link that looks great and works.
[URL] ....
Te only thing is that I don't have much control over it and would like to build something similar as we also need to work with access 2003 (still )Any way to find how the code works (if there is some of it hidden in access)?
I have the task of creating a new questionaire and associated reporting. Normally I would have done this in Excel, but due to the size I feel it is neccessary to create this in Access.
My question is should I revise my table of completed forms? I initially planned to create a form to populate the completed forms table. Job done? However I am sure I have read somewhere this is not the best in terms of reporting and I should have a table of answers for each question? Is this correct I have approx 50 questions?
I have the following tables:
1) Staff Details 2) Table Of Questions 3) Table Of Answers (Questions are multiple choice) 4) Table of completed forms
Wondering if anyone can suggest how I might be able to deal with a questionnaire. I thought about doing a straightforward database with all the questions listed as fields in the one table but I think there are too many questions/fields (about 200, definitely not 254 but still raises an error message of "Too Many Fields Defined). The questionnaire will be an on-going process, with users re-submitting their answers every quarter. I do want to be able to analyze the data and create reports, etc. so am hesitant about using Excel. For instance, I did a basic report involving Excel and then adding many columns to calculate various averages - the report was made in Word and I mail-merged in the data from the spreadsheet. Long-winded for just one report and I'm hoping to avoid this.
I'm building a questionnaire/survey database for my work. Everything seems pretty straightforward, but I'm a little unsure about how to store the survey configurations.
I'm assuming that the survey will change sometime between now and the end of time so I'm tracking survey configurations (which questions and in what order). Right now my "Configuration" table has two fields: ConfigID (Autonumber), Questions (Memo). The 'Questions' field is a space seperated list of question IDs. The other way I could've done it is to use three fields: ConfigID (Long), OrderNum (Int), QuestionID (Long).
I figure the first way is more memory efficient in the long run and it will take much less time to add a new configuration.
The way I'm doing things now works for me, but I'd like to know if anyone thinks I'm setting myself up for trouble down the road or if my way is actually less memory efficient (I'm not as concerned with processing time).
I'm trying to set up a simple query that links four tables. However, the tables are extremely large, all in excess of 1.5GB each so I had to split the tables up into four separate DBs. I've tried the following with no success:
1) Link the 4 tables in the DB which contains my primary key. This quickly inflates increases the file size above 2GB and won't let me go any further.
2) Build a remote query to connect the four tables. This looked promising until I tried to run the query and it became evident that it only knows to point to the last database source that you specified.
I'm running everything locally on my C drive. The data source are simple text files (1.6 million rows) from the FDA website.
i have a database that runs updates from within itself.what i need is, this database to then open a another database run a update query, then close it.
I'm trying to run a UNION query that joins five queries through a MS WorkSpace into a DAO.recordset in VB. I'm pulling the data from a SQL Server Database through VB in Access. I'm attempting to open a recordset with a query passed to it as a string. The query is below. For some reason, I'm receiving a message: "MS Jet database engine cannot find the input table or query. Runtime Error 3078".
Here's what's puzzling. When I run a single query without any UNION statement, the code finds the table and runs fine without error, but anytime I join two or more queries with a UNION statement in the VB, it gives me the error.
I've executed the same UNION query in both Access Query Builder and SQL Server's Query Analyzer and they work fine in both environments. It's only when I call the query from a DAO.Recodset with VB that it causes this problem. The following is a sample of the UNION query joining two of the five queries. Does anyone have any idea what could be the problem? The following query executes in about 5 seconds so I don't think there's a "time-out" issue. I'm thinking that the UNION statement may be the culprit. Maybe there's another way to approach joining these separate queries? Any help would be most appreciated. Thanks.
SELECT SalespersonID, Sum([SlsPrice]-[RtnPrice]-[SlsDiscnt]+[RtnDiscnt]) AS fldPrice FROM MyTable WHERE (((Source)='d') AND ((DistrictID)='01') AND ((CategoryID) = 'HCPROD') AND ((BrandID)<>'CSS')) AND (((BrandID)<>'1356')) AND (((BrandID)<>'1400')) AND (((BrandID)<>'1551')) AND (((BrandID)<>'555')) AND (((BrandID)<>'66')) AND (TransDate >= 07/01/2005) AND (TransDate <= 07/31/2005) GROUP BY SalespersonID UNION SELECT SalespersonID, Sum([SlsPrice]-[RtnPrice]-[SlsDiscnt]+[RtnDiscnt]) AS fldPrice FROM MyTable WHERE (((Source)='d') AND ((DistrictID)='01') AND ((ProductID) = '0029800')) AND (TransDate >= 07/01/2005) AND (TransDate <= 07/31/2005) GROUP BY SalespersonID
Set wrkJet = CreateWorkspace("", "pw", "", dbUseJet) Set db = wrkJet.OpenDatabase("DW", _ dbDriverNoPrompt, True, _ "ODBC;DATABASE=DW;DSN=DW2") 'Set rs1 = db.OpenRecordset(strSQL)
I have 10 databases . ALL identical and used for different vendors. All have same table structure ( invoice, price, etc , etc tables) I want to run a query where I type in the invoice number and it should query all the database and display the appropriate information that I need.
Would appreciate any ideas or suggestions. Thanks
ALSO databases are all on one server.I would need to connect using ODBC
I'm trying to create my own database from queries. the queries retreive data from and ODBC data source. How do I create table from query but I don't want any old data from table to be deleted whenever there's a change of data in ODBC data source. I just want to keep pushing in new information without deleting the old ones, even though those old information have been deleted from the ODBC source.
I am currently using a large Access 2002 database in order to generate various reports.
My two main tables are despatches and returns from which they hold around 1,200,000 records and 100,000 records respectively.
The problem I have is that the reports use various expressions within various queries to generate a single result (percentages per channel etc.) This is obviously very time consuming and it may take up to around 10 to 15 minutes to get a result from a chain of around 5 queries.
Can anyone suggest alternative methods to generate similar results in quicker time? (Please note that the tables can not be downsized and records can not be archived)
I've got a form that has drop down menus to select info from a particular table for search criteria in Access 2003. If I select a certain set of data I get results in from query. If I happen to leave one of the drop down menus blank than I get no results, when I know there are results availabe. What type of language do I need to put in the criteria part of the query to allow a blank search of one or more of my drop down menus? Say I have 10 drop down menu criteria, and I want to use only 3 of them as criteria for a particular search, so I select the appropriate items and leave the rest blank. But using the same form, be able to do a different search using any combination of the drop down menus with a specific response or blank to search every respoonse in that field.
That way I can have one form to do multiple types of queries depending on the information you select in the drop down menus. And if the menu is blank, the query assumes any entry in that column is open to results.
Any ideas? I am a newbie to Access, so I'm probably just missing something simple, or I'm trying to do something far more technical then I ever thought it would be.
I have a database with all of my contacts... I have a report that shows my contacts in alphabetical order and I have a form for adding new contacts. I was wondering if there is a way to have a report or query that will look up a contact by typing in the person or the company name.. Is this possible and if so.. how do I do this?
I am building a simple assets database to help me with my work. This is what I did:
I created a table with following columns:
1) Bill ID - unique number of the asset bill, created by the accounting software.
2) ID of the supplier
3) Month
4) Type of the bill (code such as RI for investments, RO for activated assets etc.)
5) Date
6) Basic non-taxed value.
There is a 20% tax on assets. 19% OF the 20% tax is added to the base non-taxed value - I get the starting value of the asset before amortisation begins.
So far so good, my query works perfect, does everything right, however:
Every now and then (less than 10 times per year) I get assets from suppliers that are free of tax. That means that the value on the bill = starting asset value, and there is no need for adding the 19% of the tax back to the asset value. I don't know how to exclude those bills from the process.
The way I figured it theoreticaly, it can be done like this:
1) I could store the tax-free supplier codes in a separate table and make the query perform a check against the column 2 (supplier ID), and if it findds the supplier there, the base value is taken as a final value.
2) I could make all non-taxed bills have an unique tag for the non-taxed bills(column 4 - type of the bill), and make the query check against those.
I have no clue how to make any of those, so I hope someone here could help me.
Hi; i designed a database it is controlling the stock and the test results but there is a problem when i was using the query because i entered the table data like "1111 Microsoft Access" "2222 Microsoft Excel" when i executed the query i must enter the exact data like "1111 Microsoft Access" but i wanna only use "1111" for executing the query.
Hello, Ok this may seem simple, but I can't think of an "easy" way to do it.
How can I query all tables in a database (that are not related in any way, and shouldn't be... for a reason) to find a specific string.
The example is, the db contains tables of pc's at multiple schools, sort of a primitive inventory. Each school has its own table at this point, because we may eventually give each school its own db in the future (and once we clean this one up a little!). If I am looking for a pc's service tag, but don't know what school's table it's in, I have to open and individually do a "find" in every table. Is there a way to simply query all tables in a db without the tables being related? Is this a union query? If so, is there a limit to how many tables can be part of that?
is there anyway to view records in an access database from an existing database? i have 3 databases that perform basically the same things, but are for different people... i would like to create a database that can report all this information in one spot, instead of creating reports in every database. if this is not possible, i'll probably go the asp.net route, but this seems to be an easier way, if it is possible what do u think? *j
but im having some problems with the search on asp. the problem as u can see is the fact it has to be the exact item for the search to work, atm the query is
Select * From PLIST Where PLIST.DESC Like "::ID::"
but i need it to work on single words from teh database rather then the whole thing, anyone got any ideas if i can change that so it will pick up on words rather then the whole item?
Amiga'Ferrari Formula One'
use that if you need to find an example of data, its got all the stock records of the shop dating back for years :P
I have an Access Database that queries TagNames from an SQL DB(dbo) and puts them in a new access table(Consolidate). The query adds a field(Time) which looks at the EventTime of the item in the SQL Database. As of now the query is a 12 hour query finding items that occurred in the SQL DB from the previous 12 hours, ie (Criteria: >Dateadd("h", -12, Now()).
I am trying to change from a 12 hour history to a dayshift and night shift query. This would be 6am-6pm and 6pm-6am. So, I am creating another field(Expr1) where, Expr1: IIf((DatePart("h",Now())>=0) And (DatePart("h",Now())<=6)DateAdd("d",-1,Now()),Day(Now())). The criteria is: >=TimeSerial(6,0,0) And <=TimeSerial(18,0,0) This query should cover the dayshifts history when the nightshift runs the query. If the system time is greater than midnight and less than 6am use the previous days criteria, else use todays criteria.
Is is possible to copy queries, reports, modules, etc. from one database to another, and if so, how? I have some queries and reports in an inventory database I'd like to copy and use in another database without having to set them up or re-code them again.
why is it that as soon as you fix one problem another one needs dealing with :mad:
OK, the problem i have is that i have a BE/FE configuration database in a multiuser environment. I have built all the tables and the relationships in the BE configuration. In the FE configurations i have built the main forms to input data. It has no come to the point where i have begun to create the queries to allow reporting and data searching. And this is where i have a problem. I am trying to create a simply query and i do mean simple (at the moment i simply want 2 fields from 1 table and 2 fields from another table) no calculations or anything i just want it to display the data. I have tried creating this query numerous times and i keep getting the same error.
"The wizard is unable to open your query in datasheet view, possibly because another user has a source table open in exclusive mode. Your query will be opened in design view"
the above is the error i get when i use a wizard to create the query, after getting this message and going into design view i try to view the results in datasheet view and get the following error "Type Mismatch in expression"
If i dont use a wizard and create it in design view, when i go to view the results i get the second error message everytime.
If i create a query from 1 table only the query works without errors, but i always get errors when i have more than one table in the query.
Also i am the only person with the database open so therefore it is total impossible for the database to be open by another user in exclusive mode.
Anyone know what the problem might be or how to fix. Your answers will be very much appreciated.
I used to know how to get the path for the current database by running a query. I forgot how to do it. I am using Access 2003. I have tried querying the msystem tables without success. I have searched forums and the web without success. Anyone? Thanks!:)
