Queries :: Triple Join Uneditable In Form

Jan 28, 2014

I have a query:

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.

View Replies

Forms :: Uneditable Dataset In A Subordinate Form?

Jul 16, 2014

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.

View 5 Replies View Related

Triple Bookings

Mar 5, 2007

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

View 7 Replies View Related

Uneditable Combobox!!

Oct 20, 2006

Hi,

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).

Does anyone have an idea??

View 1 Replies View Related

General :: Triple Cascading Combo Boxes

Jul 10, 2013

Working in MS Access 2007.

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

This this the code im currently using :

Private Sub areabox2_AfterUpdate()
devbox2.RowSource = "Select DISTINCT [Budget Info].Development " & _
"FROM [Budget Info] " & _
"WHERE [Budget Info].[Project Area] = '" & areabox2.Value & "' " & _
"ORDER BY [Budget Info].[Development];"
Me.devbox2.Requery

[Code] .....

I am thinking that i have to add a second WHERE clause to the devbox2_AfterUpdate() but i dont know how?

View 7 Replies View Related

Forms :: Triple Cascading Combo Boxes?

Jul 24, 2013

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)

View 7 Replies View Related

General Question - Uneditable Query Results

Jun 4, 2007

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.

Sorry for vagueness!

Matt

View 2 Replies View Related

Tables :: Sharepoint Linked Table Uneditable

Aug 27, 2013

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?

View 1 Replies View Related

Join Two Queries?

May 3, 2006

Ok, I have having a problem joing these two queries. Can anyone please help me out here. These are the results of my two queries:

These are the values in the A1 field of QueryA

COCKBURN 2
GT KW 1
GTN 1
IPP GT1
IPP GT2
KALG F5
KALG F6
KWIN C6
MUJA 1
MUJA 2
MUJA 3
MUJA 4
MUJA C5
MUJA C6
MUJA D7
MUJA D8

These are the values in fields A1 and X1 in QueryB:

COCKBURN 86.83
GT KW1 10.44
GTN 1 16.13
IPP GT1 62.84
IPP GT2 62.56
KALG F5 15.12
KALG F6 20.83
KWIN C6 37.78
MUJA 1 23.44
MUJA 3 8.69
MUJA 4 23.44
MUJA C5 84.56
MUJA C6 89.01
MUJA D7 86.22
MUJA D8 84.65

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:

COCKBURN 86.83
GT KW1 10.44
GTN 1 16.13
IPP GT1 62.84
IPP GT2 62.56
KALG F5 15.12
KALG F6 20.83
KWIN C6 37.78
MUJA 1 23.44
MUJA 3 8.69
MUJA 2
MUJA 4 23.44
MUJA C5 84.56
MUJA C6 89.01
MUJA D7 86.22
MUJA D8 84.65

How can this be done. I need this in another query. I have tried the join statements, but I get errors every time. Thanks in advance.

View 12 Replies View Related

Join Queries

Mar 21, 2007

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]));

View 5 Replies View Related

Join Two Queries

May 2, 2006

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.

Please help
Thanks - Navkesh Chawla

View 14 Replies View Related

Problem Trying To Join Two Queries

Dec 1, 2004

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

View 1 Replies View Related

Queries :: How To Join One Value To List Of Values

Jan 2, 2014

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?

View 7 Replies View Related

Queries :: Join Fields Query

Jul 28, 2013

I tried doing a search but couldnt find the i needed.

Category_name Sku
cars 1
Trucks 1
Bikes 1
Phones 2
Tablets 2
Hats 3

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.

so it looks like this

Joined_categories sku
Cars|Trucks|Bikes 1
Phones|Tablets 2
Hats 3

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.

View 14 Replies View Related

Queries :: More Than 1 Inner Join Slows Query Too Much

Jul 30, 2015

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?

View 7 Replies View Related

Queries :: Sum Two Fields On Left Join?

Jul 29, 2013

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.

View 2 Replies View Related

Queries :: Possible To Use SQL OUTER JOIN Or UNION

Jan 9, 2015

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?

View 6 Replies View Related

Queries :: One To Many Join / Count Query

Oct 16, 2013

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]

[Code] .....

But I get the following results:

Code:
Location EvaluationPeriod NoCompleted NoPassed
Location1 Month1 Tbl1.NoCompleted Tbl1.NoPassed
Location1 Month1 Tbl2.NoCompleted Tbl2.NoPassed
Location1 Month1 Tbl3.NoCompleted Tbl3.NoPassed
Location2 Month1 Tbl1.NoCompleted Tbl1.NoPassed
Location2 Month1 Tbl2.NoCompleted Tbl2.NoPassed
Location2 Month1 Tbl3.NoCompleted Tbl3.NoPassed
etc..

View 4 Replies View Related

Queries :: Left Join On 3 Fields

Mar 21, 2014

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.

[URL] ....

View 5 Replies View Related

Queries :: Join Two Tables For Further Manipulation

Feb 1, 2015

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.

View 2 Replies View Related

Queries :: Join Data Between Two Tables?

Sep 3, 2014

I have two tables

first table : customer name,QUANTITY ,delivery date,product type

second table : customer name,QUANTITY ,delivery date,product type

i want to make query to sum the QUANTITY from the two tables and group by customer name and delivery date

View 14 Replies View Related

Queries :: JOIN Expression Not Supported

Dec 18, 2013

I am using vb.Net to connect to an access db. In access 2010 this query works fine, but in vb.Net, it errors with 'JOIN expression not supported'.

Code:
sql = "SELECT Boxes.Box, Boxes.CustRef, Boxes.Customer " &
"FROM (Requests INNER JOIN [Request Boxes] ON Requests.[Request no] = [Request Boxes].[Request no]) INNER JOIN Boxes ON [Request Boxes].Box = Boxes.Box" &
"WHERE (Requests.[Request no]) = '" & item & "' " &
"AND ((Boxes.Customer) = '" & customer2 & "'))"

View 3 Replies View Related

Queries :: Join Two Tables With Duplicates

Oct 19, 2013

View 2 Replies View Related

WHERE Clause On LEFT JOIN : Why Do I Get Join Expression Not Supported Message?

Nov 10, 2006

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";

but I get Join Expression not supported

What am I doing wrong?

Thanks
Andrew

View 7 Replies View Related

Create A JOIN Of Different Tables Called Join A Variable And List

Nov 16, 2013

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?

View 6 Replies View Related

Queries :: Update Join Set From Table To Query

Aug 29, 2013

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.

View 1 Replies View Related