Trying To Return Specific Fields From Multiple Tables
Feb 10, 2008
OK so here is a working query:
SELECT Assets.*
FROM Assets
WHERE (((EXISTS
(SELECT *
FROM LCAMdump
WHERE Assets.BarcodeNumber = LCAMdump.T_TAG
)) =False))
OR (((EXISTS
(SELECT *
FROM LCAMdump
WHERE ((SELECT BuildingName
FROM Building_Names
WHERE ASSETS.BuildingNameID = Building_Names.BuildingNameID)=LCAMdump.BUILDING)
)) =False))
OR (((EXISTS
(SELECT *
FROM LCAMdump
WHERE ((Assets.FLOOR)=[LCAMdump]![FLOOR])
)) =False))
OR (((EXISTS
(SELECT *
FROM LCAMdump
WHERE ((Assets.DeskLocation)=[LCAMdump]![LOCATION_SEGMENT2])
)) =False))
OR (((EXISTS
(SELECT *
FROM LCAMdump
WHERE ((Assets.BuildingLocation)=[LCAMdump]![LOCATION_SEGMENT1])
)) =False))
OR (((EXISTS
(SELECT *
FROM LCAMdump
WHERE ((SELECT FirstName
FROM Employees
WHERE Assets.EmployeeID = Employees.EmployeeID)=LCAMdump.USER_FIRST)
)) =False))
OR (((EXISTS
(SELECT *
FROM LCAMdump
WHERE ((SELECT LastName
FROM Employees
WHERE Assets.EmployeeID = Employees.EmployeeID)=LCAMdump.USER_LAST)
)) =False))
OR (((EXISTS
(SELECT *
FROM LCAMdump
WHERE ((SELECT SSO
FROM Employees
WHERE Assets.EmployeeID = Employees.EmployeeID)=LCAMdump.LOGIN_SSO)
)) =False))
OR (((EXISTS
(SELECT *
FROM LCAMdump
WHERE ((SELECT UserID
FROM Employees
WHERE Assets.EmployeeID = Employees.EmployeeID)=LCAMdump.USER_LOGIN)
)) =False));
It works great returns the correct results. But I don't need everything out of Assets. I just need a few things from there and a few things from 2 other tables.
I tried this but it now gives back over 220 repeating results.
SELECT Assets.BarcodeNumber ,
Employees.UserID ,
Building_names.BuildingName,
Assets.Floor ,
Assets.BuildingLocation ,
Assets.DeskLocation ,
Employees.FirstName ,
Employees.LastName ,
Employees.SSO
FROM Assets ,
Employees,
Building_Names
WHERE (((EXISTS
(SELECT *
FROM LCAMdump
WHERE Assets.BarcodeNumber = LCAMdump.T_TAG
)) =False))
OR (((EXISTS
(SELECT *
FROM LCAMdump
WHERE ((SELECT BuildingName
FROM Building_Names
WHERE ASSETS.BuildingNameID = Building_Names.BuildingNameID)=LCAMdump.BUILDING)
)) =False))
OR (((EXISTS
(SELECT *
FROM LCAMdump
WHERE ((Assets.FLOOR)=[LCAMdump]![FLOOR])
)) =False))
OR (((EXISTS
(SELECT *
FROM LCAMdump
WHERE ((Assets.DeskLocation)=[LCAMdump]![LOCATION_SEGMENT2])
)) =False))
OR (((EXISTS
(SELECT *
FROM LCAMdump
WHERE ((Assets.BuildingLocation)=[LCAMdump]![LOCATION_SEGMENT1])
)) =False))
OR (((EXISTS
(SELECT *
FROM LCAMdump
WHERE ((SELECT FirstName
FROM Employees
WHERE Assets.EmployeeID = Employees.EmployeeID)=LCAMdump.USER_FIRST)
)) =False))
OR (((EXISTS
(SELECT *
FROM LCAMdump
WHERE ((SELECT LastName
FROM Employees
WHERE Assets.EmployeeID = Employees.EmployeeID)=LCAMdump.USER_LAST)
)) =False))
OR (((EXISTS
(SELECT *
FROM LCAMdump
WHERE ((SELECT SSO
FROM Employees
WHERE Assets.EmployeeID = Employees.EmployeeID)=LCAMdump.LOGIN_SSO)
)) =False))
OR (((EXISTS
(SELECT *
FROM LCAMdump
WHERE ((SELECT UserID
FROM Employees
WHERE Assets.EmployeeID = Employees.EmployeeID)=LCAMdump.USER_LOGIN)
)) =False));
I am sure it something simple but I am a novice at this so please help me. :D
View Replies
ADVERTISEMENT
Sep 16, 2014
lets say I have a table with five tasks for five employees and each employee's data type is with 'Yes or No'.
I need to have a desired result to check
1.if all the employees have completed that particular task then the result should be 'YES'
2.if anyone employee has not completed that particular task then the result should be 'NO'
so, for each task there must be a check on all the five employee's data type.
View 1 Replies
View Related
Nov 3, 2014
I have already made a query with all the information needed. What I am trying to do is create a form where a user can open the database and enter a unique number and have information from 10 fields associated with that number show.
View 5 Replies
View Related
Dec 19, 2012
I am trying to write a query that will return records from multiple tables. I currently get an error suggesting I create a subquery or else I get far too many records.
The query is EditAttendanceQuery (I left it in a bit of a state). The fields I need are shown in the query. The records I need are based on the Edit AttendanceQuery (Form). I can get the records I need without the CourseNumber and Section, but it all goes downhill when I include them.
View 14 Replies
View Related
May 28, 2015
Using Access 2010
I'm an expert user in Excel and am trying to learn more about Access, so I relate many of the functions to what they are called in Excel. And what I'm trying to do right now is count the number of fields in a specific record where the value is a specific result (CountIf).
For example, I need to return the number of fields in a specific record where the value is "Meets Requirements". The specific record will be determined by the Name, Contact, and Email fields (which together comprise the Primary Key).
Is there a way to count the values in multiple fields that equal "Meets Requirements"?
View 7 Replies
View Related
Jul 9, 2015
I am new to access i have a problem which is i have made a form which contains a subform and a read only subreport, what i want is the ability to select a record in read only subreport as in the picture attached and make a button that when i press on it, it should copy the values of the itemsID field, Packing field, ContainerNo field and origin field from the selected record and then paste them in the subform below.
Also i want to add more then one item, so the when i press on another record it should paste the values below the first record.
View 12 Replies
View Related
Jun 2, 2013
I am in the of designing an access 2010 database for data entry. Is it possible to create a button on a form in which a prompt asks a user for which records to export. Then depending on the entry export specific fields (First Name, Last Name, DOB) to a specific excel 2010 workbook (setup) and worksheet (template)?
For example, if the user entry is 1, only record 1: First Name, Last Name, DOB is exported to the setup.xlsx (more specifically the template worksheet of the setup.xlsx). However if 2,3,4 are entered then records,2,3,4: First Name, Last Name, DOB are exported to the setup.xlsx (more specifically the template worksheet of the setup.xlsx).
View 1 Replies
View Related
Dec 19, 2005
I am trying to pull just a specific date that a Claim was sent to be processed. But the query has 40 tables that it needs to go through to get all possible dates. I know you will all say this is over kill on the tables, but each table represent a travel claim.
The problem I have is that I get to the 10th claim and it stops pulling the date. I put the following statement in the criteria:
[Type the beginning date MM/DD/YYYY]
any ideas?
View 14 Replies
View Related
Dec 31, 2014
I have a form "frm_PatientNew" based on table "tbl_patients", this form contains a button "cmd_NewVisit" which is supposed to do the following: opens the form "frm_NewVisit" for recording a new visit for the last recorded patient in "tbl_Patients", I found many approaches depending on DMax and Dlookup and they worked fine just if "frm_NewVisit" is bound to "tbl_Patients", but "frm_NewVisit" is bound to "tbl_Main" which acts as a container for all information (patient data, visit data,service done and service provider), so the form "frm_NewVisit" contains fields from different tables. I wonder if I should create "frm_NewVisit" as unbound form, then adding fields from different tables to it and using vba to populate "tbl_Main",
View 2 Replies
View Related
Oct 28, 2013
A small issue I was wondering of for a few day . Is it possible in SQL query to SELECT multiple fields from multiple tables ? Example for the question is
Code:
dim my_var as String
my_var = "SELECT Emp_FName , Emp_LName , Emp_Adress " _
& " FROM Table1 " _
& " AND Emp_Date_Of_Payment , Emp_Sum_Of_Payment " _
& "FROM Table2 " _
& " WHERE Emp_ID = 3 "
Is this code actually valid in SQL gramatics , and is it usable if passed to a Recordset variable ( rs = CurrentDB.OpenRecordset(my_var) ) ? Just FYI - The two tables are not related and I want to keep them that way (If possible relate their records just via SQL/Vba )
View 7 Replies
View Related
Apr 12, 2013
I have 10 tables, 30+ fields on each table (every table has the same 'account number' field). I only need from 5 - 20 fields from each table. How do I get the certain fields from each table and put them in a table, query or report?
View 1 Replies
View Related
Oct 23, 2013
I am working on a database which has two tables used as part of a registration and login process.
I would like a couple of fields from table one to automatically update in table two, once the fields in table one are populated without using an 'on click' event.
The reason I would prefer not to use an onclick is because the completion of the form used to generate the users table does not require any buttons for the data to save.
View 1 Replies
View Related
Aug 15, 2007
Having problems with the following function to goto a specific record and return a value from a field in that record.
Code:'Public Function intFieldZConv(strICAO As String) As IntegerPrivate Function intFieldZConv() As Integer 'TEST PURPOSE ONLYDim strICAO As String 'TEST PURPOSE ONLYstrICAO = "KTCM" 'TEST PURPOSE ONLYDim cnCurrent As ADODB.ConnectionDim rsFieldInfo As ADODB.RecordsetSet cnCurrent = CurrentProject.ConnectionSet rsFieldInfo = New ADODB.RecordsetrsFieldInfo.Open "tblFieldInfo", cnCurrent, , , adCmdTable'rsFieldInfo.Index = "FieldICAO"'rsFieldInfo.Seek "=", strICAOintFieldZConv = rsFieldInfo!FieldZConvSTMsgBox intFieldZConv 'TEST PURPOSE ONLYrsFieldInfo.ClosecnCurrent.CloseSet rsFieldInfo = NothingSet cnCurrent = NothingEnd Function
strICAO is received from another function which is to be used to determine the specific record in the table "tblFieldInfo". Once that record is referenced, the value from the field "FieldZConVST" is returned.
My problem area is highlighed (index and seek) which I receive an error stating "method or data member not found". Any help would be appreciated on how to finding a specific record.
Thanks
Jeff
View 6 Replies
View Related
Jul 6, 2015
I have a large table with millions of entries. I want to run a query to return all entries that are on a Saturday. The date stored in the table though is just typical date format eg 15/11/2015.
View 1 Replies
View Related
Jul 26, 2013
what is the best/cleanest way to return the next primary key value which is in the format yyyy-###.These are case files for the current year, ### being 3 digit number incremented by one for each new case.
I could use a function that finds the last one, checks the date, increments by one and returns it, but maybe I could use a public property or a class? I'm keen to use classes more for code maintenance.With a class then: would i create a PrimaryKey variable of the class type/object, so when I need a new key I would just type PrimaryKey.NewKey, for example?
View 5 Replies
View Related
Dec 31, 2012
I have a Query of dates and I need to have those dates return onto a from horizontally?
View 1 Replies
View Related
Feb 25, 2014
I am trying to use a DLookup to search to find what block goes into the production of an item.
In a table called "dbo_vw_MCE_job_with_materials", each item contains a list of the parts which go into its production in a column titled "Expr1". However there are always more than one parts to this, with varying names.
What I am trying to do is to use a Dlookup to only return the first value which starts with B (as the block number is always the only part on the list which begins with B).
At the moment my DLookup would look ike this:
Code:
MixTypeTxt = DLookup("Expr1", "dbo_vw_MCE_job_with_materials", "[item] ='" & Forms![Theta Input]![ItemNumbertxt] & "'")
However I think as the item column contains the same item number in 8 columns, with different values in the Expr1 column, Dlookup only returns the top value "A-CF0057" (which is irrelevant as far as I am concered) - whereas I am hoping it will be able to pull the "B1499" value from the column as it is the only one starting with a B
Is there anyway to specify which value the dlookup function would return, or is there any way to apply some sort of permanent filter on my table?
View 1 Replies
View Related
Jun 27, 2005
I am trying to setup a database for vehicle stock control.
Im not sure if I have gone about this the right way as I am new to this but thus far it is working correctly except for one annoying problem.
The database consists of so far
tblIAWVehicleDetails (Primary key "IAWvehicleID" autonumber)
tblIAWSellers (Primary key autonumber)
tblIAWBuyers (Primary key autonumber)
tblIAWStates (Primary key autonumber)
tblIAWStatus (Primary key autonumber)
tblIAWSafetyDetails (Primary key autonumber)
tblIAWSold (Primary key autonumber)
In the Vehicle Details table a stock number has to be manually added as this will be used for new stock as well as current stock (Number range from 100 - whatever) "IAWVehicleNo".
This table contains all relevent data with reguards to make, model, bodytype, color etc.
The sellers table contains the details of the seller Name, address, Phone, LicenceNo etc.
The Buyers table contains employee names.
The States table contains all Australian states.
The Status table contains current vehicle status Retail, Wholesale, Wrecking etc.
The Sold table contains the details of the person who purchased the vehicle if sold.
The Safety details table contains a safety checklist for pre purchase inspections eg: Headlights yes/no checkbox, Headlight text field for any extra info.
It also has a field for a safety Certificate No once the vehicle is checked and recieves a Safety cert.
The forms are setup as
frmIAWVehicleDetails
frmIAWSellers Subform
frmIAWVehicleSafetyDetails
frmIAWVehicleSafetyDetails Subform
The Vehicle Details,Sellers,Sold & SafetyDetails tables all have the IAWVehicleID & IAWVehicleNo Fields but when the details are entered through the forms the IAWVehicleNo which is the manually entered number only updates to the tblIAWVehiclesDetails but the other IAWVehicleNo fields in the other tables remain blank.
Any advice or help would be greatly appreciated.
View 6 Replies
View Related
Sep 21, 2012
I have a table that is going to track people. First Name, Last Name, and Date of Birth in three separate fields.I dont want to be able to add the same person in the table. How do i do this when the data is in separate fields.
View 9 Replies
View Related
Oct 21, 2012
I have a member table and donation table. For a donation, I'd like to lookup the last and first names from the member table. I set the lookup for the last name and can see bothe first and last names in the drop down list. When I pick, I get the last name in the DonationT but how to I pick up the first name? That is, how do I fill DonorFirstName in DonationT from the FirstName field in the MemberT?
View 3 Replies
View Related
Dec 6, 2012
What I have is a table ('tblJobLog') with our main listing in it where we add customer job information as it comes in. The 'Customer Name' is a dropdown list that links to our table 'tblCustomers' so that we can ONLY select a customer name that we deal with. The alternative is to add a customer or list them as COD.
Now this is where I would like to target, the COD's. We still like to keep a record of who the COD is and we have some common customers that are listed as COD only.... So when we go to enter the customer name we have to put COD and in the description we are suppose to input the customer name (We will says RandyShop <--- me, for now).
What I would like to have done, if possible, is to see when we select 'RandysShop' from the dropdown, it checks to see if the COD Status (in the 'tblCustomers' table) is set to YES (its a check box, or YES/NO field). If it is set to YES (or selected) then the change the name shown in my main 'tblJobLog' to look something like COD: RandysShop.
The reason I would like, if possible, to do it this way, is because some customer go on and off COD regularly as the mess around with payment. So instead of creating a new customer that is listed as COD: RandysShop to have access do the leg work based on a simple YES/NO field (COD Status).
View 12 Replies
View Related
Nov 3, 2014
I was just wondering if this is a possibility to do in one query or if it has to be run from a number of different queries.
I am currently developing a database from scratch for work (with very little Access experience).
The current query I am trying to run, if linked to a number of tables with different information.
What I am trying to do primarily is link stock to a specific "Host Name", "Serial Number" and "Part Description".
In the "Host Name" there is for example - A1-TX10-10001, B1-TX2-10004, C1-TX-10004 - The latter part of the name is a unique identifier number. The first part is the compartment in which the "stock" sits. So you may have all three components (A1-TX1, B1-TX2, C1-TX3) linked to the same unique identifier (10001 for example)
The serial numbers naturally are different for every single one and of course the srial numbers are linked to the "Part Description" - which will read something like....."C1-TX3 Transmitter", "B1-TX2 Combiner" etc.....
When I run the query like this the Host Name (which is also linked to the unique identifier on its own (10001) it returns everything under "A1-TX1-10001"
What I would ideally like to do is write a statement so that if the "Part Description" contains "A1-TX1" it will only return rows that contain "A1-TX1" in the Host Name and the same for "B1-TX2" and "C1-TX3" in the same query.
If "Host Name" contains "A1-TX1" to return "Part Description" to contain "A1-TX1"
View 10 Replies
View Related
Feb 20, 2006
Hi All,
Being a newb, have a hopefully straightforward question. I'm writing a vehicle management database which covers eleven seperate areas. The data is currently contained in a spreadsheet with eleven seperate data sheets, one for each area. My thinking is I use linked tables as the spreadsheet needs to be occasionally updated.
My difficulty...
If I want to cycle through all records, I assumed I could query against all tables but don't seem to be able to.
The tables are not currently linked in any way and contain fields such as registration, emissions, list price, make and model and so on.
Any suggestions would be greatly appreciated.
Many Thanks
Q :)
View 1 Replies
View Related
May 25, 2006
Below is a Tab Delimited section of text to represent a SQL Table.
This is the result set of a select * from table where RNmbr = 0508.
I added the Top Row of A TAB B…TAB H
Row 1 references Column names of SQL Table.
And the First Column (under A) as Excel references.
A B C D E F G
1 RNmbr MesPt R1 R2 R3 R4
2 0508-1 1 28.0 48.0 48.0 74.0
3 0508-1 2 77.0 78.0 75.0 48.0
4 0508-1 3 81.0 59.0 65.0 56.0
5 0508-1 4 54.0 46.0 24.0 25.0
6 0508-1 5 21.0 2.0 15.0 74.0
7 0508-1 6 4.0 88.0 68.0 14.0
8 0508-1 7 8.0 94.0 87.0 96.0
9 0508-1 8 9.0 76.0 66.0 58.0
10 0508-1 9 48.0 48.0 35.0 74.0
11 0508-1 10 36.0 59.0 26.0 888.0
The challenge:
We can easily create STDEV(R1), STDEV(R2), STDEV(R3), STDEV(R4) from the above info.
However, we need to do a STDEV of all these points combined.
Within Excel, this is easily accomplished with the formula
= STDEV(D2:D11,E2:E11,F2:F11,G2:G11)
However, we are tasked of getting away from an Excel spreadsheet and putting this data into a database (imagine the above several hundred thousand strong).
We are using Access as a front end to SQL 2000. We need to have this conglomerate STDEV to be within Access (should the resulting STDEV fall out of spec) it will trigger an alarm for the production operator.
We are not having much success using T-SQL or Access in getting something which seems so simple in Excel. An Access query would suit our needs better.
Now, once this hurdle is overcome; there is a second phase. This table represents one of three, and, you guessed it, there is a need for a Conglomerate STDEV of ALL these points. Any help would be greatly appreciated.
View 4 Replies
View Related
Jul 1, 2005
Please be kind, i have little VB Knowledge, and wish to expand my learning on this topic.
I have a form that updates information on one table, and has a subform displaying info from another table.
the subform is filtered, and only shows data from what is specified from the filter of the main form.
If I update information on the main form for instance,
field 1, (the data on the subform has the same data so there is the relation), how do i update the subform by only updating the mainform? can this be done through some VB or something?
if you folks out there can give a Smidget of info on which VB codes i can use i can pretty much figure it out.
like Docmd.write something?
View 2 Replies
View Related
Jan 9, 2013
is there any way to put into the calculated field (in expression builder) conditions? What I need is something like
Sum If (Table1.Field1="Y" And CurrentTable.Field2=Table1.Field3)
I means sum how many times there is "S" value in the field1 Table1, but only for records where the field3 in Table1 is equal to the value in the actual table in Field2 (in the actual row).
View 13 Replies
View Related