Queries :: Referencing Other Tables In A Query?
Dec 4, 2014
I have a split database that is working pretty well, but I've been asked to add a functionality to it. I have a table, product1, that contains basic product information. One of the values is a "class", in that if a product was created at a certain time, it fits into a class; the other value in this table that is applicable is the time the product was created.
I have another table, class1, that lists the beginning and end times of the class periods and the class numbers.
So, my users will "ingest" a product, including the time the product was created. I need the query (i think so anyway) to fill in the "class" value in product1, by determining which class it is in by seeing which class beginning and end time it is between in class1 and using the class number found in class1.
View Replies
ADVERTISEMENT
Jul 22, 2015
The code below bring back the results I want however.....
SELECT dbo_MF_PATIENT.Forename,
dbo_MF_PATIENT.Surname,
dbo_MF_PATIENT.DOB,
dbo_MF_PATIENT.Postcode,
Count(dbo_MF_PATIENT.HEYNo) AS CountOfHEYNo
FROM dbo_MF_PATIENT
GROUP BY dbo_MF_PATIENT.Forename,
dbo_MF_PATIENT.Surname,
dbo_MF_PATIENT.DOB,
dbo_MF_PATIENT.Postcode
HAVING (((Count(dbo_MF_PATIENT.HEYNo)) > 1));
My issue is I want to run this bit of code but only if the dbo_MF_PATIENT.MFPatientID appears in any of the 3 tables below:
dbo_ED_ATTENDANCE, dbo_OP_APPOINTMENT, dbo_IP_ADMISSION
Suppose im unsure on the joining because there is only ever one patient in the dbo_MF_PATIENT table but they could appear dozens of times in any of the other 3 tables.
View 3 Replies
View Related
Jul 23, 2015
I am trying to create a query that has a self referencing running total based on the values (point totals) of itself (running total of values in the running total column that have already been calculated for all previous records) plus the total of new points being added in the current record, less the total of points being removed in the current record. This running total can never go below 0, if it does, the running total should restart at zero and add in only new points and begin the process again with the next records
I am able to do this in Excel in less than two seconds so I know there has to be a way to port this into a query. I've attached an excel example of what I am exactly trying to do
If it takes multiple queries to complete the required output I am ok with it. In my previous outtakes I have had up to 8 queries but just couldn't seem to do it..
View 9 Replies
View Related
May 8, 2006
Hi all,
Firstly I am trying to retrieve data from three tables in a format like this
Table Data Fields Descirption
Table 1 A,B PC Details
Table 2 B,C Software/PC
Table 3 C,1,2 Software Details
Where the B's are linked as are the C's and these both form primary keys in their respective tables.
My form has on sub form in it. The main for refers to Table 1, displaying Fields A and B. My subform is linked through child/master fields by B and is used to edit which software titles are installed on each PC. However, as C, the primary key for the software, is a code, it is not obvious what it refers to exactly. Therefore I use a combo box who's row source is query containing the software title (1) and the column widths precceding the software title are set to 0 so that software title (1) is visible in the field in the subform. Hence, although you change the title name on the form, the actual change is made in Table 2 altering the Software Code (C). This is fine, except that there is more than one version (2) of some of the software titles. The versions are shown in the drop down list for the software title combo as an extra colum, so you know you are choosing the correct one. But i also want a seperate field which actually shows the version chosen. I cannot link a normal text box to the same query as it doesn't have the relevant properties, but I can do it with a combo as I am effectively doing the same as for the software title.
This is all very well, I could just lock the combo box and be done with it, but it doesn't look very professional and is a bit confusing to the user. Is there away that I can get around this problem?
Cheers,
Matt
View 7 Replies
View Related
Jan 10, 2008
I am working on a database at my workplace. We have a list of available IP adresses that can be used for printers, not all of which are in use currently.
I have a table for the printers at each branch, and that printer's IP address.
I have an IP addresses table that contains all available IPs, a domain name field, and a yes/no field entitled "In Use?"
I want my IP address table to check my printer table, and put 'yes' in the "In Use?" field if the IP address exists in both tables, or a 'no' in the "In Use?" field if the IP address is not currently assigned to a printer.
Can this be done, and if so, how?
Much appreciated,
~Mike
View 2 Replies
View Related
Apr 2, 2015
I created tables that have lookup fields referencing another field. Actually I have several tables that all have relationships and object dependencies in my database. Now that I am trying to create some different reports, when I run the report I get the ID rather than the contents of the field. Also, I have a 'Report Dashboard' so to speak that I can run different reports from. On the form I have Combo boxes that reference one of the tables. I can generate the report showing the information, however I tried using a text box (criteria)(=[Forms]![Reports Form]![Combo47]+" County") in the header of the report to reference the selection made in the form combo box. When I run the report I generate the ID in the header.how do I get rid of the lookup fields in the tables, or is there another work around?
View 5 Replies
View Related
Jul 3, 2013
I have a form that I want to filter by different fields on different occasions. i.e., I might look at all records that have a review date of earlier than today on one occasion and I might look at all records that have a Yes in a Yes/No box on another occasion (date then being irrelevant.)
I have finished the form which has a query as it's Record Source.
The only way I can figure out how to accomplish my goal is to copy/paste the form with a new name, copy/paste the query that is it's record source, with a new name, and then put the criteria I want in the new query and make that query the new form's record source.
Is there a more elegant way I can do this. For example. I have a form consisting of buttons that I use as a Navigation Form. Could I, in the VBA that opens the form when I click on it's button, stipulate which Record Source the form would use?
View 3 Replies
View Related
Oct 10, 2013
I have a form (named frmAddSession) with a combo box (named Band_Name) and lets say i have it displaying the band "Band A"
I have a query where the criteria is [Forms]![frmAddSession]![Band_Name]. when i run the query the results are blank. but if i type ="Band A" it works just fine.
why won't my reference to the combo box on the form work?
In a series of deductions to try and figure this out, my form only has the one combo box, and my query is based on only one table and only has the one field.
View 5 Replies
View Related
Apr 23, 2014
Is it possible to create a query to select all dates from a given reference date? I don't mean all dates in a table - I mean all dates generally?
(The idea being to fill the first field in the resultant dataset with the list of dates, then run subqueries off that to fill the remaining calculated fields)
I'm currently using a date field in one of my tables to populate this first field (the full SQL is in a separate thread here)
But that was just a convenient way of getting a list of dates; the dates in that table don't actually have any significance to the resulting dataset (other than they should roughly overlap with the dates I'm looking for)
The flaw in that method is that the table from which I get those dates can only ever have dates up to and including yesterday. I also need to get today's date in there (and calculate the subqueries based on that date as well).
It's also possible - although unlikely - that there could be random dates missing from that table as well - in which case I need to plug those gaps and calculate my fields for those missing dates as well.
For clarity; that first field (AsOfDate) should contain every weekday from the earliest date in that table (i.e. Min([tblBalances].[BalanceDate]) up to and including today. It doesn't matter if any of the dates inbetween are missing from tblBalances as the subqueries will just return zeroes for those dates (which is exactly what I want to see).
View 3 Replies
View Related
Oct 24, 2006
Hello,
I am trying to write a simple query that references calculations in the same query - but I keep getting the "enter parameter value" box.
Basically, I have some columns in a query calculating average and sum values for a column. I would then like to multiply some of those columns together in a new column, using an expression like this: =[AvgOfColumn1]*[SumOfColumn2]. Access doesn't want to do this for me. It seems very simple so I'm sure I'm missing something!
Thanks!
View 3 Replies
View Related
Jul 24, 2005
Hi,
My situation is hard to explain but i will give it a shot. What i have is a db with employees and its being used to track training sessions. These employees are split into 6 teams and those teams are split into 7 teams each.
I have written two queries, first asks the user what team to display. This data is used in the second query. The second query asks for what training item we are searching for. The result of the 2nd query is displayed in a form. Displaying of the information on a form is important. This way the user can verify that the members on this list are the ones he is updating. Now I wrote a third query that works off of the 2nd and updates the datecompleted field with todays date.
Problem is when i post a button on the form to show all these employees complete it referes back to the first query and asks all the filtering questions again.
I would like to know if there is any way to run an update query from a form that has already been filtered. Or if there is a way to reference these records in a new query so as to avoid all the filtereing again
hope i explained it well
Thanks
Ricky
View 7 Replies
View Related
Mar 10, 2006
Hey All,
I have a question regarding referencing a form using dates between.
Currently I have it setup as:
Between [Start Date] And [End Date]
on the query.
I want to reference a form, which i figured would be like this
Between [Forms]![frmSBReports]![txtStartDate] And [Forms]![frmSBReports]![txtEndDate]
But this doesnt work! :confused: What did I do wrong here? Any help as always is greatly appreciated!
Thanks!
Mateo
View 1 Replies
View Related
Aug 13, 2007
Hello - am hoping someone can help with the below query.
I have a database with two linked tables. Both of them contain a 10-digit ID number. One table (Table 1) has this ID stored as text; the second (Table 2) as a double.
I need to find records in Table 2 that are not in Table 1. I first created a stored query that converted the Table 1 ID into a double using the function CDbl([ID]). I then created a new query left-joining the stored query on Table 2 against the ID. The query displays the stored query's converted ID.
I run the new query. In the results - if there's a match, the ID successfully appears. But where there's no match - instead of a Null or an empty string, "#Error" appears. Can anyone advise why this is, and how I can work around it?
Thanks!
View 2 Replies
View Related
Dec 3, 2013
We are developing a complaints tool. Each completed complaint needs to be signed off by 3 leads and I'm hoping to display the progress of this in a form. Obtaining the first is simple and I was able to do that relatively quickly. The subsequence ones are now giving me a headache as it doesn't seem I can reference the initial subquery field in the others.Here's what I have so far:
Code:
SELECT COMPLAINT_TBL.COMPLAINTID, COMPLAINT_TBL.CASENUMBER, COMPLAINT_TBL.COMPLAINTANTFORENAME, COMPLAINT_TBL.COMPLAINTANTSURNAME, BUSINESSUNIT_TBL.BUSINESSUNIT, COMPLAINT_TBL.FINALRESPONSEDATE,
(SELECT TOP 1 [SIGNOFF_TBL].[SIGNOFFDATE] FROM [SIGNOFF_TBL]
WHERE [SIGNOFF_TBL].[COMPLAINTID] = COMPLAINT_TBL.COMPLAINTID
ORDER BY [SIGNOFF_TBL].[SIGNOFFDATE] DESC) AS FIRSTSIGNOFF,
[code]....
View 6 Replies
View Related
Apr 3, 2014
How to make 1 queries for many table
example :
Table 1
name Status
john Ok
mike No
Ann OK
Table 2
Name Status
michel Ok
jony Ok
Bruce No
Queries for OK
Name Status
John OK
Ann OK
Michel OK
Bruce OK
This is just simple example, actually i have many table January until December. So when i key in data using form on every month, I want just 1 queries covering all month, so easier to check.
View 13 Replies
View Related
Mar 18, 2013
I am working on creating a query to base a form on. I've done it several times in this database, but today, for some reason I cannot figure out how to do it.how I did it before but it looks like the SQL statement got wonky:
Code:
SELECT tblEmpInfo.EmpInfoID, tblEmpInfo.EmpIDFK, tblEmpInfo.JobNumberFK, tblEmpInfo.EmpType AS Expr1, tblEmpInfo.CraftCode AS Expr2, tblJobs.JobSite
FROM tblJobs INNER JOIN tblEmpInfo ON tblJobs.JobNum = tblEmpInfo.JobNumberFK;
Basically I have a look up table and all I'm doing is making it so I don't see the Cert ID it just pops up with the description.
View 2 Replies
View Related
Jan 29, 2014
I have two tables:
Budget
Actuals
I have these fields in both tables:
MONTH, Organization Code, Account, SubAccount, AMOUNT
I want to Compare the results in a single query. However the amounts aren't correct and after a full day I cannot find the answer.
Code:
SELECT [Budget].Month AS [MONTH], [Budget].[Organization Code], [Budget].Account, Sum([Budget].AMOUNT) AS [Budget Amount], Sum([Actuals].AMOUNT) AS [Actuals Amount]
FROM [Budget], [Actuals]
GROUP BY [Budget].Month, [Budget].[Organization Code], [Budget].Account, [Budget].SubAccount
HAVING ((([Budget].SubAccount)="K08"));
View 10 Replies
View Related
Apr 1, 2013
I have two tables and want to compile the data onto one query.
One table is a time monitor table with one record per date, with date as the primary key. It contains a number of fields where I enter the number of minutes spent on each task at the end of the day.
The second table is an appointment log. It can have multiple records for each date and the records contain a number of fields with info about the appt, one of which is the length of appt.
I have set up a relationship between the date field in both tables (was this necessary?). Is it possible to set up a query that will sort by date and grab the data from both tables, so that it will present the info from the time monitor table along with the summary of the length field from every record that fits the date range in the appointment log table? It would be good if it could also include the count for the total number of appointments that day.
It would also be good to have the info either grouped by day / week / month / yr or to have a drop down option in the query to select the date and summarise the findings.
View 4 Replies
View Related
Jan 23, 2014
I need to get the MAX id from two tables for use in vb.net. For example:
Code:
Table1 Table2
id, customer id, customer
How do I write a query that will return the last or MAX id from both tables based on a customer criteria.
View 5 Replies
View Related
Oct 14, 2014
one of my tables.
tbl_type1
-type1_id
-PONumber
tbl_type2
-type2_id
-PONumber
tbl_bill
-billID
-PONumber
-bill_type
Now I have huge data from tbl_bill which contains mix PONumber depends on type. If bill_type is type1 then it will get data from tbl_type1 table and vise versa. the problem is tbl_type1 and tbl_type2 is changing, when someone update this table then the data in tbl_bill will not match to 2 tables.
Now, i need an update query where I can update all records of tbl_bill to match with tbl_type1 and tbl_type2 PONumber.
View 3 Replies
View Related
Nov 5, 2013
I have TableA with
CityCode (indexed, no duplicates, Primary Key)
CityType (duplicates ok)
TableB, with
CityCode (indexed, duplicates OK)
StreetCode (indexed, No Duplicates, Primary Key)
A One to Many relation between Tables on City Code
In need to feed a cell table with the value of the number of streets that are in "Type1" cities
View 5 Replies
View Related
Jul 27, 2015
I'm trying to combine/summarize two tables in a query. What I've found so far is that I have to use a union query. I tried that but all I get is punctuation error.
So here is what I'm trying to do:
Code:
tblProdA
------------------------------------
ID | Date | Production
-----+----------------+-------------
01 | 1/1/2015 | 10
02 | 1/3/2015 | 10
03 | 1/10/2015 | 20
04 | 1/13/2015 | 20
05 | 1/17/2015 | 30
------------------------------------
tblProd2
------------------------------------
ID | Date | Production
-----+----------------+-------------
01 | 1/1/2015 | 5
02 | 1/5/2015 | 5
03 | 1/10/2015 | 10
04 | 1/13/2015 | 10
05 | 1/15/2015 | 10
06 | 1/17/2015 | 5
------------------------------------
qrySummary
------------------------------------
ID | Date | Production
-----+----------------+-------------
01 | 1/1/2015 | 15
02 | 1/3/2015 | 10
03 | 1/5/2015 | 5
04 | 1/10/2015 | 30
05 | 1/13/2015 | 10
06 | 1/15/2015 | 10
07 | 1/17/2015 | 35
------------------------------------
And I uploaded the sample as well.
View 5 Replies
View Related
Sep 1, 2014
Can't figure the SQL to UPDATE a table (tblAccomResv) with columns: AccomID and DtBooked. It will be an empty table.
I need to populate it with one record for each record in tblAccomodations (ID) by each record in tblEventDts (EventDts).
Like:
Room1 Dt 1
Room1 Dt 2
Room2 Dt 1
Room2 Dt 2
etc.
View 5 Replies
View Related
Jul 9, 2014
I have an invoice form that is from tblInvoice, and on that form i have 5 subforms all tied to 5 tables, tblRepair1, tblRepairs2, etc. When we get service done at a shop the user can enter up to 5 repair types, which all five tblRepairs are joined to the InvoiceID of the tblInvoice.
the issues is one user might put oil change in repair 1 and tire rotation in repair 2 and the other 3 repairs not used. another user may use all 5 repairs and put oil change in the repair 5 field. What i want to do is query a repair type, Oil change, and get every InvoiceID record that has oil change in it regardless of what repair table it was stored in, or in other words regardless of if it was entered in repair1 or repair5.
i have tried to "step down" the criteria referencing the cbx on the search form but if queries blank. I only want the records that have an oil change in them but still want to see all the repairs that were done with it.
View 4 Replies
View Related
Oct 22, 2013
I need to design an unmatched query on 2 tables to identify employees names that exist in 1 table but not the other (or names are misspelled, etc.). The tables exist in on a server and I do not have the ability to change either. Table 1 has the following structure:
EMPLOYEE NAME: Doe, John ID-12345
Table 2 has the following structure:
LAST NAME: Doe
FIRST NAME: John
Since the field names are not equal, the unmatched query wizard (as far as I know) will not work. I've tried using "NOT LIKE", LEFT JOINS, RIGHT JOINS, NUll Values, etc. in the SQL but nothing has worked as yet.Here's the SQL I've been working with but this returns all values (haven't figured out why):
SELECT DISTINCT [Table 1].[EMPLOYEE NAME]
FROM [Table 1], Table 2
WHERE ((([Table 1].[EMPLOYEE NAME]) Not Like "([Table 2].[LAST NAME]*"));
View 2 Replies
View Related
May 3, 2015
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.
View 4 Replies
View Related
