Code:
SELECT purchaseOrder.poId, purchaseOrder.poYear, purchaseOrder.poDate, purchaseOrder.userId, purchaseOrder.currencyId, purchaseOrder.poSendDate, partnerDetails.name
FROM purchaseOrder INNER JOIN (orderDetails INNER JOIN partnerDetails ON orderDetails.partnerDetailsId = partnerDetails.partnerDetailsId) ON Cstr(Format(purchaseOrder.poId,"00000")) = Nz(MID(orderDetails.poNo,6,5),"0");
Simple join query gives dup entries also, i need in single entry.When using Distinct query for the form control source, the record is uneditable, can i get the records without dups.
I've got a form with a combo box where I choose an employee and according to that employee's attributes are shown. Some of the attributes are stored in the same table with employee names, some of it is connected by foreign key to other tables. This form is not editable, information only. I've got a button "Edit employee" which opens another form intended to edit the information. I put text fields there by dragging the fields from tables. And so, for some reason, the only editable fields in that form are those connected by foreign key to the main table, but the main table fields are all not editable and error "not in dataset" comes up.
im making a system and part of that system is the booking of the library
2 classes can be in the library at once i want a way where if a 3rd teacher tries to book a slot then theyll be told its not available if only one class was allowed itd be easy, by using a compound key but cos 2 classes are allowed this is a bit hard for me lol trying to think of a way to allow 2 bookings but not more any ideas? thanks
I have an bound textbox, what I want is that when the user selects a value from the combobox, he should not be able to edit it afterwards or as a mater of fact before the selection of the values(Uneditable combobox).
I am creating a small database for a budget and Im trying to create 3 cascading combo boxes on a form (areabox2 , devbox2, entitybox2) . I have it right now so that that
devbox2 is based on areabox2 and entitybox2 is based on devbox2 BUT!!!!!!! I want entitybox2 to be based on both areabox2 and devbox2
Parameters are :
Table = Budget Info areabox2 data = [Budget Info].[Project Area] devbox2 data = [Budget Info].Development entitybox2 data = [Budget Info].Entity
I have a form with various input boxes, three of which are combo boxes. These combo boxes need to affect one another. So let's call these cmb1, cmb2 & cmb3.
Hypothetically let's say:
Within cmb1 the list is A, B, C & D
If the user was to choose A, cmb2 would show the options 1, 2, 3, 4, 5 etc.. If the user was to choose B, cmb2 would show the options 2, 4, 6, 8, 10 etc.. if the user was to choose C, cmb2 would show the options 1, 3, 5, 7, 9 etc..
(So, it is possible that if the user chooses A from cmb1, cmb2 would show 1 and if the user chooses C from cmb1, cmb2 would also show 1)
Using the example in the brackets above:
If the user chose cmb1 - A then chose cmb2 - 1, cmb3 would then show the options Option1, Option2, Option3 etc..
BUT
If the user chose cmb1 - C then chose cmb2 - 1, cmb3 would then show the options Random1, Random2, Random3 etc..
At the moment I have a table with 3 columns. Within the table is every possible combination of the drop down, i.e:
-A l 1 l Option1 l -A l 1 l Option2 l -C l 1 l Random1 l -C l 1 l Random2 l Etc...
I have tried to use the example shown on this website but i've had no luck: fontstuff.com/access/acctut10 (If you look at "Example 2", thats what I tried to do)
This is a very general question, but a problem that I hit repeatedly in my low level database work - I am not trained in databases at all, but have to use them to get on.
I find that when basing forms on select queries that are stacked on each other - (ie one query shows the results of two tables, that query then is joined to another table in another query and so on) that at some stage the query results become uneditable.
I cannot see why this happens. Is there a simple database rule that I am not adhering to here? Is there a maximum number of tables that can be joined in queries and form the basis of editable datasets in form view.
I'm just starting out to learn SharePoint, which I utterly hate thus far but anyway. So I have successfully linked to the sharepoint list in my DB, but I cannot edit any values for some reason. I can edit them in sharepoint and both systems update, but when I type in ANY box in access I get "Invalid arguement" error.... also note the new record button near the record navigators is greyed out so I can't add a record either.
I noticed that my library user group has URL... set to Read while all others are set to full control... is this the cause or something else?
In QueryB, the entry MUJA 2 is missing. What I need is to take the X1 field from QueryB and join those values with the A1 field from QueryA as follows:
I have three queries that I would like to make into one query, but I'm having trouble. How do I combine the three queries into one? The three queries are below. The qryGetMaxChapter3 is the one that I will use, i.e. it is built on qryGetMaxChapter2, and qryGetMaxChapter2 is built on qryGetMaxChapter1.
qryGetMaxChapter1 SELECT tblCourt.CourtID, tblCourt.ID_Number, tblCourt.ActionDate, tblCourt.Chapter FROM tblCourt WHERE (((tblCourt.CourtActionsID)=1 Or (tblCourt.CourtActionsID)=2));
qryGetMaxChapter2 SELECT tblCourt.ID_Number, Max(tblCourt.ActionDate) AS MaxOfActionDate FROM tblCourt INNER JOIN qryGetMaxChapter1 ON tblCourt.CourtID = qryGetMaxChapter1.CourtID GROUP BY tblCourt.ID_Number;
qryGetMaxChapter3 SELECT tblCourt.ID_Number, tblCourt.ActionDate, tblCourt.Chapter FROM tblCourt INNER JOIN qryGetMaxChapter2 ON tblCourt.ID_Number = qryGetMaxChapter2.ID_Number WHERE (((tblCourt.ActionDate)=[MaxOfActionDate]));
Dear friends I maintain a document database wherein query is generated for individual status like approved, pending & due. approved and pending are generated through "like text*" in status filed. But for due it is separate query using >=Now() in date field. I want to join these two queries and result should be in one query as : Approved document Pending Document and from balance document using Now query - due. One report is attached which will help understand - blank yellow field requires due query.
I am having a problem trying to join two queries. The first query, which is called qry_Planned_By_Month comes from a table (tbl_planned) that captures budget planning information. I created an expression in this query called SumOfPlanned. It takes the sum of all common budget line items (BLI) and is broken down by month.
The second query is called qry_Actuals_By_Month, which comes from a table (tbl_actual) that captures the actual amount spend against each BLI. Again, I created an expression in this query called SumOfActuals. It takes the sum of all common BLIs broken down by month.
Problem: I am trying to create a third query where I can compare planned versus actual. The formulas that I am using to do this are accurate. However the result set contains duplicate data. For example, I may have three entries on the planned side (March, June, and December) and on the actual side for the same BLI, I only have data in February. It displays the February data three times (because it is in the same row as the March, June, and December data.
Is there anything I can do? Thanks very much in advance. URL
An affiliate sent us a table of email addresses, one per record. We need to find which ones already exist in our master table. Our master table contains an email field but it may contain MULTIPLE email addresses separated by semicolons. How do we create a query (or queries) which tell us which email addresses already exist somewhere in our master table?
and what i need to is something that will link all sku 1 category names in a new field with a | divider and then all the sku 2 category names together with | as a divider and so on. the amount of categories is different for every sku.
And if this is easy enough, its not that important though but to delete duplicate category names when it transfers them across to a new field joined together.
I have my main linked table VI, and 4 of VI's fields are to be filtered in a query.
To do so, I have 4 very very tiny local tables (less than 20 records) called respectively "soff", "skus", "warr" and "typ", in which the user will only put what he wants in the result, and by doing Inner Joins, I filter it easily.
Problem is, doing more than 1 Inner Join slow the query so slow it's unbelievable.
If I run the query:
Code: SELECT VI.* FROM VI INNER JOIN SKUS ON VI.Sku= SKUS.Sku;
It runs in 0.7 seconds. It's about the same if I replace SKU by SOFF, WARR, or TYP, the query takes about 0.5 to 1.5 sec.
Now if I run:
SELECT VI.* FROM (VI INNER JOIN SKUS ON VI.Sku= SKUS.Sku) INNER JOIN SOFF ON VI.SOff = SOFF.Soff;
It takes either 15 seconds, or up to 100 seconds.
I tried by doing 4 successive queries, it's the same. The 1st query runs well, and then it grinds to a near-halt.
Running either the 4 queries or a query with the 4 inner joins takes me about 200 seconds, sometimes 400-500.
It's not even a hard query, VI has only almost 1mil rows, and the fields are indexed. The result is 800 rows.
If each join on VI takes 1 second, it should do 1sec+1sec+1sec+1sec, taking in account the fact that the left side of the join grows smaller at each step, it should even do something like 1+0.8+0.5+0.2, or something. Why does having these joins together, or follow each other, make things so damn slow?
I currently have two tables each containing information that I need to complete a query, however the results of suming this information is incorrect.
Below is the structure of the table, one contains more fileds than the other however the joins are on the Date and the Name fields. I have added a few dummy results to show the format
Table 1 "Date" "Name" "Times Logged In" 10/01/2013 Chris 1 11/01/2013 James 3
Table 2 "Date" "Region" "Manager Name" "Name" "Times Logged Out" 10/01/2013 Scotland Cindy Chris 1 11/01/2013 Wales Robert James 2
Query Fields
"Data" "Region" "Name" "SUM Times Logged In" "SUM Times Logged Out"
I can see what the issue is, if someone hasnt logged out there would be no entry in the table so the result query would show "Times Logged In" as X and a blank value at "Times Logged Out." However if there is a value at "Times Logged Out", "Times Logged In" is displayed again and causes the SUM calculations to be incorrect.
how do I overcome this issue though? I can do two spereate queries on the tables to produce results for Times Logged In and Times Logged Out but cant seem to create one query to display both these results in one table.
What I want is IF the date in Table 2 = Date in Table 1 AND IF Name in Table 2 = Name in Table 1 THEN take the value from Logged In and place that in a column and the value from Logged Out and place that in a column.
I have a table and a query. The first 4 fields of the table correspond to that of the query. The query does not have any other fields, but the table has 26 more fields. Is it possible to use SQL OUTER JOIN or UNION or whatever to append the data of the query to the table or do I have to go with recordsets of VBA?
I am looking to create a query using a main table and 3 related tables to produce a count of evaluations completed and evaluations passed by month (using the 3 related tables) and site location (using main table) which is joined by agent name. I have the following so far but:
Code: SELECT [MainTbl].Location, Format$([Tbl1].[EvaluationDate],'mmmm yyyy') AS [EvaluationPeriod], Count([Tbl1].[Agent Name]) AS [NoCompleted], Count(IIF([EvaluationScore]>=0.9,0)) AS [NoPassed]
I'm having problems doing a left join with 3 fields. I know I don't need the 3 fields for this example to align the data but, I deal with much more than this and need this functionality.
The query object I'm looking into is named 'left join' and the sql is as follows:
SELECT Act_Base.CustID, Act_Base.Chipset_ID, Act_Base.Product, Act_Base.Qtr, Act_Base.Qty AS Shipments, SE_Base.Qty AS SE FROM Act_Base LEFT JOIN SE_Base ON (Act_Base.Product = SE_Base.Product) AND (Act_Base.Qtr = SE_Base.Qtr) AND (Act_Base.CustID = SE_Base.CustID);
I'm expecting to see 150 units in the SE.Qty field.
I have two tables: products and orders.I would like to query the products only with orders using the product code and/or model number and join up all product and order information for further manipulation.
I am trying to combine two peculiar tables in Microsoft Access and have been unable to do so even after doing a lot of brainstorming and searching on the internet.
The two tables are spend and export Spend +-------------+--------+-------+-------+ | Country | Metal | Month | Spend | +-------------+--------+-------+-------+ | China | Iron | Jan | 100 | | China | Iron | Feb | 200 | | China | Iron | March | 300 |
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 was working on an update query while joined to another table - and the error I was receiving was the query was not updatable. Er... The table that was being updated sure seemed able to be updated...
Then I wondered if the reason this didn't work was because the other table I was updating from was a query whose records were sum'd and group'd by..I ended up testing the idea by inserting the query's records into a temp table and then did the update to the target table from the temp table... which worked fine.
