Queries :: Show Entries From 2 Tables Not In Third Table
Jun 22, 2013
I have 3 tables;
tblEmployees - Stores employee data
tblProcess - Stores process data
tblTraining - Stores data on which employees have trained on which process
tblEmployees has a 1 to many relationship to tblTraining through empID
tblProcess has a 1 to many relationship to tblTraining through pcsID
When a new Process is added, a query auto populates tblTraining with 1 record for each employee in tblEmployees. Equally when a new Employee is added, a query auto populates tblTraining with 1 record for each Process in tblProcess.
In theory this should ensure that every employee has a training record for every process (and vice versa). However, as many of us know, never assume the user wont find a way to mess things up!
So i want to have a query that can show me Employee and Process without a training record. I have been manipulating a SELECT (SELECT) query to pull this together, but i just keep coming up blank.
I have attached a copy of the relevant tables and query. I have deliberately deleted a training record for empID 6 on pcsID 1 AND empID102 on pcsID 2 (qryCheckTrainingRecordsBalance will show the processes these 2 DO have a training record for).
View Replies
ADVERTISEMENT
Oct 16, 2013
I am trying to create a query which show me which of my customers in my database does not also contact a contact to go with it.
I have two tables; Customers and Contacts.
When I create a query I add the fields Company Name from the Customers Table and First Name from the Contacts Table. There are obviously other fields in both tables.
However when I run the query it does not not show me all the results. It only shows me customers if something has been entered into any of the fields in the contacts table for that Customer. I suppose this triggers a contact ID entry for that customer even if no Name has been entered.
However, how do I run a query to show me customer entries which have no contacts to go with them because no contact entry has been made for that customer?
View 2 Replies
View Related
Feb 26, 2014
I created a form for data entry only where records could be continually added until you were done and closed the form. Into that form I dropped a query that kept a running list in datasheet form of all the new records entered while the form was open, and then cleared when the form was closed (so the query was empty when the form was opened again).
My Problem: I accidentally deleted that query.
What I need:
-Query to be empty when form is opened.
-Records added while form is open get displayed in the query after record is saved.
View 1 Replies
View Related
May 14, 2006
hi
i am trying to show the total number of records that are in a table.
there are currently 8 entries in the table 'applications'
now i would like to show this on a tabcontrol in a form. I have been told to use Dcount. I have read about this and tried this on my form but unfortunately its not working.
Can anyone show me an example of how i would do this.
View 1 Replies
View Related
Jun 7, 2007
Is it possible to convert fields listed down to show across.
IDNo Code Position
AAA B01 1
AAA C01 2
AAA D01 3
AAA E01 4
I want to show it as
IDNo Code Code Code Code
AAA B01 C01 D01 E01
Is this possible using Access 2003?
View 7 Replies
View Related
May 8, 2013
I'm new to MS Access, indeed database design as a whole.
I have been tasked with creating an issues logging database and am having some issues...
I have a table/form that has all the fields I want, including a unique ID, called "Issues". I want to be able to log multiple entries in another table called "emails". The trouble is I am not having much luck. I can create an entry in the issues Form which then shows in the emails but I can't then add another email under the same ID in the "Issues" table.
View 2 Replies
View Related
Dec 30, 2012
I have a table with 22 entries and some how numberID 8 is missing . How can I get the numbers back in order without redoing the whole thing?
View 2 Replies
View Related
Jul 2, 2014
I have a table that is linked to a survey and pulls data from the limesurvey backend.
The user would like to add a local field, so they can add entries to each row of data.
Is that possible ?
View 1 Replies
View Related
Feb 15, 2006
Hi All,
I am wanting to build a table which consists of all the queries and tables in the database.
I am expecting column 1 to have all the queries and column 2 to contain all the tables that make up the query. So the table will look like this:
Queries - Linked Tables
Query1 - Table1
Query2 - Table1
Query2 - Table2
Query3 - Table1
Query3 - Table4
As you can see a table can be linked to any number of tables.
Now for my actual question. I don't want to have to type in all the queries and tables. I would like to choose any query and table from a drop down of all possible queries and tables that are currently in the database - like a list, where the list contains all the possible values.
I would then select the relevant entry.
Any pointers would be great.
Thanks,
Evan
View 2 Replies
View Related
Mar 13, 2014
I have a table in access which is updated weekly; I need to create two tables from this updated table.
1st table will consist of all the new entries for the current week
2nd table will consist of all the entries from the previous week - an amalgamation of all the entries which are not from the "current week" (table) For example; the table below shows the two entries from last week.
ID
Name
1
Adam
2
Ben
This week I have three new entries New entries
ID
Name
3
Charles
4
Richard
So when I run the same query next week I will get something like this.
Old Entries
ID
Name
1
Adam
2
Ben
3
Charles
4
Richard
[code]....
How do I get a query /queries which divides up the weeks new entries and also all the old entries.
View 11 Replies
View Related
Jun 11, 2013
I have a table called Stock Levels which contains 3 fields. (ID, ProductID, StockLevel) ID is the Pkey, ProductID contains duplicates and StockLevel which contains different stock levels
and I am trying to remove the duplicates and retain the the data so I am left with the correct stock number
what I have done is the following, but I am still getting duplicate values in productid and stocklevels
SELECT DISTINCTROW id, productid, stocklevel into mynewtable from stocklevels
I have attached a screenshot of the table
View 3 Replies
View Related
Oct 25, 2013
im in the middle of developing a database to log DOWNTIME of various machines in the factory. Basically myself and others will use it to log breakdown details and the amount of time (in HH:MM) the machine was off.
I have a form which will list details of a certain machine in a list box which i can populate using a query.
The list box will display 3 columns: Date of Breakdown, Machine Name and Downtime
I then have a text box below this that i would like to display the TOTAL amount of downtime for that machine (The listbox may have different information in it depending on the query criteria, e.g. It might display a certain month for a certain machine).
THE ISSUE i am having is its proving to be difficult to get the correct sum of the accumulative time to display correctly in the text box.
Is there a way i can use a query to calculate the total time of a column and return a value which is formatted as time (like this hhhh:mm or dd:hh:mm) or preferably use some VBA code on the form itself to total up the DOWNTIME column of the list box and display it with the correct format in the text
I have gotten close, but as the total amount of time exceeded 24 hours, it went past 00:00. I'm assuming it treated it as a clock and went into the next day? This is NOT what i am after, i need the total amount of days, hours spent on the machine and not a time of the day.
View 10 Replies
View Related
Jul 31, 2013
I have a Membership List Table with a calculated field entitled Member Name.
The second table entitled Groups contains only 46 records each of which has up to 30 Fields also containing member names.
I Have written a query to establish which of the members from the single field of the first table do NOT featured in any of the fields and records of the second table. In the Query Design View I have listed 'Member name' from Table 1 followed by all the relevant member fields from Table 2, carrying the criteria 'Is Null'.
When I run the query, it merely lists all the members from Table 1.
View 8 Replies
View Related
Feb 25, 2005
I have table with different companies and related info. Another table with contacts for each of the companies, each company might have different number of contacts. I want the form to show all the contacts for each of the company, if there are two contacts in the company then show two, if five then show five.
Just to make sure I am doing it the right way, what is the best way to build a form that is based on multiple tables.
Thanks in advance,
John
View 1 Replies
View Related
May 31, 2013
I have a query joining two tables. TableInvoice in the query is Product number and Date purchased. TablePrices contains the same fields and contains prices (quarterly) over the past five years.
Joined by PONumber, I want the query to find the price charged for that date. Right now, if the Product number (TableInvoice) is not listed in TablePrices, it won't show. So, 100 Records might return only 80 if TablePrices does not have all of the Product numbers.
Is there a better way to return all 100 records in TableInvoice and show (Blank) price data if the item is not in TablePrices?
View 1 Replies
View Related
Jul 5, 2007
Is there a place I can insert a snippet of code to have all the forms come up full screen. Some users think it is annoying that they dont always come up maximized. Any help would be greatly appreciated!
View 12 Replies
View Related
May 25, 2005
I am trying to customize one of my query table, so that it shows the latest review date of an employee (with multiple entries). I have gotten the SQL statement to work so that it shows the latest employee review date which is greater than the current date. But if the employee has 2 records after the current date and I want it to show the latest entry of the 2. I don't know to put add a sub-Select statement or whether to add another criteria in the Where criteria.
e.g. Current Date = May 25, 2005If Employee A's next review date is set for June 1, 2005 but has already been reviewed (the next review date is May 31, 2006). The query will show the June 1, 2005 and not the latest entry of May 31, 2006.
Because there are 2 review dates that are after the current date. What should I do to make only the latest entry appear?
Below is the my VBA code so far, which produces the above result.
SELECT tblEmp.fname, tblEmp.lname, tblEmpWorkHistory.[current store], tblEmp.position, tblEmpSalaryHistory.salary, tblEmpSalaryHistory.next_review_date
FROM (tblEmp INNER JOIN tblEmpSalaryHistory ON tblEmp.ssn=tblEmpSalaryHistory.ssn) LEFT JOIN tblEmpWorkHistory ON tblEmp.ssn=tblEmpWorkHistory.ssn
WHERE (((tblEmpSalaryHistory.next_review_date)>Now() And (tblEmpSalaryHistory.next_review_date)<=[Please enter the Next Review Date]))
ORDER BY tblEmpSalaryHistory.next_review_date;
My brain is stuck and can't figure it out. I hope any fresh mind could help me out. Thanks in advance.
View 5 Replies
View Related
Feb 22, 2013
Access 2010 ... I have 2 tables. One with base information second one is linked with multiple results each having a price. On table one i see the + sign when i click i can see the linked second table. Can i get a total amount of the price on table 2 on table one?
IE:
Table 1:
Trans ID - Seller - Quantity - Lot Cost - Parts Cost<-- the one i need total for.
123 - joe - 3 - $100 - $20 <-- the total of the 2 linked parts (Keyboard, Mouse)
Table 2:
ID - Trans ID - Part - Cost
1 - 123 - Keyboard - $10
2 - 123 - Mouse - $10
View 11 Replies
View Related
Feb 13, 2006
Hallo!
I'd like to make querie to show all changes made in database (one table) in some period of past time.
Please help me!
Robi
View 3 Replies
View Related
Sep 17, 2013
I am creating a database for an ambulance organization to track call history. One of the fields that needs to be populated is patient's last name. I need to show the last name when printing the form for billing purpose. However, I only want to shoe the first three letters of the last name in the table. For example, if the patients last name is Smith. When printing the form I would see the name Smith, but in the table I would only see Smi**.
View 1 Replies
View Related
Sep 12, 2013
I have Linked a few ODBC Databases to my database in the Tables objects. How do I make them usable for queries or as record source for my form? I tried to create a query but the ODBC databases do not show up in the Table options.
View 14 Replies
View Related
Jun 12, 2015
I have two tables. One with a persons name and ID, DOB, GP etc (TblName) and one with the persons ID and medication they take (TblMedication). I have a query that runs a report. The header is made up of the first table (TblName) and the body of the second table (TblMedication). All works fine but it shows all records from the first table (TblName) regardless of whether they have medication or not so the body shows up blank. I want to run the query with both tables information but only show the persons header if they have medication.
View 14 Replies
View Related
Jun 15, 2014
I have a table that includes a date/time field. I want to query that table and show all but the TOP N records. Since the number of records will always be changing I cant do it using the BOTTOM N records.
View 2 Replies
View Related
Aug 14, 2013
How to get the following results using 1 table:
Field1 ID is an auto record ID, field2 ID is actually field1 ID assigned that record, in other words record 1 has a roommate (record 5) assigned to it, record 2 has a roommate (record 4) assigned to it
Table A
Field1 ID Field2 ID
1 5
2 4
3 6
4 2
5 1
6 8
Results records I'm looking to display for would be:
1 5
2 4
3 6
6 8
I only want to display all fields for records, but I don't want to show their matching record, so I want to display record1, but not record 5 because record 1 has record 5 as a roommate, want to display record 2 but not record 4.
View 5 Replies
View Related
Mar 4, 2014
I have a PivotTable that I am trying to put together that will give me the following:
Types of Payments - Left
Accounts Where Payments came from - Top
Amount of Payments - Data
I want it to look like a spreadsheet where it will show me all the Types of Payments even though I don't have any data in there. Almost like an Excel Spreadsheet but I want it in Access. I was able to get the Columns to show up even though I had some blanks but now I need the Rows to show up.
View 11 Replies
View Related
Sep 12, 2013
I have one table in my access, and out of that I need to show a queri that will show only following result in 2 columns ID (same as original table)
SCHOOL_YEAR (all the students that are):
1 year and 1 year (repeat) as 1 year
2 year and 1 year (repeat) as 2 year
3 year and 1 year (repeat) as 3 year
Not to show dripouts, na and finished
Original table:
IDNAME
11 year
42 year
53 year
10dropout
11finished
12n/anot categorized
131 year (repeat)
142 year (repeat)
153 year (repeat)
View 3 Replies
View Related
