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 have a database tracking customers and inventory, and I've tried to add in a feature where I can meet customers needs with future inventory. Key to this process is three tables:
tblCustomer - containing my customer information tblInventory - with inventory data including a part number tblNeeds - a table I've created for this purpose, containing CustomerID and Part Number.
Here's the premise: when speaking with a customer, I may learn they want a product I don't currently have in inventory, or may have never had in inventory, but that I may get in the future. I do this "on demand" in the form of a report, and it works really well ... except:
I have found that several similar products from the same supplier have similar product part numbers. What I would like to do is be able to match my query on a fractional part of the part number. In all cases, the part number would be the same over the first six characters (in a text field). Any ideas how I might do this? In my current query, I use a join, but I can't do that for fractional strings....
I've attached the query I am currently using (and running a bound report from this query):
SELECT DISTINCT tblCustomer.CustID, tblCustomer.CoName, tblInventory.ProductDescription, tblInventory.ProductPartNumber, tblInventory.UnitsAvailable, tblInventory.ProductID FROM tblInventory INNER JOIN (tblCustomer INNER JOIN tblNeeds ON tblCustomer.CustID = tblNeeds.CustomerID) ON tblInventory.ProductPartNumber = tblNeeds.PartNo WHERE (((tblInventory.UnitsAvailable)>0));
And what I want to do is match the tblInventory.ProductPartNumber on the tblNeeds.PartNo by matching only the first six characters....
Code: SELECT tblData2.Prefix, tblData2.LineNum, tblData2.Year, tblComments.comment, tblComments.Address FROM tblData2 LEFT JOIN tblComments ON tblData2.LineNum = (NumbersOnly([tblComments].[Address]) WHERE (((tblData2.MyYear)<1990)) ORDER BY tblData2.LineNum;
The NumbersOnly function returns a set of numbers from a custom formatted string. The above fails on the join (I think) but maybe there's another way of doing it?
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'm having multiple problems with my database like things such as -
i'm currently working on the Query 2 - On the Phone database (ignore Query 1) and i want to search for multiple plot numbers preferably in one parameter prompt with a comma to seperate numbers. (this could be a multitude of numbers so i would like to be able to input as many as needed). Also when i do search on this query since the Criteria is a 'Between' Value i would expect everything between the 2 numbers input to show up - but a lot of numbers out of the range show up too - why is this? (The Numbers are like "69 to 136" and they will show up - but 1-69 and 136-170 would too
I would also like to implement the search results from Query 2 into the Form i currently have made but it just opens up a access table when the search is made?
i cannot link my database as it is too big for the server - But here are the Criteria for Query 2:
Plot No - (criteria = Between [Enter First Plot No:] And [Enter Last Plot No:]) Site - (criteria = Like "*" & [Enter Site:] & "*") Product - (criteria = Like "*" & [Enter Product:] & "*"
The Query is the one im most concerned about , i can live without a form.
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 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 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 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')
I'm going to try and explain this the best way I can so please bear with me if it doen't make total sense.
I have two tables
tblSales (is used when am employee makes a sale) employee date tier level
tblTierLevels (shows all the different tier levels and the pay amounts) tier level pay amount
I want to create a report which groups by employee and shows every tier level even if they didn't sell any of those tier levels. For example... Jane Doe Units Pay Total Pay Tier Level 1: 0 $5.00 $0.00 Tier Level 2: 2 $6.00 $12.00 Tier Level 3: 0 $7.00 $0.00
If I create a query with a join to show all from tblTierLevels and only those from tblSales where tier level = tier level if the employee did not sell any of a certain tier it does not display their name. So I can't group by name and show all tier levels.
If I create a crosstab query this will show all employees as well as all the tiers however this lists the tier levels across (as columns) I want to list the tier levels as rows.
