I have a table that is a list of all of my events. Each record of events should have a child table that list all of the things that happened at the event. When a new record (event) is added how can I have a new child table created and linked to that record. Also I have a blank table to serve as a template for what each child should look like. How do I make sure this occurs? The child tables can have the same name as the index. I am just using numbers 1- for the index with 1 being the first event and so on?
I been tasked with a project to be written in access which I am rather unfamiliar with, web design is more my area.
For part of the project I need to copy some pricing fields from a pricing table to a new record at the point that the new record is created. The new record is created when a 'new form' button is clicked.
What I would like to know is what and where I should trigger the copy query from and what function should I be using?
why is it that as soon as you fix one problem another one needs dealing with :mad:
OK, the problem i have is that i have a BE/FE configuration database in a multiuser environment. I have built all the tables and the relationships in the BE configuration. In the FE configurations i have built the main forms to input data. It has no come to the point where i have begun to create the queries to allow reporting and data searching. And this is where i have a problem. I am trying to create a simply query and i do mean simple (at the moment i simply want 2 fields from 1 table and 2 fields from another table) no calculations or anything i just want it to display the data. I have tried creating this query numerous times and i keep getting the same error.
"The wizard is unable to open your query in datasheet view, possibly because another user has a source table open in exclusive mode. Your query will be opened in design view"
the above is the error i get when i use a wizard to create the query, after getting this message and going into design view i try to view the results in datasheet view and get the following error "Type Mismatch in expression"
If i dont use a wizard and create it in design view, when i go to view the results i get the second error message everytime.
If i create a query from 1 table only the query works without errors, but i always get errors when i have more than one table in the query.
Also i am the only person with the database open so therefore it is total impossible for the database to be open by another user in exclusive mode.
Anyone know what the problem might be or how to fix. Your answers will be very much appreciated.
I have an issue with designing a query.i have three tables (there is more but these should do for this)
Game Review reviewer Game
i need a query that will only select games that the reviewer has not reviewed.its to populate a box on a add review form so that when a reviewer has been selected, the next drop-down will only show games that have not been reviewed yet by that reviewer.
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 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?
