I am working on a "course registration" system using asp (vbscript) and MS Access. There are 4 tables as shown:
activity (1 entry per course - key is ActivityID)
activitysections (1 entry for each course section for an activity - key is SectionID - ActivityID is also in the record)
registrations ( 1 entry for each registrant in the section - key is RegistrationID - Section ID and PersonID are also in the record)
users ( 1 entry for each student with their demographic info - PersonID is the key)
When I run the following query on the test Access database:
SELECT activity.ActivityName, activity.ActivityStatus, activity.ActivityID, activitysections.SectionNumber, activitysections.SectionStatus, activitysections.SectionSchedule, activitysections.SectionCapacity, activitysections.SectionParticipants, activitysections.SectionDuration, users.FirstName, users.Initial, users.LastName, users.NameSuffix, users.PreferredName, users.Gender, users.BirthDate
FROM users INNER JOIN ((activity INNER JOIN activitysections ON activity.ActivityID = activitysections.ActivityID) INNER JOIN registrations ON activitysections.SectionID = registrations.SectionID) ON users.PersonID = registrations.PersonID;
I get the correct results - 1 entry wherever there is a registrant for a section of an activity.
However, I want to run a query that lists all the sections regardless of whether there is a person registered. To do that I changed the query to a left join as follows:
SELECT activity.ActivityName, activity.ActivityStatus, activity.ActivityID, activitysections.SectionNumber, activitysections.SectionStatus, activitysections.SectionSchedule, activitysections.SectionCapacity, activitysections.SectionParticipants, activitysections.SectionDuration, users.FirstName, users.Initial, users.LastName, users.NameSuffix, users.PreferredName, users.Gender, users.BirthDate
FROM users INNER JOIN ((activity INNER JOIN activitysections ON activity.ActivityID = activitysections.ActivityID) LEFT JOIN registrations ON activitysections.SectionID = registrations.SectionID) ON users.PersonID = registrations.PersonID;
I get this wonderful little message saying "join expression not supported". Can anyone give me some advice on what I am doing incorrect. Thanks.....
ContentTypes ---> Site Content Directory Cats ---> Categories Sierra ---> Store Content for Sierra BrushStrokes ---> Store Content for BS
I have "tried" to create a QUERY for the 4 Tables (The code that I have, originally came with 3-Table Query connection I have added in the 4th Table "BrushStrokes")
This is the Query that I created. Before the code, a little insite
At first there was: RIGHT & LEFT JOIN's, But I would receive this: Error 3258 After not really finding any good examples of how to create the 2- Queries and have then work together as one. I decided to try something else, which was to create all [INNER JOIN]'s Which got rid of the Error, and the code still will not work in my site. ======== Microsoft JET Database Engine error '80040e14' Syntax error in JOIN operation. =======
So, here is the code, please let me know what is wrong with it? And if possible, a good example of creating the [2 - Queries] that work as one.
Thank you all Carrzkiss -----------------------SQL QUERY------------------ SELECT Cats.CatID, Sierra.CatID, BrushStrokes.CatID, Sierra.ContentID, BrushStrokes.BSContentID, Sierra.Product_Title, BrushStrokes.BSProduct_Name, Cats.CatDescription, Cats.CatTypeId, Sierra.Product_Th_Image, BrushStrokes.BSThumbnail, Sierra.Our_Price, BrushStrokes.BSPrice, Sierra.Retail_Price, BrushStrokes.BSRetail, Sierra.Aff_Name, BrushStrokes.Aff_Name, Sierra.Brand_Name, BrushStrokes.BSArtistName, Sierra.YAvailable, BrushStrokes.BSStatus, Sierra.Percent_Savings, Sierra.Unique_Product_ID, BrushStrokes.BSProductID, Sierra.Main_Cat, BrushStrokes.BSCol_Cat, ContentTypes.ContentType, Sierra.Product_Description, BrushStrokes.BSDescription FROM ((ContentTypes INNER JOIN Sierra ON ContentTypes.ContentTypeID = Sierra.ContentTypeID) INNER JOIN BrushStrokes ON ContentTypes.ContentTypeID = BrushStrokes.ContentTypeID) INNER JOIN Cats ON (BrushStrokes.CatID = Cats.CatID) AND (Sierra.CatID = Cats.CatID) WHERE (((Sierra.Display)=1) AND ((BrushStrokes.Display)=1)); -----------------------------END SQL QUERY------------------
This is my query:- SELECT DISTINCTROW Customer.custName, Sum(Invoice.invAmount) AS SumOfinvAmount, Sum(Payment.payAmount) AS SumOfpayAmount FROM (Customer LEFT JOIN Invoice ON Customer.custID = Invoice.invCustID) LEFT JOIN Payment ON Customer.custID = Payment.payCustID GROUP BY Customer.custName, Customer.custID;
Result:- custName SumOfinvAmount SumOfpayAmount blake $20.00 julie $50.00 kate $375.00 $290.00 steven $200.00
The resulting SQL query returns incorrect invoice and payment totals for Kate. The Sum() function has duplicated. Can anyone tell me how I would change my query to fix this problem?
Orders ====== OrderID = AutoNumber OrderDate = Date/Time OrderInfo = Memo CustomerName = Text CustomerAddress = Text PaymentDetails = Text
OrderProductDetails ========= OrderNumber = Number (Linked to OrderID) ProductNumber = Number (Linked to ProductID) Notes = Memo (Notes on product customization)
The OrderProductDetails table is in many to one relationships with both Orders and Products table. If I place an order with 3 products, the OrderProductDetails datasheet would be:
On my report, instead of 3 entries for this order, I would like show just one entry under a column "Products Ordered" ---> Product 1, Product 2, Product 3.
I tried the Concatenate macros I found online, however, I am unable to configure them properly to use for this purpose.
Here is my current table structure (I have omitted some fields from this example and have given some sample data in italics to make the table structure more clear.
tblEmployees
ID (autonumber) 3 EmployeeName John EmployeePhone 555999555 EmployeeLocation New York
tblClients
ClientID (autonumber) 1 , 2 , 3 ClientName ABC Company , XYZ Company, PQR Company fkeyLocationID
tblLocations LocationID 1 , 2 Location New York , Chicago
The junction table tblEmployeeClients only stores ID of the Employee and in the second column (which is a multi-valued field), the ID of each of the clients the employee Supports.
I am trying to generate a report that lists say, EmployeeName alongside the clients supported by the Employee (listing the client location is not required, however, it would be good to know how to do that as well).
The report (for the example above), should look like this:
Name Clients Supported John ABC Company, PQR Company
Currently, I am able to get :
John 1, 2 i.e the client ID for the clients that the employee supports instead of the corresponding company names.
I have to join multiple values into a string for summarizing data on reports and exports. This process in vba is taking up to 10 minutes to process and will get worse as the size of these reports grow.
My method so far is to query the individual items into a recordset, loop through the values, adding them to the string then return the string in the query.
Here is an example:
Public Function SO_Description(intSO As Integer) As String Dim db As DAO.Database Dim rs As DAO.Recordset Dim strSQLSelect As String SO_Description = "Profiles: " Set db = CurrentDb
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?
I have a table that is basically a survey form. The same series of options was available for 35 questions, and the table used to have a text string written for each answer. Because of all the repetitive data, I created a second table that assigned a number value to each of the nine possible options in these 35 separate fields. What happened is that, instead of the same text strings repeated over and over (and taking up real estate), now each of the 35 columns had a single number in them.
Now comes the day of reckoning and TPTB want a query with the raw data and the original text strings back in instead of the numbers. I was thinking doing something along the lines of a DLookup, but I can't seem to make that work in a query correctly. Apart from calling the same table and linking it over and over to the different fields in the original data table (see photo for how insane that is).
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`.
I having a little trouble with a query I was hoping someone could help me with. Basically I have the following SQL statement
[code].....
I would like to show all records for the tblCService.CService field however it won't let me change the join to a RIGHT JOIN. I get an "ambigious outer joins" error message. As far as schema tblEList is the main table and the tblCService is a lookup table. Any ideas?
hello everyone,Please can someone tell me if it is possible to create a Join on two tables where the value from 1 field is contained within a field from the other table.eg.table1.field = "xyz"table2.field = "the xyz super thingy"This is the SQL it gave me orrigionally but this is an "= to" join which is not what i want. I tried replacing the = with a LIKE but am still getting the same results.SELECT table1.*, table2.*FROM table1 INNER JOIN table2 ON table1.modelno = table2.name;SELECT table1.*, table2.*FROM table1 INNER JOIN table2 ON table1.modelno LIKE table2.name;
I have a database with PCs and installed hardware. An external program scans all PCs, the result of the scan can be saved as Access file. Now I need a query to select from each PC the installed hardware (Monitor, Printer...) but only with the latest date. I have written a query MaxDatum to select the the lastest date. Here's my query code:
SELECT [Table1.needed fields], [Table2.neede fields]... FROM (((Table1 INNER JOIN (Table2 INNER JOIN qryMaxDatum ON (Table2.Field1 = qryMaxDatum.Field1) AND (Table2.Field2 = qryMaxDatum.MDate)) ON Telle1.Field1 = Telle2.Field1) INNER JOIN Table3 ON Table2.Field2 = Table3.Field2) INNER JOIN Table4 ON Table2.Field2 = Table4.Field2) INNER JOIN Table5 ON Table2.Field2 = Table5.Field2 ORDER BY Table1.Field1;
My problem: this query mulitply the number of data records so that I have 80 data records with exactly the same content per PC. How do I neesd to change the query to get only one data record per PC?
Now, in tblDailyTripLog.driver, the tblDriverID is stored. Same goes for Destination, Depot & Depart, the tblDepots.ID gets stored which can or will differ....
Now, when do a query, how would I show 3 different joins to the same table (ie. tblDepots) to get the name, instead of the ID for Departing, depot & Destination?
I've tried doing a Left Join and then another LEft Join, but its not working....
it has already 2 inner joins can i use one more inner join within.
strSQL = "SELECT distinct z.location_id,z.location, y.company_id,y.company_name,w.category_id,w.catego ry FROM (appointment_detail AS x INNER JOIN location_table AS z ON x.location_id = z.location_id) INNER JOIN company AS y ON x.company_id=y.company_id ( INNER JOIN category AS w ON x.category_id =w.category_id)WHERE x.expiry_date >=date() ORDER BY z.location ,y.company_name"
INNER JOIN category AS w ON x.category_id =w.category_id
now I need to loop through the whole customer table and pull up all customers where the count of the following statement is more then 1
select count(geographic) as mycount from dialcodes where [phone1]like code + % or if phone2 is not blank then the count of the following statement select count(geographic) as mycount from dialcodes where [phone2] like code + %
I want to return all records from the customer table where the phone fields don't match the beginning codes from dialcodes.
I'm trying to create a database of Publications (instruction manuals) where one publication is a modification to a parent modification. I'm storing all 'publications' in one table for better maintenance. This means that I have to use a Self-Join if I want to make one the parent, and one the child. Fundamentally this is no different than the Employee/Supervisor example I've found in many places:
http://www.databasedev.co.uk/self-join_query.html
I created a query with two copies of the same table with the 2nd one renamed for easier viewing. The query final comes up, but when I choose "ParentPub", it doesn't give me a drop down showing the other pubs so that I can choose one to be the parent.
What did I do wrong? I should be able to open up the table 'tblPubs', and get a drop down menu showing me all the pubs I can assign as a parent.
Hi, I was wondering if someone can give me a possible solution to my join below:
select tableA.name From tableA, tableB where tableA.id IN (Select id from table c where .......) <--I would like tableB.prodId to make a join with the tableA.id's that are in this select sub query
HELP!!! :confused: I do have a 5 tables 4 tables do have a foreign key of the main table. I join the 4 tables with the main table but when I am editing the information I cannot edit it. and no error appears. so I am just wondering what happen with my joined tables? please help me!!!!Thank you in advance
How do I join two tables. I have a table and a lookup table. My table has products on there that are listed as custom or basic. I have a look up table that has an ID for basic and Custom. In my table, i want it to read what the id is for each product instead of it reading "basic" or custom. Someone said that I need to join the two tables and do an update query, but I don't understand how to.
That is the question: Whether 'tis nobler in the mind to suffer with VBA Programming and the outrageous errors, Or to take up arms against a sea of Access troubles.
Sorry I was in the mood for Shakespeare.
A quick summary first: In the attached file I have my Relationships. One main table, Workorders with various one-to-many relationships back to their respective tables. If you look at the attachment and see a field with 'wrk' that's my foreign key.
What I'm trying to do is this: Where-ever there is a 'wrk' field I want to add in all the fields (minus the ID Primary Key) into the main Workorders Form.
Then on the form itself I should just be able to enter in the data that is required for the fields.
So here's what I'm thinking for the coding is to Join the various tables to thlet me know if I'm on track or not. I'll start with a small one, because if I can get that right, the rest should be simple.
SELECT Model.ComputerID, Make.ComputerID FROM Computer INNER JOIN wrkComputerID ON Model.ComputerID = Make.ComputerID;
