I have a database which contains details of groups of students. Groups are assigned tutor(s) using a link table between the Groups table and a Tutors Table.
I want to create a query which shows each group with their tutor. If I add the three tables to the query I can correctly generate a list of groups with their tutors, but any group which hasn't got a tutor (i.e. where the Tutor column would be blank) doesn't feature in the results.
I want all groups to be listed, even if they haven't got a tutor. Is this possible? How?!
I have a query that pulls data from 2 different tables. The query should display two columns made up from data of the two tables. Some of the data in the two tables are equal. How can avoid from displaying equal results.
Dog Cat Dog Dog Elephant Cow Cow Chicken Dog Chicken
I do not want to display the result in row 2 seeing that both are equal.
I currently have two different excel spreadsheets linked to access 2003. The first few columns are very similar with similar headings. What i have now is a form displaying the data from one spread sheet (Engine Status), and another form displaying the data from the other (Engine Health).
As I am very new to access is there a way that i can link the two spreadsheets together and display all the data on one single form. I would like a combo box to find the required serial number (tail number) and once the serial number is selected the data from both spreadsheets will be displayed.
The spreadsheets i have to work with are of set standard for ease to input data from numerous other sources. I have attached examples of the two spreadsheets with the headings that are the same between the two although i have attached these to a single work book in reality they are seperate work books.
What i would like is for a combo box to display the Tail number then have single boxes to display core serial numbers across the form in order of position, then all the other data associated with that core serial number displayed in seperate boxes below the core serial number. I hope this is possible as it will prevent a lot of toggling between forms.
I have a form where I am entering orders. For this, there is only 1 item and 1 customer per order. The Order_Table is the 'general' of the form. However, I want a Customer_name dropdown on the form, and return data to 'display' on the form (but capturing and ONLY storing the Customer_no), once the user makes a selection.
Additionally, in the same vein, I want to have an Item_name list, where the user selects one from the listing, and display (again, no edit) the name. Again, only want to store the Customer & Item numbers on the Order table.Here's the 'general' table layout (I've taken out what doesn't matter):
My question is that can we do multiplication of data of two columns and result is automatically displayed in third columns in datasheet view.? Is it possible ?
I am trying to display all the fields from Employee table where Employee.TokenID= Rating.TokenID but I only want to display the fields from the Employee Table and not from the rating table. And each record displayed should be distinct by the TokenID....
Hi, I have two tables of data, one is a customer information (membersdata) table and the other is information recived from a bank (bankdata). Each customer has a 'bank description' field in its membersdata table and the bankdata table also has field 'bank description'. The query I have at the moment gives me back the data that both tables have a matching 'bank description'. The query I want is one that will give me the data from the bankdata table that does not exist in the membersdata table. So simply put the query I want is the opposite of the one I made with the wizard.
I have a table where I record attendance. In the table they select the type of training in via a check box "Training, Drill or Other". I created a query to show how many times someone has attended the Drills in each quarter (counts the check box). I am wanting to be able to specify which year is displayed as atm it is showing all te quarters for years. The SQL is below.
From this I have a report where it displays the data and have conditional formatted it so that if they have not attended more than 4 times in one quarter the "cell" turns red - the only problem is is that some of the cells have no data in it. How can I get the query to display 0 if there is no data?
QUERY SQL: TRANSFORM Sum(Abs([ATTENDANCE 2011].DRILL)) AS SumOfDRILL SELECT [MEMBERS LIST].[NUMBER/NAME], Sum(Abs(Nz([ATTENDANCE 2011].DRILL,0))) AS [Total Of DRILL] FROM [MEMBERS LIST] INNER JOIN [ATTENDANCE 2011] ON [MEMBERS LIST].ID = [ATTENDANCE 2011].ATTENDED.Value GROUP BY [MEMBERS LIST].[NUMBER/NAME] PIVOT "Qtr " & Format([DATE],"yyyy/q");
Display data from Query into Form..I am attaching the database dbtest.zip which contains a table, couple of queries and form.I have done what ever settings I could after going thru some tutorials / discussion forums online, but to no avail.
All that I want is, when I open the form "frmtest" in "form view", and select CS in Combo 8, the corresponding fields in the form viz Item name, Sl No and System Name should get updated on the form. I want to view it in "Single Form" format.
I have a form that displays employee details. I've also created a query that calculates the current age of all employees and updates automatically. I've also created a subform that displays the query data.
My question is, how do I get the query data to display on my main form? Can I do it directly from the query or do I have to use the subform? There are multiple columns on the form and I want to add it as a new column that displays the same as the rest but continues to update automatically.
I have a cross tab query that displays data by customer (rows) and MONTHS (columns).
However I need the columns to be the 12 months of the year 1 to 12.
However, if the selected data for a particular customer does not have any records in a month then I get an error in the report as the cross tab query only selects the months with data.
How do I get the report to show 0 or a blank in these columns
I have a graph chart and my row source type is a Query. So I retrieved data from the Query that I built.
However , I want to get the data from the uploaded excel file.
I have a form that will prompt user in the beginning to upload the excel file and replaced the old table in Access- "CrossSystemData"
Basically , I want to pull data from Excel file , Run the query(Data from excel file) then excute to "display a chart based on my query" ... Is it possible ?
Query SQL: "PolyWrongRegInsCount"
Code: SELECT GROUPING.INSTITUTION, Count(*) AS NO_OF_GROUP FROM (SELECT DISTINCT CATEGORY, GROUP_NO, INSTITUTION, IIF(Left(INSTITUTION,1)="P","POLYCLINIC","HOSPITAL") AS INS_TYPE FROM CrossSystemData WHERE INSTITUTION NOT LIKE "*UNKNOWN*") AS GROUPING WHERE (((GROUPING.CATEGORY)="WRONG REG") AND ((GROUPING.INS_TYPE)="POLYCLINIC")) GROUP BY GROUPING.INSTITUTION;
My Chart SQL :
Code: SELECT PolyWrongRegInsCount.[INSTITUTION], PolyWrongRegInsCount.[NO_OF_GROUP] FROM PolyWrongRegInsCount;
I created a subform that displays the records of a select query after selecting the desired data from a single combo box (for instructors name) and then using an OpenQuery macro attached to a button.
Only 1 record is displayed in the subform each time I hit the button. If I look at the query return there is no change. However, if I close the query and select a new instructors name, although I only get one record in the subform, the query when opened shows all the correct records.
I'm looking into storing query data in temp tables for my reports run better. From what I'm reading, it seems best to have the temp tables in a separate db, and to break the links to avoid bloating of the FE database. I'm unsure how to do this with VBA, especially since my temp database will be password protected. When do I break the link - when I close the FE database?
I want a Text Box Query on my form to display the Status, Workshop, Time, Enrolled and Limit. The problem is these values come from two different tables and the Enrolled value comes from a single field that contains the different workshops.
What I mean is: In Table[Attendees] a row contains a customer's Number, First Name, Last Name, Workshop and Phone Number. The workshops vary for each customer so one row on the table could have John Doe attending Cover Letter Writing and the next row could have John Smith attending Resume Writing. What I want is to be able to count the different workshops within the Field[Workshop] and total them and then display the total in a Text Box Query. I have a Text Box Query set up displaying Status, Workshop, Time and Limit as these values all come from Table[Workshops].
So basically I need to Query to also display a result that is the Total for each workshop from Table[Attendees] and display the total for each workshop in a Query with data from Table[Workshops].
Here is a link to an Example Database [URL] ....
I'm trying to avoid putting things on different reports and the like because the people using this are basically computer illiterate and if they have to click a button (no matter how well labeled) they won't do it and the information might as well not exist.
And if there's a better way to do it, I'm all ears. The only thing is, I have to update these workshops month by month. Since they are dynamic, I want to avoid creating separate tables for each workshop.
i have a main form named(EMP) i have a subform named(SEMP)with EMPID i have an another form Named(SDetail) with EMPID i want to open form Sdetail with filter records for data select in subform (SEMP) ,EMPID field Subform SEMP in as datasheet view. i can open sdetail for selected records only
Background I have a query (Q1) that retrives data from a table (Table 1). One of the fields in Table (F1) contains both text and numeric data (ie: 24 eggs). I want to separate these values in Q1.
Questions How can i in Q1 retrive only numeric data from F1 and display that data i a field? How can i in Q1 retrive only text from F1 and display that data i a field?
My db has multiple linked tables. As time passes and work on a project continues data needs to be added to a separate table that is linked to the first table to be populated.
i.e., Initial design data is entered first, when drilling is complete more data is added to a linked table, when drilling is surveyed yet more data is added to the second table.
What I want to be able to do is to query for a particular set of existing design data, display this on a form and on the same line enable data entry to other fields from other tables which are linked to the existing design data.
Im making a report that will display what driver went where how many times to keep track of mileage.
in a query, you search by driver, it will bring up which runs the driver had to where, but in the table of driver runs doesn't have mileage, that is in another table.
when it see that the driver went to smiths house, it will pull the mileage out of another table and display it.
I can not, or not allowed to enter data in fields in a query built from three tables that are jointed by a common field with the same name. The parent table is linked to another Access data base as is one child table the other is local data. Also, I am not able to view the child table data when viewing the parent table.
I have two tables with name of accone and the second with the name of acctwo. These two tables are same according to number of columns and also same according to data types and also same according to the column names just the data are difference and also one column (attribute) with the name of ID is same in both tables. Their is a primary key relationship (one-to-one) between these two IDs. I need a query that can combine the data of both of them and can be updated using query. I mean that data of first table and second table must become under one same column not two columns one for first table and second one for second tabel.
I have 9 seperate tables - each of the tables has similar headers
Claim # Agent Pass/Fail Request type Record Date
I have built relationships between all the tables. I am attempting have a singular query be run based on start and end dates under "record date" . I have the criteria already set. But when I run the Query no information is pulled. How do I get the query to pull this data from all 9 tables?
A field in a table can be populated by a lookup up but it has to be done manually or with a form.A "new" table can be created with a query that matches the data.
Is it possible to skip these steps and create a field that automatically populates with the data from another table based on other common data?I can do this in Excel but not sure it can be done in Access.
user_change primary key = user_change_id (AutoNumber) user_id (Number) which relates to the user_id from "users" table change_type (Text) action_date (Date/Time) ...
In user_change I record any changes made to the users table.
What I am wanting to do, within a query, is pull basic details from the "users" table (forename, surname, etc.) which is working fine, but also add in SPECIFIC data from the user_change table if it exists.
I want to pull ALL rows from the users table, not just specific rows, and not just rows where my criteria for the user_change table match.
This is the data I want to pull from user_change...
The MOST RECENT action_date WHERE change_type is LIKE "*issued*".
However there won't be a change_type LIKE "*issued*" for everyone - I want it to be included only if it exists.
At the moment my query is ignoring any users who don't have a user_change record with "*issued*" in the change_type value. I'm also getting duplicate user rows where people have more than one value for "*issued*" - I only want the most recent one...
I know that i can edit contents in a single table query and it will update the data in the underlying table, but how do i gain the ability to change data in a query when 3 tables are involved?
