Queries :: Joining Tables - Missing Data
Jun 4, 2014
I have 2 tables: one for repairs and the other for the billing for those repairs. There is a foreign key(record_num) in the billing table to match the primary key(prikey) in the repairs table. This works fine as long as the unit repair has been completed.
Now an employee wants to see records even if they are not completed and wants the rate to be $0.00 if the unit has not been completed. But by this method there is no record in the billing table.
My problem is if I have the 2 tables joined then I only see records that match both tables. Here is my SQL for the query:
SELECT DISTINCTROW tbl_module_repairs.end_user, tbl_module_repairs.pickup_date, tbl_module_repairs.complete_date, IIf([pickup_entity]="Storm","APS Storm","APS Field Tech") AS [Repair Pickup], tbl_module_repairs.mfg_part_num, tbl_module_repairs.manufacturer, tbl_module_repairs.module_type, tbl_module_repairs.incoming_module_sn,
[Code] ....
View Replies
ADVERTISEMENT
Nov 21, 2006
I am new to access and am having some problems. Here is what I have and what I want to do. I have one table that contains the ID number for a particular facility, facility name, and the sample location id number(s). Sometimes there is only one sample location id number and sometimes there are multiple. I have another table that has results of samples taken - it includes the facility id number, sample location id number, and the corresponding sample result. What I would like to do is join the data in the two tables so I have one table that shows takes the sample results from table 2 and combines it with the information in table 1.
Any help is appreciated
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
Nov 25, 2014
I have a question regarding making a query with joint data that are not identical, as I'm not sure whether it's possible.
I have to look at certain serum levels at te start of a new medication regimen, and serum levels after 5 years.
I have one database with:
Patient number
Start date medication
Start date medication + 5 years
And one database with:Patient number
Date of blood test
Serum level molecule A
Serum level molecule B
What I want to do is make a query where the patient numbers are joined. The problem is that the "start date of the medication" and the "start date + 5 years" are not identical to the dates of the blood tests. E.g.: I have a patient that started medication on 01/March/2006, but he has had blood tests done every 3 months from 28/November/2003 till now, and not on 01/March/2006.
I would need the results of the blood tests (of molecule A and B) at the start of the medication and after 5 years, but if the test date differs by a day, Access already sees they're not identical.
I would like to have access select a blood test date that is as close to the medication start date as possible, within a time frame of e.g. +3 and -3 months. Is this at all possible?
View 4 Replies
View Related
Apr 2, 2013
I am trying to use a join query to join two tables in order to create a form. I have done this on two other occasions in my database with no issues. Now it is only pulling the ID and I want it to pull the description. I have looked at the SQL view for the other queries that are similar as well as the design view and all of them are set up the same way with the exception that this new join query is pulling CertID rather than the CertDescription.
Is there any reason why it would be doing that? Is there an error that I could possibly be doing? It seems according to my notes that I have created all the queries the same but this last one does not pull the same info as the other queries.
View 2 Replies
View Related
Dec 26, 2014
I have tried to combine data from 3 different tables (unrelated) to make a new one. However, as I understood I can not do this because these 3 tables are unrelated. In my case my 3 tables are for 3 labs' material requirements. I am planning that each lab will fill a form which directly related to there field and direct to me. That is why I intend to have separate tables for each. However, I want to create a table where I will be able to see all the requirements of different Labs altogether. This new table will be kind of Orders.
View 7 Replies
View Related
Jun 29, 2013
I've got a query that uses a key from one table to pull back records from another.
My question is...
On the table with the key there is another field that I want to use to pull back data from the same table from above.
A bit more info...
Table one holds colleagues details
Table two holds records
Within table one there are 2 different fields that hold different ID's for the same colleague, currently I run two different queries to pull all data - I want to know if I can simply run one query that will return every thing?
Schedule an import or export operation
View 11 Replies
View Related
May 8, 2013
All; using 2010. I have a table that I need to update some data from another table. I want to use the SSN but one of the SSN fields in the table has letters at the end of it and doesnt return any records. How can I join fields?
View 1 Replies
View Related
Aug 13, 2013
I have an access database with several linked tables (linked to MySQL database) and several local tables. The theory is that if there's ever a connection issue, the device connected to the computer will continue logging data to the local tables. Once a connection is re-established, the linked tables should be updated with all the missing records which appear on the local tables.
I found several possibilities which I outlined below, but I've been asked to investigate whether there's a built in function in access that does this for you and use the other options as a last resort. Does access have any program feature that updates one table with missing data from another table, or will I have to write VBA code to do that? Options I've discovered:
1) Write unmatched query and insert missing data into table.
2) Create a linked table on MySQL that will link to the local tables on access, then compare the records there.The boss isn't happy with those options because he wants to keep the amount of code we add to a minimum. Ultimately, we hope that a program feature that does this is built in to access. If not, I have no problem adding code to do this instead.
View 1 Replies
View Related
Jul 19, 2013
I currently have query that looks at 2 tables that hold financial information. I am querying the data to do some adding and subtracting based off a financial class of a facility. The issue I am having is that one table may have a financial code that the other does not have but I still need to show that financial class for the facility.
My looks as follows
FROM [CashValue Link] INNER JOIN TCashValue ON ([CashValue Link].FINANCIAL = [TCashValue].FINANCIAL) and ([CashValue Link].Date1 = TCashValue.Date1) AND ([CashValue Link].FACILITY = TCashValue.FACILITY) AND ([CashValue Link].CLT = TCashValue.CLT)
I am sure I am over looking something.
View 2 Replies
View Related
Sep 3, 2014
I have a master table that holds all of my data. The table details what qualifications someone is holding.I would like a query that would enable me to produce a list of people who DO NOT hold a qualification.
View 8 Replies
View Related
Mar 8, 2014
I've got an Excel sheet with +700k rows and 20 columns that I wanted to import to Access. All fields are text except the field that I want to use as a primary key, but I planned to import that as a text as well.
When I used the import wizard, I set all fields to import as text except for three that I set to memo. The wizard didn't say there was any error after importing the data, but when I checked the table, I noticed there were *a lot* of records where many fields where blank. Some fields where completely unaffected by this problem throughout the entire table, but in the rest of them, there is data missing in many records, and when there is data missing, it is not always the same fields that are missing. I have been unable to find any pattern that explains why sometimes the records were imported correctly, and why sometimes they were not.
View 2 Replies
View Related
Jan 10, 2015
How to fix some records in my access table. It is a huge table more than 12k records!
In one of the field there are some data missing. The logic to reconstruct them is easy but I am not sure how to apply it in Access.
I have three columns one is the student ID, Year, term1 and term2
ID Year Term1 Term2
1234 2001 001 002
1234 2002 002 002
1234 2003 002 003
1234 2004
1234 2005 004 004
3311 2001 003 003
3311 2002 003 004
3311 2003
3311 2004 005 005
In the above example student 1234 has a missing record in year 2004 which supposed to be Term2 in the previous year (i.e. 003) and Term2 supposed to be Term1 value in the following year (i.e. 004). Similarly for student 3311
ID Year Term1 Term2
1234 2001 001 002
1234 2002 002 002
1234 2003 002 003
1234 2004 003 004
1234 2005 004 004
3311 2001 003 003
3311 2002 003 004
3311 2003 004 005
3311 2004 005 005
View 3 Replies
View Related
Jul 28, 2014
I'm trying to make a sub form that displays the hours of an employee selected in a listbox. I've got most of it working but having a bit of an issue.
The info for thre query is in 3 tables:
tblStaff (name etc)
tblShifts (start and end times for days that this employee works)
tblDays (a list of days names so I can use numbers elsewhere)
My query looks like this:
Code:
SELECT tblDays.dayName, IIf(Nz([startTime],"")="","NWD",[startTime]) AS start, IIf(Nz([endTime],"")="","NWD",[endTime]) AS [end], tblStaff.staffName
FROM tblDays LEFT JOIN (tblStaff RIGHT JOIN tblShifts ON tblStaff.staffPK = tblShifts.staffFK) ON tblDays.dayPK = tblShifts.workingDay
WHERE (((tblStaff.staffName)=[Forms]![frmMain]![lst_myTeam] Or (tblStaff.staffName) Is Null));
This worked fine with a single user and some test data - it correctly displayed all days of the week, with start/end times on Mon and Tues where I had entered shift information, and "NWD" against all other days.
However, when I add a couple more employees to the mix it shows the correct info for the first employee, but anybody else it will only display days where person 1 doesn't have any hours. I haven't entered any hours for the new employees, but the query should still display Mon-Sun with NWD in every column. It shows Wed-Sun but Mon and Tues are missing.
I've tried different join types but they all come back with "ambiguous joins" error when I try to run.
View 4 Replies
View Related
Apr 19, 2007
I have some experience doing 'Update Query' using two different tables but I'm having a hard time doing an 'Update Query' using 3 tables.
I have my source table TP05XY with the fields 'Mark' 'Date' 'UTM_Edig' and 'UTM_Ndig'. Mark and Date are my primary keys (they together uniquely ID each record). I have my Observations table with the fields 'Mark' 'Date' and 'Obs_ID'. The last table is Locations with 'Obs_ID' 'UTM_E' and 'UTM_N'.
I want to update my fields UTM_E and UTM_N from UTM_Edig and UTM_Ndig. However, to do so, I have to go from my TP05XY table, through Observations table to update Locations table. Table TP05XY is joined to Observations through 'Mark' and 'Date' and Observations table is linked to Locations through 'Obs_ID' field.
I have tried a few options without success ... anyone knows how to do it?
Thanks,
Josée
View 1 Replies
View Related
Oct 27, 2014
I have a db with 2 tables one with 10 fields and the other with 11 fields. In these tables there are 4 and 5 non common fields in tables respectively.
How can I join these tables in a 3rd table with all the fields from both tables.?
View 1 Replies
View Related
Oct 25, 2006
Hi
I have an Access database that contains 2 tables. Both of these tables have the same structure and have the same field names. So:
Table 1 Fields :
Name
Address
Telephone
Fax
Table 2 Fields :
Name
Address
Telephone
Fax
Is there a way to write a query, that will show the results of both tables in one go. (None of the information in table 1 is duplicated in table 2 - I want to show all records from both tables in one new table). So, if both tables had 3 records each, the query would return:
Name - Address - Telephone - Fax
jon-uk-12345-54321 (from Table 1)
julie-uk-21451-41541 (from Table 1)
paul-ir-98545-11241 (from Table 1)
pat-uk-99585-63362 (from Table 2)
phil-uk-99985-99631 (from Table 2)
ted-uk-44444-55555 (from Table 2)
These 2 tables need to be kept separate, so I can't copy and paste the records from Table 1 into the bottom of table 2.
The field names are the same in both tables.
Can this be done?
Many Thanks
View 1 Replies
View Related
Jan 2, 2007
Hi All!
I have two tables. Table Heffalump which has field "DealGroup" and other fields as well... and table 8-YTD Principal Investments whic has field "book" and other fields as well... I need to use BOOK and DEAL Group as a combination primary key to compare against table 8-YTD Principal Investments. If there is a new DEAL GROUP then append into table "8 - YTD Principal Investments"
THANKS IN ADVANCE,
B
View 2 Replies
View Related
Feb 16, 2007
I am building a simple task tracker to keep a record of tasks being passed back and forth between our team. The schema is fairly simple as illustrated in the image below.http://img2.freeimagehosting.net/uploads/f4f3a05c21.gifI want a recordset as follows out of this database (+/- few columns. But these are the most essential ones needed) -ixProject | Project.sTitle | ixTask | Task.sTitle | TaskHistory.dtEntry | sStatus | sEmployeeAssignor | sEmployeeAssigneeThis is basically a recordset of the history of the task (who assigned task to whom) and the status (assigned, resolved, closed). I can get the query to return the user id (ixEmployee) without any problems. But to display this in the interface I will need the employees name (sUsername).I tried the following query -SELECT Employee.sUsername AS sEmployeeAssignor, Employee.sUsername AS sEmployeeAssignee, Project.sTitle AS sProjectTitle, Task.sTitle, TaskHistory.dtEntry, TaskHistory.sDescription FROM(((Project INNER JOIN Task ON Project.ixProject = Task.ixProject) INNER JOIN (Employee INNER JOIN TaskHistory ON Employee.ixEmployee = TaskHistory.ixEmployeeAssignor)ON Task.ixTask = TaskHistory.ixTask)INNER JOIN Employee ON Employee.ixEmployee = TaskHistory.ixEmployeeAssignee <<< THIS IS THE PROBLEM LINE)WHERE (SELECT LAST(TaskHistory.ixEmployeeAssignee) FROM TaskHistory) = 1ORDER BY TaskHistory.ixTask, TaskHistory.ixTaskHistoryThis is the error I get -Join expression not supported.State:S1000,Native:-3530,Origin:[Microsoft][ODBC Microsoft Access Driver]Can someone explain how I can solve this problem? I am currently running two queries - one to retrieve the usernames, and the other to retrieve the history with the user id's - and replacing the user id's with usernames when displaying the records, but would like to have a cleaner solution.
View 2 Replies
View Related
Jan 30, 2008
Hello! I need to join 2 tables, 1 table with all of the fields, and another with just 2.
The link between the 2 tables is an ID field in field 2 wich is long integer.
The problem is, the ID field in table 1 that correlates to the ID field in table 2, is in the datatype text.
I cant change the datatype of the field in table 1 because it is a linked table to a txt file. And some of the values in there are dashes, indicating no ID. (not sure why).
So, is there ANY way to link the2 tables with the 2 different datatypes
Thanks in advance!
View 5 Replies
View Related
Jul 31, 2013
I have 3 queries named Mech Final Equipment 3 Mth, Mech Final Equipment 6 Mth, and Mech Historical Final Equipment.They all have two fields-Final equipment and Sum of Sum of Down (calculating the number of minutes each piece of equipment was down in the time period selected).
My ultimate goal is to join the three queries to display a pivot chart that uses the Final Equipment as the category field and 3 Mth, 6Mth, and Historical as seperate data fields.What I have is a join query (Which I have named Mech Final Equipment H63 Joined)
Using this SQL:
Code:
SELECT DISTINCTROW [Mech Final Equipment 3 Mth].[Final Equipment], Sum([Mech Final Equipment 3 Mth].[Sum Of Down]) AS Duration
FROM [Mech Final Equipment 3 Mth]
GROUP BY [Mech Final Equipment 3 Mth].[Final Equipment]
UNION
[code]...
Which returns a table that looks like this:
Final Equipment, Duration
Ancillary Equipment, 225
Ancillary Equipment, 401
Ancillary Equipment, 1787
Brush Unit , 1252
Brush Unit , 2519
Brush Unit , 8004
And so on.What I need the table to look like is this
Final Equipment, 3 Mth, 6 Mth, Historical
Ancillary Equipment, 225, 401, 1787
Brush Unit, 1252, 2519, 8004
And so on, like a cross tab.I tried to do a crosstab query but I don't have enough fields.
View 6 Replies
View Related
Dec 15, 2005
Hi whats the syntax for left joining more than 2 tables if each of the tables has a common column???
View 4 Replies
View Related
Feb 10, 2006
I need to do a join on two tables, one is in the current database, but the onther one is an another database. I am struggling with the syntax. Can anyone advise where I am going wrong?
This is the code, but it says the usual 'Syntax error in FROM clause'
SELECT za06_report_2006.wbs, za06_report_2006.cost_cntr, wbslist.description
FROM za06_report_2006 LEFT JOIN wbslist in '\ukdewgs002gdatapcdatafinancedocumentwbslis t.mdb' on za06_report_2006.wbs= wbslist.[wbs element];
All table names , variable names, and paths are correct.
Thanks for any suggestions
(I am very new to SQL)
View 1 Replies
View Related
Oct 25, 2006
Hey everyone. I have this situation: I work for a veterinary company and I have a project which includes 4 tables.The first one has a list of all of our clients.The other 3 are : a table for the year 2004, one for 2005 and one for 2006. Not every client visits us every year and I need a query which will make certain action (different sorts of calculations) with the number of animals that we take care of.
To make it more clear:
During 2004 one client with ID 12345 visited us with 3 animals.
He didn't show up in 2005 but he came back in 2006 with 4 animals.
Trying to do so I joined the tables properly (in my opinion one-to many) and I only get the sum if a client has visited us for all the 3 years. If he appears in just two tables or one I don't get a result.
This is the problem. Has anyone got a clue?
Thanks in advance
Kyriakos
View 3 Replies
View Related
Feb 10, 2007
Hi all,I am having problems joining two tables together.
Table1
ID Name
1 A
2 B
3 C
Table2
ID name
1 D
2 E
3 F
I would like to make the query same
ID name
1 A
1 D
2 B
2 E
3 C
3 F
Can anyone help please? It would be much appreciated.
Many Thanks, mimic
View 2 Replies
View Related
May 7, 2014
simple join my Main Data Compiling Table and Secondary Table Delete from Main Data table (Located Under main Content Tables). Basically I want it to run like a flat data table which I cannot do because I have too many fields. I want ability to further expand it in the future.Somehow I need a one to one relationship with Referential Integrity enforced with cascade Update Related fields and Cascade Delete Related fields. Problem lies in it tries to create a one too many relationship.Both Main1ID and Main2ID are AutoNumber with Main One being primary key.
I have created a tabbed Navigation Form to show what is currently happening the first Tab is from Client Demographic Form generated by fields from Main Data Compiling Table and Second from Floral Information Form generated by fields from Secondary Table Delete From Main Data Table.
View 3 Replies
View Related
