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?
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.
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
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?
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'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";
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?
[1] AvailableCars consists of fields as Zone (eg. ZoneA, ZoneB, ZoneC) , CarCategory (eg. CarAA1, CarAB1, CarAA2 ... and so on), DailyRates, and Valid DateBounds (FromDate, ToDate).
[2] CarPics consists of CarCategory and Carpicture (Path of the car image).
Now I want to Bring Only those cars from the AvailableCars table and CarPics which satisfy the criteria of falling between Valid Date Bounds...I am using join as the following :
SELECT AVAILABLECARS.ZONE, AVAILABLECARS.CARCATEGORY, AVAILABLECARS.DAILYRATES, AVAILABLECARS.FROMDATE, AVAILABLECARS.TODATE, CARPICS.CARPICTURE FROM CARPICS JOIN AVAILABLECARS.CARCATEGORY = CARPICS.CARCATEGORY WHERE AVAILABLECARS.ZONECODE = "ZONEA2XY" AND AVAILABLECARS.FROMDATE BETWEEN "DATEVAR1" AND "DATEVAR2" ORDERBY AVAILABLECARS.ZONE, AVAILABLECARS.CARCATEGORY
Using above query, I am not able to get the desired results. :o
Can Any body help me in solving this problem ? :confused:
I need to write a query which retrieves all the records from first table and those matching from second table and also satisfies a criteria.
I had
Table 1 F1 F2 F3
Table 2 F4 F5 F6 F7
I had to get all the records from Table1 and also those matching with Table2 So i did left join and getting all the records. And I also need to check whether F3 in Table1 is in between F6 and F7 of Table2.
So I wrote query like this
SELECT Table1.F1, Table1.F2, Table2.F5 FROM Table1 LEFT JOIN Table2 ON (Table1.F1 = Table2.F4) WHERE Table1.F3 BETWEEN Table2.F6 AND Table2.F7;
I am getting only those records which are matching where condition. I need to get all the records including which match the criteria and also those which don't match.
I am trying to create a query which shows only the newest note for every prospect. I have the code below, but I get a 'Syntax error in JOIN Operation' when I try to run the query and TBLNotes is highlighted.
SELECT TBLProspect.CompanyName,TBLNotes.Note, TBLNotes.NoteDate FROM TBLNotes AS a INNER JOIN TBLProspect ON TBLProspect.LeadID=TBLNotes.LeadID WHERE ((((Select Count(*) from tblNotes where LeadID=a.LeadID and NoteDate>=a.NoteDate)) In (1))) ORDER BY a.LeadID, a.NoteDate DESC;
I hope someone out there can help me, I'm not an SQL experianced person, I only know enough to make problems. Anyway I believe my problem below is probably simple to someone that knows SQL very good. I have a link to a sample Access database if anyone is interested in helping me solve this.
The Sample Database (http://shadesoffire.net/files/db1.mdb)
Have a problem and can't quite seem to get it solved. I have simplified the whole thing to make it easier to understand.
I want a report that shows me the Product Category and Sum from work orders that were > $100.
I'm not quite sure how to do this, I've tried several methods of Joining and Sub queries but nothing really works. All I get in my results is product categories > 100. See I would expect the product category to be on the report even it it were $55 if it came from a work order containing total costs > 100.
I have manually supplied the results that I want, any help would be greatly appreciated. My real problem deals with larger results and numbers but these are the basics.
Linked is an Access database with one of my queries that show all work orders > 100. I would expect all those product categories in my summary report.
Redesign of the database is not an option.
These work orders are all > 100, the total on all reports should be $511
WorkOrderNo Total Cost 00001 $120.00 00003 $268.00 00005 $123.00
These product categories are associated with the above work orders and this is the result I am looking for.
Hi all, this is my first post to the board. I’ve been searching through the forums but haven’t been able to put all the pieces together on my problem. First some background on my application: I have 3 tables ISSUES, UPDATES, and PRTS. Each record in ISSUES has several records in UPDATES, and may have a single record in PRTS. The issues and updates tables have 3 “clones” (everything except the table name prefix is the same) that are used by other departments. All of the clones use the same PRTS table. I am trying to put a query into SQL (I first built it using the query builder & an example from Microsoft for a query to find the last record) that will return each issue, along with the last update associated with the issue. My intent is to switch the table names using code depending on the user’s selection. I know how to switch “ElectricISSUES” to “PowerISSUES” below, but I do not know how to switch the source for the “qryLastUpdate”. Any help would be greatly appreciated.
issueSQL = "SELECT ElectricISSUES.Index, ElectricISSUES.Status, ElectricISSUES.Description, ElectricISSUES.Driver, ElectricISSUES.Champion, ElectricISSUES.PRTS, ElectricISSUES.SubDate, ElectricISSUES.DateClosed, ElectricISSUES.Diamond, Date()-[Subdate] AS DaysOld, qryLastUpdate.Update, qryLastUpdate.Udate, qryPRTS.Step, qryPRTS.[Next Step], qryPRTS.Designee, qryPRTS.[Ewo#], qryPRTS.BreakPointDate " & _ "FROM (qryLastUpdate RIGHT JOIN ElectricISSUES ON qryLastUpdate.UIndex = ElectricISSUES.Index) LEFT JOIN qryPRTS ON ElectricISSUES.PRTS = qryPRTS.[Prob#] " & _ "WHERE (((ElectricISSUES.Status) = 'closed')) ORDER BY ElectricISSUES.Index;" db.OpenRecordset (issueSQL)
Hi all How are you? I have a small question.... I have a db with 2 tables (Contacts, Products). Contacts table has 6 columns (CustomerID, CustomerName, Bread, Tea, CD, Pencil) Products table has 2 columns (ProductID, ProductName). I want a query that show this result: Customer NameProduct Name JohnTea JohnCD MarkBread MarkCD MarkPencil JaneTea TonyBread TonyPencil
How can I do that?? Kindly, find attached the data base. Sorry for bothering you.
Head is battered with this join query. I have 2 tables which I want to join, one being individual data and one being budget data. I want to Sum the budget Cost And Budget admissions dependant on the joins.
SELECT FyldeCoastActivity.Speciality AS [Specialty Code], dbo_Specialty.SPECTEXT AS [Specialty Desc], FyldeCoastActivity.GP_Practice, FyldeCoastActivity.[Activity Type], Count(FyldeCoastActivity.Activity) AS CountOfSUSActivty, Sum(FyldeCoastActivity.BasicSpellCost0708) AS BasicCost, Sum([PBC month 8 budget].[Budget Admissions]) AS [SumOfBudget Admissions], Sum([PBC month 8 budget].[Budget Cost]) AS [SumOfBudget Cost], [BasicCost]-[SumOfBudget Cost] AS [Cost Difference], [BasicCost]/[SumOfBudget Cost]*100 AS [Cost %] FROM ((FyldeCoastActivity LEFT JOIN dbo_Specialty ON FyldeCoastActivity.Speciality = dbo_Specialty.SPECCD) LEFT JOIN [Al Providers] ON FyldeCoastActivity.Provider_Code = [Al Providers].Trust_Code) LEFT JOIN [PBC month 8 budget] ON (FyldeCoastActivity.ProviderCode1 = [PBC month 8 budget].[Provider Code]) AND (FyldeCoastActivity.Speciality = [PBC month 8 budget].[Specialty code]) AND (FyldeCoastActivity.GP_Practice = [PBC month 8 budget].[Pract code]) AND (FyldeCoastActivity.[Activity Type] = [PBC month 8 budget].PODSUS) WHERE (((FyldeCoastActivity.[Financial Month])<=8) AND ((FyldeCoastActivity.ExcludeReason)=0 Or (FyldeCoastActivity.ExcludeReason)=99 Or (FyldeCoastActivity.ExcludeReason) Is Null)) GROUP BY FyldeCoastActivity.Speciality, dbo_Specialty.SPECTEXT, FyldeCoastActivity.GP_Practice, FyldeCoastActivity.[Activity Type] HAVING (((FyldeCoastActivity.GP_Practice)="p81129") AND ((Sum(FyldeCoastActivity.BasicSpellCost0708)) Not Like 0)) ORDER BY FyldeCoastActivity.Speciality, FyldeCoastActivity.[Activity Type];
The query is coming back with Very high figures which leads me to believe that the joins are not working and just summing all the data in the budget table.
If I were to do a right join on four different attributes E.g. a = a b = b etc etc and the criteria would be Attendance is null. I am getting the results back which I want but the columns which I have joins on are blank? Any ideas why?
I am a SQL Server user and there are a few Access differences which seem to be getting the better of me... Can someone answer a simple question? I have the following query:
SELECT tbUserGroups.f_GroupName, tbUserGroups.f_DefaultCredits, tbUserGroups.f_OverrideUsers, tbCreditSchedules.f_CreditName, tbUserGroups.f_Created, tbUserGroups.f_Updated, COUNT(tbUsers.f_ID) AS Users FROM tbUserGroups LEFT JOIN tbUsers ON tbUserGroups.f_ID=tbUsers.f_UserGroupID INNER JOIN tbCreditSchedules ON tbUserGroups.f_CreditScheduleID=tbCreditSchedules. f_ID GROUP BY tbUserGroups.f_GroupName, tbUserGroups.f_DefaultCredits, tbUserGroups.f_OverrideUsers, tbUserGroups.f_Created, tbUserGroups.f_Updated, tbCreditSchedules.f_CreditName;
This comes up saying that I am missing an operator? From browsing the forum, someone suggested putting parenthesis such as:
SELECT tbUserGroups.f_GroupName, tbUserGroups.f_DefaultCredits, tbUserGroups.f_OverrideUsers, tbCreditSchedules.f_CreditName, tbUserGroups.f_Created, tbUserGroups.f_Updated, COUNT(tbUsers.f_ID) AS Users FROM ((tbUserGroups LEFT JOIN tbUsers ON tbUserGroups.f_ID=tbUsers.f_UserGroupID) INNER JOIN tbCreditSchedules ON tbUserGroups.f_CreditScheduleID=tbCreditSchedules. f_ID) GROUP BY tbUserGroups.f_GroupName, tbUserGroups.f_DefaultCredits, tbUserGroups.f_OverrideUsers, tbUserGroups.f_Created, tbUserGroups.f_Updated, tbCreditSchedules.f_CreditName;
However, when i do that... It says i haven't supplied a parameter! As far as i'm aware i'm not trying to supply a parameter, its just a simple join.
Hi,I am very bad in queries and need your help on this one.I am trying to run a simple delete query having an inner join but it gives an error every time. The error being "Specify the table containing the records you want to delete"here are the details of the simulated table and the query than I am trying to run table1 : name number abc 1 xyz 2 table2 name cityabc AQuery that I am trying to run is:Delete Table1 from Table1 INNER JOIN Table2 on Table1.name = Table2.name;Thanks
I've have used the usual code format for creating a column of row numbers for my query, but it doesn't seem to work. Sequence: DCount("InventoryID","Chemical Location","InventoryID <=" & [InventoryID]) I got this error The Microsoft Office Access database engine cannot find the input table or query 'Chemical Location'. Make sure it exists and that its name is spelled correctly.
So I tried this: Sequence: DCount("InventoryID","qexpFacilityInventory","InventoryID <=" & [InventoryID])And I got this error: The expression you entered as a query paramenter produced this error: Business Emergency Plans can't find the form 'frmMain' referred to in a macro expression or Visual Basic code
I think my code doesn't work because its a join query; so I'm searching for an answer. I have attached an image of the query builder in Access 2007 to show my problem. Also I'm showing the SQL code. Can someone help me with putting row numbers in a Join Query "qexpFacilityInventory"? SELECT tblChemicalInventory.[Business Name], tblChemicalInventory.[Item Number], tblChemicalInventory.[Chemical Location], tblChemicalInventory.[Chemical Name], tblChemicalInventory.[Common Name], tblChemicalInventory.[Map ], tblChemicalInventory.[Grid ], tblChemicalInventory.[Largest Container], tblChemicalInventory.[Average Daily Amount], tblChemicalInventory.[Maximum Daily Amount], tblChemicalInventory.Units, tblChemicalInventory.[Storage Container], tblChemicalInventory.[Usage Purpose], tblChemicalInventory.[Annual Waste Amount], tblChemicalInventory.STATUS AS Reviewer, tblChemicalInventory.STATUS AS [Modified Yes/No], tblChemicalInventory.STATUS AS Comments FROM tblChemicalInventory INNER JOIN tblChemicalProperties ON tblChemicalInventory.ChemicalID = tblChemicalProperties.ChemicalID WHERE (((tblChemicalInventory.BusinessID)=[Forms]![frmMain]![ctlGenericSubform].[Form]![BusinessID]));
I would like to join two tables with one query. Here is the twist though. The first table would have a set of value like so:
0 50 100 300 500 750 1200 1500
the second table would have values as such
0 1000 1300 1500 1750
by just setting up the relationship to show all values from the sirt table and only those from the second table that match I get a list of values that will not work for me. What I would like to reflect in the query is all the values from the first table and if there is for instance no value of 500 in the second table I would like to return 1000. For instance there is a zero in each table so that would be output twice. There is however no value of 50 in the second table so the query would read 50 and null or blank when I would like it to read 50 and 1000 (The next value higher).
I have a table from which I want to extract selected fields from each record and write a new corresponding record in a separate table (like a summary of each record). This process will need to be repeated on a regular basis. I am using an append query so far but this appends 0 rows unless I set the join type on the query to option to 2 (I think a left outer join) - this means that each time I run the query - it appends records that have already been appended previously. What I want is the query to append only those records that exist in the primary table but don't exist in the secondary one. Can't see a join type that will do this for me - have experimented with the alternatives but it just creates more duplicates.
I am a brand new user of Access, but need to know how to do an advanced join query. I have two lists joined on one field, but the data across the two fields is not always consistent. For example, one list has a title listed as NY Branch, but another has it listed as New York Branch. How do I have access compare only a certain number of characters, rather than the entire entry?
Hi, i am having some problems with a delete query,
This is because i am using 2 tables in the query
TBL_12a_Requisition_Sub
and
TBL_12_Purchase_Requisition_Sub
The 2 tables are linked by fields both named [Requistion Number]
Basically where the 2 tables match i would like to delete all records in TBL_12_Purchase_Requisition_Sub. The query works fine when in design view but when running it you get the error message (specify the table containing the records you want to delete). The problem with this is i have already done this in the query.
I have looked over the net and it looks like you need to do something with a select statement in sql, but i can't figure it out. Any help would be much appreciated. Thanks.
I am trying to do an update on a table with a query which has a join as follows but getting an error:
UPDATE RoleObjects SET AccessType = 1 FROM RoleObjects INNER JOIN Objects On Objects.ObjectID = RoleObjects.ObjectID WHERE (RoleObjects.RoleID <> 1) AND (MID(Objects.ObjectName, 4, 2) = 'SR')
I tried to debug the code and found that the following part of the code with the Select clause works fine.
SELECT * FROM RoleObjects INNER JOIN Objects On Objects.ObjectID = RoleObjects.ObjectID WHERE (RoleObjects.RoleID <> 1) AND (MID(Objects.ObjectName, 4, 2) = 'SR')
