Hi,
i've got a query that is linking 2 tables.
i'm having problems with the join properties.
basically from what i can understand, there are 3 options.
1. Where join field from both tables are equal
2. All records from table 1 and only those from table 2 where they match
3. All records from table 2 and only those from table 1 where they match
how do i go about having all records from both tables showing?
I have 2 tables. one (Data_Participant) has all of the data on individual participants. the other (Data_Main) has the data of how they participated per year. my Data_Participant table has a primary key (ParticipantID) which does not allow duplicates and the Data_Main table is setup w/ a non-primary key field (ID) which will allow dupes. these two fields are joined in my query with the Data_Participant.ParticipantID set to include all records and and only those records from the Data_Main.ID field where the records are equal.
to me, the join properties being setup like this would allow me to put any type of search criteria i would like under the Data_Main table fields and i would still get all of the Data_Participant's tables as a result, but only those from Data_Main that i had filtered.
ultimately, i'd like to see all of my Data_Participant data and only the Data_Main data that makes it through my filter/search criteria. am i not understanding how the join properties work?
Okay, I apologize, I'm not extremely Access knowledgeable so bear with me.
I have an Access database I created which I thought was working okay, then I realized it was missing one vendor because we haven't started writing checks to that vendor yet and probably won't for a while.
I am using a make-table-query to create a table that combines data from two linked tables. The two linked tables have a matching field (VendorID) one linked table is "History" and the other table is "Master" (these tables are SQL data from MS Great Plains if you need to know that).
Now my problem ... not every VendorID in Master table is present in History table.
I first created the join statement to say use all records in History and only those records in Master that match. This works without any error statements, however, I discovered there is a significant field of detail that is being omitted because a VendorID is in Master, but since no checks or invoices have been created for that VendorID it does not exist in the History table yet.
I decided to change the join properties to say "include all records in Master and only those records in History that agree". When I try to run the query I receive the error message "data type mismatch". I feel certain it is because several of the fields have no information to draw from on the vendors that don't exist in the History table.
What I need the query to do is go ahead and use the information from the Master table and when there is no matching VendorID in the History table use a zero for those fields.
I used to program and know a little C+. I understand "if" statements and feel like a sql if statement could work around this (for example, if History.VendorID does not exist zero, else History.[fieldname], but I don't know how to go about doing it. Any help for a novice would be much appreciated. Thanks in advance
I have table1 with fields 'project', field1, field2, field3 and table2 with fields 'project', fieldA, fieldB, fieldC. I need a query that has all fields from table1 and only fieldA from table2. What kind of join property should it have in order for me NOT to have duplicates? 'project' field in both tables has the same values.
As always, any help is greatly appreciated. Thanks.
I am trying to do the good 'ol sales report (query) to include customers with no sales.
I have a customers table, account number table, sales table & sales (line) detail table. (all linked in that order)
If I run a query to show customers (in the customer table) with account numbers, that works
An unmatched query to show customer without an account number works (but of course the unmatched account number field isn't shown).
How can I get the two two be shown together with the "unmatched" having a null or 0 for their account number?
I am guessing in principle, the resulting solution can be modified to show customers without sales alongside those with sales?
Hi everyone,
i am quite a begginer at this so sorry for the simple question
I am running a access query from a vba code. IN the vba code I am writing the sql command.
I am getting the criteria for the sql code (WHERE command) from a number of combo boxes in a form.
The whole thing works quite well when I have something selected on all combo boxes but it does collapse when one of the combo boxes has the default value which is "" nothing.
for instance when I have
WHERE domicile.IdDomicile = 5
works fine. 5 comes from the combo box
when there is nothing selected in my combo box the sql code would be
WHERE domicile.IdDomicile =
and then I get an error msg saying the query is wrong.
Question,
what is the command in sql that instruct the query to return all records.
I tried using "*" when the combo box was not selected but then I dont get the records that are blank, only the ones with something writen in it.
Any other suggestion for this
Many Thanks
K Regards
if its possible to have a button that after clicking it, it doesn't show certain records. Reason being I have a "shows" for each month on our personal site and amazon, and we've been selling on those for about 2 years, and I have a "show" for each month. But, that quickly clutters up the list box. Any way to create a button the (after typing in the name) it doesnt show only those record(s)?
View 2 Replies View RelatedI have a DB of books and a form to search for multiple fields including ranking (combo box). The way I have it now is to pick "3 stars", "4 stars", "5 stars". What I want to do is have it so that if I pick "4+", it will show all records that have 4 stars and 5 stars and "3+", it will show records with 3, 4, 5 stars.
View 14 Replies View RelatedI am setting up a database to hold staff details, and would like a query to show each member of staff's total hours and FTE.
Staff name etc is in tblStaff
Staff shift details are in tblShifts linked via staffID
tblShifts will contain details of the shift worked on each day of the week, but the majority of our staff work a standard shift - e.g 8-4, 9-5. Therefore what I wanted to do was in tblStaff set a field named shiftPattern to 1 2 3 or 4. 1 indicating a custom shift, and any other number indicating a set shift defined in a separate table.
The problem I have is that my query only returns people who have details in tblShifts - regardless of their shiftPattern value. If I enter a blank record in tblShifts it will do the above as intended.
Hi
Bit of an Access beginner and am trying to sort something out for work - not sure why they've asked me!
I've created a query to search on a couple of items using drop down boxes on a search form I created. This bit of it works fine, I used this site http://www.fontstuff.com/access/acctut08.htm and copied what he had done. This is fine.
I now want to add a date search to the same query. I know I can use Between [..] AND [..] but if I leave the boxes blank it finds no records. I'd like it to search and include all.
Ideally I want to include 2 extra text boxes on my form that I can put a to and from date in (or not put a date in and it find everything).
Hope that makes sense, please can someone do me an idiots guide?
Many thanks
Phil
Three tables:
Employee, Sessions, EmployeeSessions.
Many sessions can have many employees - thus the joining table has been included.
When trying to delete an employee from the database using a form, I encounter the error:
The record cannot be deleted or changed because table 'tblEmployeeSessions' includes related records
Is there a problem with my table relationship structure? Or is it 'correct' that as the employee is supervising a session he/she cannot be deleted as this would interfere and maybe mess up the session record?
Hi, I would like to change the properties of either the column heads in a combo-box or the column names for tables. I don't think there's any way to adjust the column head properties, and I'm doubtful there's a way to change the column caption properties.
Some of my column titles are long, and I'd like to be able to word wrap them essentially. Anyone know if this is possible? Thanks.
I have a form that uses the following join as its control source:SELECT * FROM [Purchase Order]
LEFT JOIN [Work Orders]
ON [Purchase Order].WorkOrderID = [Work Orders].WorkOrderID;
I also enclosed a small picture of the relationship between the two tables. This is the problem I'm getting when using my purchase order form.
What I Want:
When I type in the "Work Order #" it will lookup the most recent work order with the same number and put its ID into the purchase order record.
What I'm Getting:
When I type in the "Work Order #" a brand new work order is created and the "Work Order ID" field gets the next number in the sequence.
Make note that I'm not using code for any of this. This is happening all on its own. I think the join is messing things up here. Any ideas?
I inhereted a database, and i know nothing about access. I'm trying to add a new payment record and I get this error: cannot add record(s); join key of table 'tblPayments' not in record set. I have never created or updated access before.
View 1 Replies View RelatedBasically, I want to be able to click on the New (blank) record button, and then start adding in my Attraction, Date, etc fields in the main form, however I keep coming up with the error:
Cannot add record(s); join key of table 'OrdersList' is not in recordset.
I've gone back and checked all my relationships, and the query the form is based on, and all appears to be working there fine. I can manually enter information on to the tables & queries just fine, (but obviously I don't want other users to be able to see these).
I'm wondering if it's something to do with the Auto Number, which is also my PK in table 'OrdersList'. As the first design of this database had this field set to a Text field and I would manually enter the next sequence and I didn't have any issues adding new Orders to the form.
The form is 'Orders', which is based on a query called 'Orders List'.
I want a query that gives me something like this from 2 different tables:
table1 - AA, AC, DE
table2 - AA01, AA02, AA03, AC01, DE01, DE02
query -
column 1 - column 2
AA - AA01
------ AA02
------ AA03
AC - AC01
DE - DE01
------ DE02
Is that possible?
I have a form that gets its info from a query, I would like to be able to add a new item, Customer, etc, etc. But, when I try to add one (I have a button using VBA code, ill post that at the end) it gives me the error
Code:
Cannot add record(s); join key or table tblitems not in recordset.
Code:
Private Sub cmdEventNewI_Click()
Me.Visible = False
DoCmd.OpenForm "frmItemsEdit1", acNormal, , , acFormAdd, acDialog
Me.Visible = True
Me.lstItems.Requery
End Sub ' cmdEventNewI_Click
I have 2 tables that are joined by a many to many table:
tblProductInfo
- ProductID
tblProductLinerMM
- PLProductID (FK to [tblProductInfo].[ProductID])
- PLLinerID (FK to [tblLiner].[LinerID])
tblLiner
- LinerID
I have a range of products that each use 2 liners. An inner liner and an outer liner. I need to add 2 records per product to the tblProductLinerMM table.
for example
tblProductInfo has the following records:
- 2138557
- 2378954
- 4387657
tblLiner has 2 liners in particular that relate to these products:
- L5475
- L5468
I need to create the following records in tblProductLinerMM preferably with the use of a query :
- 2138557 | L5475
- 2138557 | L5468
- 2378954 | L5475
- 2378954 | L5468
- 4387657 | L5475
- 4387657 | L5468
Hi all,
I have the following issue:
In my job we work with several raw data .txt files exported from Oracle ERP system. These data include information about: inventory, sales, backorders, purchase orders, forecasts, product line.
My goal is to put information from all of these imported txt files together to create an easy to use snapshot file.
The common field between al of these files is the item description. I have an issue where records are repeated for several fields every time the same item description is showed. For example for the same product description the january sales forecast QTY is repeated on several rows because there are several orders for this product description. Is there a way to make forecast QTY appear only once but keep the multiple orders and their information?
Example of current result
http://img178.imageshack.us/img178/5659/currentsu7.th.png (http://img178.imageshack.us/my.php?image=currentsu7.png)
to achieve result
http://img179.imageshack.us/img179/5100/toachievefk4.th.png (http://img179.imageshack.us/my.php?image=toachievefk4.png)
thank you for any comments
I have a list of PC SN#s in tbl1;
I have a list of other PC SN#s in tbl2
I want to display ALL records of tbl1, AND ALL records of tbl2, matching up the SN#s (where there is a match) - but I want to display all records whether they match or not.
How can I accomplish this?
In Access 2013 I have a query that join two tables, Conduminium Owners to their possetions (fractions of conduminium).
Owners.OwnOwners.Vote SumOfTblFracPerc
O; P................................No............... .9,54
F....................................Yes.......... ......5,48
L; Q................................Yes.............. ..7,13
M; U...............................Yes............... .7,86
H...................................Yes........... .....5,71
D; R...............................Abstention......7, 78
etc...
This query is grouping and summing, and I want put the results in a form and see it grouping the fractions in lines like this:
"These were the votes of the owners of fractions:
O;P, voted No = 9,54
F, L;Q, M;U, H, G;W, C, N, I, voted Yes = 37,72
D; R - voted Abstention = 7,78
How can I do it?
I have a simple nested query that is not working as expected. My inner query returns 102 records but when I run with outer query I only get 96 records. Below is my query, I don't really want to pull the same fields from both tables but I was doing to test. The values that are missing are those that don't exist with the monthenddate 8/31/2014 - a left join should fix that but doesn't seem to be working ..
Code:
Select distinct a.entity, a.gl_account,a.profit_center,[Open Items_1].profit_center,[Open Items_1].gl_account,[Open Items_1].entity
from(
SELECT DISTINCT [Open Items].entity, [Open Items].gl_account, [Open Items].profit_center
FROM [Open Items]
)a
left outer JOIN [Open Items] AS [Open Items_1] ON
(a.profit_center = [Open Items_1].profit_center) AND (a.gl_account = [Open Items_1].gl_account) AND (a.entity = [Open Items_1].entity)
Where ([Open Items_1].MonthEndDate=#8/31/2014#)
I have two queries. The unique key in both queries is GUID for katalogposition.
One is showing me records which has an product end date (Produkt slut dato) between today and end date of next month. This query works fine and is called q_termination.
The second one shows me unmatched records in the first query (q_termination). The query works fine and is called yq_NonTermination.
The goal is now to show me records from the first query "q_termination" that fullfill one of two criterias.
1. No match in second query "yq_NonTermination"
2. Match BUT product end date (Produkt slut dato) is greater than the match in "yq_NonTermination".
I have made a left join query on the field "Dublet_Lagervarer". From the join query the goal is to show me only q_Termination.Guid for Katalogposition number 47 and 134008.
How can I do that? Is there another way to do it? Please see attachment.
I've been toiling with the issue of WHERE clauses on the "Right" side of Left Joins. I'm aware that you need to use JOIN ON......AND.... rather than JOIN ON....WHERE.... if the WHERE relates to the Right Hand table.
I've even got an example in my DB where the above works, but now am struggling to use the same theory for other tables. Therefore, I went and created two Mickey Mouse tables to test the logic but am getting an error.
I have
Table 1 with one field called Field 1 - values A, B, C
Table 2 as follows
Field 1.....Field 2.......Field 3
A.............100
C.............200..........XXX
I hoped to have a query that finds all records on Table 1 and records on Table 2 where Field 1 matches on the two tables and Field3 = XXX
My SQL is
SELECT Table1.Field1, Table2.Field1, Table2.Field2, Table2.Field3
FROM Table1 INNER JOIN Table2 ON Table1.Field1 = Table2.Field1
AND Table2.Field3="XXX";
but I get Join Expression not supported
What am I doing wrong?
Thanks
Andrew
And then called this join as a symbol or variable, and then have it use to select the items from these joined tables, can this be done in Access? Here is an example of a code that I created, but it has an error message saying the FROM syntax is incorrect.
Code:
SELECT firstJOIN.trainID, firstJOIN.trainName, firstJOIN.stationID, firstJOIN.stationName, firstJOIN.distance_miles, firstJOIN.time_mins
FROM (trains INNER JOIN ((station INNER JOIN lineStation ON station.stationID = lineStation.stationID)
INNER JOIN bookingLeg ON bookingLeg.startID = station.stationID or bookingLeg.endID = station.stationID )
ON trains.trainID = bookingLeg.tid) as firstJOIN
Can Access do something similar to this, in the FROM statement I joined 4 tables, because each unique fields are in each table and I have to joined them to get those fields. I called this join firstJOIN and in the SELECT statement, I list those columns in the table by calling it firstJOIN.trainID. Can Access do something like this, but syntax it differently?
I am trying to do an inner join with a left join. The only problem is, I want to inner join the table that is being joined. This is how I thought it would work below, but it doesn't work.
SELECT * FROM ((Submissions LEFT JOIN Candidates ON Submissions.`Candidate Code` = Candidates.`Candidate Code`) INNER JOIN `Type Candidate Status` ON Candidate.Status = `Type Candidate Status`.`Status ID`)
WHERE Submissions.Status <> 7 ORDER BY `School Interest` DESC;
I want to get the Candidate.Status to inner join with the `Type Candidate Status`.`Status ID`.
If you can help, thanks in advance.
Dave