I have a very simple database for recording data collected from a questionnaire. The database comprises of
table1: QUESTIONNAIRE
fields: QUESTIONNAIRE ID
QUEST_1 ........
up to QUEST_75
table2: VALUE (1 to 6)
ANSWER (strongly agree, agree etc)
All of the 75 question fields in table1 record the same information, and all look up to a table2 which stores the 6 values: agree (1), strongly agree (2), neither (3), disagree (4), strongly disagree (5) and spoilt (6). As the data is being entered into a from using option groups, the database stores the value rather than the text in each QUEST field.
Pretty straightforward I thought, but when I come to query the database I am getting into such a muddle I feel like my head is going to explode!!
The results I need to produce should look like this:
Basically all I want is a count of the number of answers for each question! Given that the answers for all the questions are the same I thought this would be a piece of cake, but alas no.
I thought I would simply be able to crosstab my results from the questionaire into a new table, which would have the six rows (agree, strongly agree etc) and the questions as fields. The values should be the count of "agree" etc. I can quite happly have one field and the counts of each answer but when I try an add any other fields it just duplicates the counts!! I have sat staring at my query and am at a complete blank as to how I resolve this seemly simplistic problem :confused: .
Please if anyone has any ideas why I am in such a muddle over something which seems so simple I would really appreciate their help!
Ive got a form, that has inputs to a table, and it also has 2 subforms that link to another table, the subforms are different forms but they link to the same table, this is bacause I need to be able to enter two different items off the table that may be different items, the reason Ive used subforms is because there is the need to have the part number for the item, as well as the weight, this will be a growing list as the Dbase expands. now I have the two subforms with a combobox for part number, and the weight field looks up because of the combo,
back on the main form I need a entry that adds up the 2 weight fields, and enters them on the main table that the main form writes data to, can someone help?
Ive been heatbutting my monitor for a week or two now, and my limited know how on Access is beginning to show :-)
Hope this is the correct forum, and I am not breaking any rules of etiquette. :rolleyes:
I am after some guidance on how to start to tackle a problem, rather than any advice on the details of how to do it. That I want to work out for myself in order to try and improve my mastery of Access.
I have a team responsible for completing a number of scheduled reports. These reports are scheduled for different frequecies ie :- some are scheduled Weekly on a particular day, some are scheduled Monthly on a particular date, some are scheduled Monthly say on the 1st Monday of the month.
My goal, amongst others, is to be able to, from the switchboard,:-
a. Print a list of reports scheduled to run in the current work week. b. Print a list of reports scheduled to run between any 2 inputted dates.
This is just part of what I want to acheive from this database, but as we all know Rome wasn't built in a day!! ;)
This post is a bit long, but it is actually really simple. I have two tables(DEDPARM1 and DEDETAIL1). I ran a “CREATE TABLE” query using these two tables, to create a NEW table called “Biweekly_Temp_Table.”
DEDETAIL1 sample data SSN | Name | Type | Amount 555-99-8888 | Joe | FAE | 1000 555-99-8888 | Joe | FAR | 20
BIWEEKLY_TEMP_TABLE results SSN | Name | Type| faeAmount | farAmount 555-99-8888 | Joe | FAE | 1000 | Null 555-99-8888 | Joe | FAR | Null | 20 This result makes sense, b/c it is what I wanted.
Now here is the problem. I ran another query(qrySingleRecord) against the BIWEEKLY_TEMP_TABLE to combine the TWO records into ONE. It works, BUT the faeAmount will equal 2000 and the farAmount equals 40. Why is Access doing this? I know something called Cartesian Product will occur if I query against both DEDPARM1 and DEDETAIL1. However, in this case I am NOT querying against these two tables; the whole purpose of creating a new table(BIWEEKLY_TEMP_TABLE) is to try to prevent Cartesian Product. Please help.
Ive done this before in College but it was along time ago and i cant remember what i did
Bus say I have a table with Businesses and i need each business to be able to have multiple records associated with it.
So i have two tables one named 'businesses' and one named 'orders' how do i make sure that when a business makes an order the data in 'orders' is associated witht the releveant business in the 'businesses' table
I really suck at table design and coding, and access. I'm a techie that usually stays away from all this development stuff; it makes my head hurt. I'm a lot happier knee deep in an Exchange migration than developing even the most basic of databases as I suck at it (think I mentioned that already) However, i've been roped into doing a tiny little project for a friend. Below is the process I went through for the data / table design. When I started to create the relationships it got messy with the many to many relationships. Am I over complicating it or am I on the wrong track? Any assistance from you super-clever developer dudes would be greatly appreciated and would affirm your superiority over us humble techies yet again. :)
A Supplier can have many Items, SupplierItemCodes and Costs An Item can have many Suppliers An Item can have many Units (e.g. Sharp Concreting Sand comes in many different weights (Units)) A Unit can have many Items (e.g. the unit of 25Kg can have several different types of sand or other items associated with it)
So what I did was to create four tables (* - primary key, ( ) foreign keys):
I created the ID fields in each table even though Supplier, Item, Unit and SupplierItemCode are all unique values that could be used as primary keys (or would that be bad practice; I told you I sucked at this?).
1. What I'd like to do is do a lookup based on 2 fields in my table. I'm tracking inventory for the company I am currently working with. I have a computer table with 3 relevant fields: compID, areaId, locationId
computer table compID = PK for this table areaId = FK from area table locationId = FK from location table
area table areaId
location table locationId areaId
There is a distinct relationship between area + location. I want a lookup for the locationID, based on the area they have selected. Thanks for any help on this one. On to the next question related to this.
2. As mentioned above I have an area table controlling the general departments (for lack of a better word). When an area is selected in the table, they have the ability to do a lookup for the relevant locations (implemented in the combo box). I'd like the user to be able to add a new location item in the computer table, and have that value be added into the location table with the corresponding areaId as well. Am I looking for cascade update or something of this sort? Thanks anyone. This is my first post here, I'm quite new to microsoft access as a database tool.
I haven't built a relational DB in years and I realise I'm pretty damn rusty at it. Any help would be appreciated.
Basically I've compiled a list of software applications our offices use, and I'm compiling which PCs have which software installed. Ideally I'd end up building a form with checkboxes for common software apps and text fields holding serial numbers or login IDs where applicable. But I'm stumped already on just the table layout. I currently have:
tblSoftware: SoftID (Autonumber) - Primary Key and identifier for individual software apps Software (Text) - Software title Description (Text) - Any additional information (Licence, etc)
tblComputers: NetID (text) - The Network Identification of the PC, also acts as Primary Key User (text) - Name of user assigned to the machine
and having a One to Many relationship between NetID and SoftID. This should be a simple DB but I've been separated from all my manuals and the net is proving more useful for specific fault finding.
How can I set up the tables and relationships so that a query can be used to update the software for each computer?
Hi, I was asked to help create a very simple Access DB for a small restaurant to help them keep track of inventory.
The only things that the owner wants to keep track of are: 1.Item Catalog 2.Date and Quantity of Item IN or OUT 3.Current # of Items
My problem here is that there are 5 Item categories, each with a relatively lost list of Items under each one. 1.Drinks 2.Ice Cream 3.Kitchen Supplies 4.Grocery 5.Etc.
Will it be advisable for me to create an Item Catalog TABLE for each of the category instead of piling all the Items into just one Item Catalog TABLE?
The Designs I’m currently considering are:
A.)
TABLE SET 1 : Catalog of Items Fields (ID, Name) •TABLE Drinks •TABLE Ice Cream •TABLE Kitchen Supplies •TABLE Grocery •TABLE ETC --------------------------------------------------- TABLE SET 2 : Movement IN/OUT of Items Fields (Movement ID, Name, Date, IN-Amount, OUT-Amount) •TABLE Drinks •TABLE Ice Cream •TABLE Kitchen Supplies •TABLE Grocery •TABLE ETC
B.) TABLE 1: Catalog of Items Fields (ID, Category, Name)
TABLE 2: Movement IN/OUT of Items Fields (Movement ID, Name, Category, Date, IN-Amount, OUT-Amount)
I’m really more inclined to choice A because I just re-learned Access last month and will be creating my first serious database just now. I don’t want to choose a more complex table structure (choice B) that might cause problems with the DB later (as it will really be used to keep track of real-life inventory and might screw up their operations if it goes haywire). Is Design A viable? Or flawed?
Another question I have is with the structure of the Movement – IN/OUT table. Is this workable? My thought is just to let the user enter something like:
In the database, then compute current Quantity of the Item using a query (Sum[IN] – Sum[OUT]) . Will this work? I’m a bit apprehensive with this because I’m thinking I should have another TABLE that will store the actual inventory Quantity instead of the Quantity just being computed through query. But doing another TABLE for Quantity does complicate stuff a lot since it will have to determine which Quantity value gets added to or subtracted from based on many criteria. Will it be OK just to use the query to compute Quantity left?
it's record source is a table called "tblPatientDemo" that are populated based on the information place in the above text boxes
Here is my problem
I have a subform called "frmPatientLanguageSub"
Source Object......... frmPatientLanguge link Child Fields..... PatientID link Child Fields..... PatientID
When list box Multi Select is set to "Simple" and I add a new patient and click on multiple selections in the list box it does not populate the "tblPatientLanguge"
But if I change the Multi Select is set to "None" it populates the "tblPatientLanguge" for that patient
Any Ideas why access populates the "tblPatientLanguge" if I set the Multi Select property to "None" but not for "Simple"? If so How do I make access poplulate when the table when muliple selections are selected in the list box?
Hello All, At work we have a large and messy Contacts list so I decided to set one up using a database. At present I have 3 tables:- Companies (custID,companyname,address,etc) People (nameID,firstname,middlename,lastname,custID) Phones (phoneID,phonetype,areacode,number,?????)
My problem is this, Some of the phone nos belong to the individuals and some belong to the company. If a person is replaced at a company I need to reasign the company phone nos to the new person whilst retaining the individuals and their personal phone nos. If a company is deleted I need to delete only the company phone nos. and if a person moves within the company I want the company nos to reasign to the new replacement but keep the personal nos of the individual. Now I see its going to be more complex than I thought.
Can anyone help me with the table layout and links. (nb this is only a simple database relating a person to a company without using departments etc.) Its main use is to provide phone nos names and addresses quickly. Many Thanks Peter
Hey guys. Seems simple enough, I just can't figure out how to do it. How can I display the total number of table entries in a textbox on a form? thanks!
Hi there, I have a really simple question. I have a field in a table that contains comments (text format). I want to run a query where I can get only the records that contain the word "high" in the comments. There may be some typos so I would like records returned that also contain something like "high". High is not the only word in the comments.
ItemType is a list of items (Table, Desk, Bookshelf, Chair) Style is a type of style for the item (Circular table, Radial Desk)
I want to create the Items.Style field based upon a query of the StyleTypes. So if a user types "Desk" in Items.ItemType then the Items.Style field will be limited to only those Styles which match that selected item within the StyleTypes table.
Think thats clear enough, but my attempts have all failed, hope you guys can give me a push in the right direction, thank you!
I have very little knowledge of databases and even less about writing queries.
The problem: I have a database that lists cities (such as Paris, London, New York etc) as rows and employment status (Full-time, Part-time, Unemployed etc) as columns. I need to go through each city and record which employment status is greatest (for each city) and present it in a new column. I can do this manually but I’m sure a query can be created to automate the process.
Hope that all made sense. Can anyone suggest ideas on where to start?
hi have just designed a rather simple music database for my dad to use at home as he has large collection. have a table with the heading"artist" and another one with "album" ...my problem is that on the table there maybe 1 or more instances with the same artist but with different albums...when i run a simple query for ..say artists starting with the letter"a" i may get about 10 cases of the same artist appearing in my query when all i want is one...(depending on if i may put in say one artist like abba having 10 albums etc)...i am entering each artist with the album each time so i may enter the artist in say 10 times....depending on how many albums etc
if you understand all that...i would be grateful if someone could help me with a simple query alan
I have a query that returns the uncompleted jobs from a single table ( its a calls database) all i need is query that returns the amount of uncompleted jobs onto the switchboard I.E whatshername has X amount of uncompleted jobs , i have done a search and most of the solutions are very complicated for what i want to do .....anyone any ideas ?? pls
SQL and Access are both new to me. I have purchased some books (which are still in the mail), and have gone through this forum (which seems to be a little more advanced for me at this moment).
I have a database that contains Business_Unit (Store Numbers), Post_Date (Last Date of Inventory), and other misc. stuff. Anyways, My goal is to pull the latest date of inventory for each individual store before the last ninety days. What I have so far:
SELECT DISTINCT informix_shrink_head.business_unit, informix_shrink_head.counting_event_id, informix_shrink_head.post_date, informix_shrink_head.delta_cost, informix_shrink_head.delta_count, informix_shrink_head.count_reason_cd, pcw_loc_master.closed FROM informix_shrink_head, pcw_loc_master WHERE (((informix_shrink_head.post_date)<=Date()-90) AND ((pcw_loc_master.closed) Is Null)) ORDER BY informix_shrink_head.business_unit;
However, I get the same store multiple times and for multiple post dates. I do not even know if this can be done in one simple swoop, or if I need to create another table and re-query that? Any assistance or comments would be much appreciated.
I have a database that collects information on products when engineers return them, what I need to do is when they have submitted the form, email it to 3 people so that the paperwork can be raised.
I understand that I will need to create a query to display just the last record and then email it.
So the idea is when a record is created and the engineer clicks submit the last record then gets emailed to the three people.
Please be gental with me im still quite new to all this.
How do i create a query that just displays the last record?:confused:
I am sending ZIP file in attach with files in it. I would like some help to change the 'Q_test' query so that I can get the result shown in the excel file.
I want all the records to appear which have some text in that particular field. So if there is no text in that field, it won't come up. What would I need to write under that field in the query?
I am trying to run a query to retrieve all rows in all tables using a row called Project Status. I am however not able to retrieve any data what so ever with the wizard and by using the limited sql I know. I have attached a copy of my tables if it helps.
The relationships are as follows: Release to PCR - One to many, Sponsors to PCR - One to many. The column i want to filter on is Project_status located in the PCR table.
