Initially the query below used Inner Joins, however that limits my list, when i want to see ALL elements from the MasterTable no matter what, thus my inner joins became left and right joins as follows.
Simple Query: Query1
SELECT MasterTable.Station, MasterStable.Description, MasterTable.Area, AreaTable.Percent, GangTable.GangID, GangTable.Speed
FROM (AreaTable RIGHT JOIN MasterTable ON AreaTable.AreaID = MasterTable.Area_ID) LEFT JOIN GangTable ON AreaTable.AreaID = GangTable.Area_ID;
This should simply display All Records in MasterTable, (Multiple Times if Necessary) listing all the elements of AreaTable that are Linked to the MasterTable, and all elements from GangTable that are linked to AreaTable. It does this, and displays them nicely. But I can't edit the fields. I get the result:
1 | Station One | Area 1 | 45% | 204 | 1000
1 | Station One | Area 1 | 45% | 304 | 500
1 | Station One | Area 1 | 45% | 404 | 750
2 | Station Two | Area 1 | 45% | 204 | 1000
2 | Station Two | Area 1 | 45% | 304 | 500
2 | Station Two | Area 1 | 45% | 404 | 750
3 | Station Three | Area 2 | 75% | 254 | 800
3 | Station Three | Area 2 | 75% | 354 | 600
3 | Station Three | Area 2 | 75% | 454 | 700
So you can see that Area 1 has multiple Gangs (204,304,404) and Multiple Stations (1,2). If you do a simple set up like this, you'll find that you can't change the Description field (Rename "Station One" to "Hello World"). It just doesn't work, no matter which way I've tried, I can't seem to make a Query that presents all the information from MasterTable and All the Information IN AreaTable and All the Information in GangTable which will allow me to also edit the fields.
Any Help would be most appreciative, I'm tearing my hair out on this one.
Thanks,
Jaeden "Sifo Dyas" al'Raec Ruiner
ps - It just seems that with Junction Tables and all the many to many relationship designs I have tried, you'd be able to change the non-related fields. I understand that you can't change the "ID" fields, but the others should be editable.
I have an access database and some asp-code on my computer which only works one way? I can read from it, but not write?
...and when I put the asp-code and the database on anohter server it works fine. ..what setting do I have to do, to make it work on my computer?
I have another asp-site with a mySQL-database on my compter which works fine...but it doesn't work with my access-database...hmm...I can only read from it.
I'm currently creating an interface program for a MS Access database. I've only used Access once in the past but used other DB's a handful of times so I didn't have any trouble getting the general program created. My issue arose when I tried to make it so that the users needing to use the program can just copy the .exe and the .mdb files and use it.
The program doesn't require users to share the database but to store their OWN programs information in their OWN database, so basically each needs to have their own database with the exact same design (but different data). The original plan was to just get them to copy over a near empty .mdb with the design set up and have them slowly create their own dataset with it. However, upon copying these files between my computer and others it ends up allowing my interface program to retrieve data from the DB but not write any new entries to it. All security permission on the file itself are set to allow read/write to everyone so I'm not sure what's causing this. Is there any other security settings I should worry about? Everything I could find within access seemed to be set so that it could be used freely so I'm not sure what the problem is. Any help would be greatly appreciated.
P.S. - I apologize if there is already a related topic in existence, I am new to these boards and tried to search beforehand for any information on this topic but came up empty handed so decided to start my own topic about this problem.
I have a list box whose data is inserted in a table named as "test0" ,now in a macro of vba i want to select all the items in the list box and create there pdf files in a folder at my desired location. So far i have managed to create a pdf file of single item ,but i want to select multiple items at once ,
Code: SELECT test0.ID, test0.item FROM test0 WHERE (((test0.item)=[ItemNumber]));
So, in this query itemNumber are multiple and i want to create there pdf files at once ..just on a click of one button ?
I'm a SQL Server DBA and have a few Access 2010 Db's to look after and occasionally have to make amendments to (as is the case today). I've been asked to apply permissions to certain users of the Access database whereby users in a group we're calling 'Viewer' only have read permission and users in another group called 'Writer' (original I know!) have both read and write permission.
The database is form driven (i.e. no-one directly accesses and amends the tables in the database), the file is saved with a .mdb extension.
I've played around with the 'Manage Users & Permissions' in the File Menu however these don't seem to do what I need them to and only result in having to input a user and password to access the database on start-up.
Does a (the) .ldb file have to be closed for another user on a network (separate FE linked to network drive BE) to read/write info to a table? Or even select info from a table?
Will like to check, i have a access database which can be open up by a few users.
How can i only allow the first user who open up the database to read and write the database and the follow users who open up the access have only read access?
All retailers have at least one distributor BUT a retailer may or may not have ordered any products.
I have created my form but the query linked to the form is having some trouble. It is only selecting those records that have ordered products. For example, if I query a retailer name only and it does not have any ordered products, it will not display. Is there a problem with the table joins? The SQL for the query is displayed here:
Hello, I have the following code for a multiple join:
INSERT INTO [AppendAllFields]SELECT [TreatyList].[Treaty] AS [Treaty],[MLAC 42 Treaty Xref ER].[tai treaty] AS [TreatyType],[txn 01/04].[Policy_Number] (and more other fields from [txn 01/04] table) FROM [txn 01/04] INNER JOIN [MLAC 42 Treaty Xref ER] ON TRIM([txn 01/04].[Policy_Number]) = TRIM([MLAC 42 Treaty Xref ER].[Polnum]) INNER JOIN [TreatyList] ON TRIM([MLAC 42 Treaty Xref ER].[tai treaty]) = TRIM([TreatyList].[TreatyNo]);
Basically, the txn 01/04 table has a corresponding Polnum field in the MLAC 42 table, and MLAC 42 table has a tai treaty field, which corresponds to TreatyNo in TreatyList table.
However, when I tried to run this, I got an Syntax error.
I am trying to set up a file compare process. I load the two files into two "matching" tables (there is a key field). I have a series of queries which find any differences in the various fields and displays them. My problem is that one field (Field4 - a tran code) is coded 1,2,3 in one file and A,B,C in the other. I have set up a third translation table with two columns, each row showing the matching codes (1/A, 2/B, 3/C, etc). I want to find corresponding rows in FILE1 and FILE2 (matched on KEY) where tran codes (Field4) do not match (ie a "1" in File1 but NOT a "A" in FILE2).
I have not been able to get the two inner joins to work. Can some one help.
This is my last attempt:
FILE1: KEY-Field1-Field2-Field3-Field4 FILE2: KEY-Field1-Field2-Field3-Field4 (Field4 is the code that needs to be translated)
FILE3/numeric/alphabetic 1 A 2 B 3 C 4 D
SELECT FILE1.KEY,FILE1.Field4, FILE2.Field4 FROM FILE1 INNER JOIN FILE2 ON FILE1.key = FILE2.key INNER JOIN [FILE3] ON (FILE1.Field4 = FILE3.alphabetic) WHERE FILE2.Field4 <> FILE3.numeric.
I have a table (Table A) that includes every number that's been dialed in our call center. I have another table (Table B) that has account numbers and three different phone fields for each account.
What I initially tried was to left join the Dialed Number column from Table A to all three phone number columns in Table B. This produces no results. If I only join Dialed Number to Phone Number 1 (for example), I get results, however, if the agent dialed one of the other two numbers, it's not going to show up.
My query references 2 related tables: one for persons (PERS) and one for telephone/fax numbers and email addresses (CONT, for Contacts). The relevant fields are:
The foreign key come_id refers to a table for contact methods (COME), either "Phone (Home)", "Phone (Work)", "Mobile", "Fax" or "Email".
Now i want to list all persons with their home phone number and email address, also if they don't have one. It seems to be impossible to get it ... I will explain what happens. Lets start simple: first list all persons with their home phone number (come_id = 1): SELECT PERS.pers_forename, PERS.pers_surname, CONT.cont_number AS Phone FROM PERS LEFT JOIN CONT ON PERS.pers_id = CONT.pers_id WHERE (((IIf(IsNull([come_id]),1,[come_id]))=1)); This works fine. The IIf expression is necessary since we are dealing with an outer join: not all persons have a home phone number. If we would simply put "WHERE come_id = 1" then the query produces only the persons that have a home phone number.
But now i also want to see the email address (come_id = 5): SELECT PERS.pers_forename, PERS.pers_surname, CONT.cont_number AS Phone, CONT_1.cont_number AS Email FROM CONT AS CONT_1 RIGHT JOIN (PERS LEFT JOIN CONT ON PERS.pers_id = CONT.pers_id) ON CONT_1.pers_id = PERS.pers_id WHERE (((IIf(IsNull([cont].[come_id]),1,[cont].[come_id]))=1) AND ((IIf(IsNull([cont_1].[come_id]),5,[cont_1].[come_id]))=5)); It seems perfectly logical: i added a second alias CONT_1 for the email address. Since this is also optional we have a second outer join, and the WHERE condition should also use an IIf expression. The result is not correct though: the resulting recordset shows only the persons that have both a home phone number and an email addres or neither! I have a lot of experience with SQL and queries, but i know i am not infallible. Nevertheless i am quite convinced that i should get all the persons: those that have a home phone number or an email address, or both or neither ... I hope that someone of you can explain this.
I have this query in design view and in an asp page and it works fine:
SELECT dbo_feedback.*, dbo_origin.originName, dbo_product.prodname, dbo_category.catName FROM dbo_product INNER JOIN (dbo_origin INNER JOIN ((dbo_feedback INNER JOIN (dbo_category INNER JOIN links_cat ON dbo_category.catID = links_cat.CatID) ON dbo_feedback.id = links_cat.FeedbackID) INNER JOIN links_product ON dbo_feedback.id = links_product.FeedbackID) ON dbo_origin.originID = dbo_feedback.origin) ON dbo_product.prodID = links_product.ProductID;
BUT, I want to return all feedback entries, even if they have no matching Product or Category. :confused: When I change the inner joins to outer joins I get a syntax error in the browser window. I changed the join type in the relationship diagram in Access and tried to recreate this in query designer, but Access says the statement cannot be executed because of ambiguous outer joins.
FYI, one feedback can have many products, many categories, and only one origin. I have the joins correct and enforced.
From table 1, I join fields A, B, and C to fields A, B, and C on table 2. From Table 2, I join Fields 1 and 2 to Table 3. All the joins are Join 1.
When I pulled (Queried) fields D, E, and F from Table 1, field D from Table 2, and field D from table 3, I have a sum of $1000 under field (column) E from Table 1.
The second time I pulled data, I added fields A and B from table 1 to the query. However, I get a total of $1500 from the same column. i.e Field E from Table 1. I can understand that there will be more rows to provide further data breakdown, but I could not understnad why the total change.
Hi- I have three queries: Queries: Contain: qryPrimary - primary_code, 1Reasons, EnteredDateCount as "CountP" qrySecondary - 2ndary_code, 2Reasons, primary_ref, EnteredDateCount as "CountS" qryTertiary - tertiary_code, 3Reasons, EnteredDateCount as "CountT", 2ndary_ref
In another query to combine data for a report, I need to see: All the "1Reasons" and "CountP"; their corresponding Secondary "2Reasons", "CountS"; and finally the Tertiary "3Reasons", and "CountT" that correspond to the Secondary's.
Like a fool I thought it would be easy. I know it is probably a join problem, but I can't figure it out.
I currently have query that looks at 2 tables that hold financial information. I am querying the data to do some adding and subtracting based off a financial class of a facility. The issue I am having is that one table may have a financial code that the other does not have but I still need to show that financial class for the facility.
My looks as follows
FROM [CashValue Link] INNER JOIN TCashValue ON ([CashValue Link].FINANCIAL = [TCashValue].FINANCIAL) and ([CashValue Link].Date1 = TCashValue.Date1) AND ([CashValue Link].FACILITY = TCashValue.FACILITY) AND ([CashValue Link].CLT = TCashValue.CLT)
I have a question. If I have a form, which has underlying source is the table LineItem. The table has these fields PO#, Line Item, Cancel check box, and Cancel Reason list box. Each PO can have 1 to multiple line items. Once in a while, the users need to cancel certain line items and type a reason in the Cancel Reason box. When the users click the Cancel check box, it force the user to enter a reason.
However, in cases, some POs have 10-100 line items, and the reason are the same for all line items, and it is time-consuming and tedious to do one by one. I would like to find a way to enter reason in line 1, for example, and copy/update the rest of line items in that same PO with that.
Please advise HOW and WHAT COMMAND should I use to update the text in one field of one record (line item) to ALL other records (line items) in the same PO.
I have 2 forms which allow the user to first select a catergory. They can then select a sub category based on the selection made in the first box.
I have the form working 90% but can't get the list to filter based on the previous selections.
The code I'm using to generate the listbox rowsource is shown below;
"SELECT tblcatctry.CtryID, tblcatctry.Country, tblcatvtry.zoneID " & _ "FROM tblcontactsCountry RIGHT OUTER JOIN " & _ "tblcatctry ON " & _ "tblcontactsCountry.CtryID = tblcatctry.CtryID " & _ "WHERE (tblcontactsCountry.CompanyID IS NULL) OR " & _ "(tblcontactsCountry.CompanyID <> " & VarCompanyID & ") " & _ "GROUP BY tblcatctry.CtryID, tblcatctry.Country " & _ "ORDER BY tblcatctry.Country"
I want to be able to add in a join to the table tblcontactszone which has the selections previously made for the fields, ZoneID and CompanyID. How do I filter the above further?
I am trying to write a query that will return records from multiple tables. I currently get an error suggesting I create a subquery or else I get far too many records.
The query is EditAttendanceQuery (I left it in a bit of a state). The fields I need are shown in the query. The records I need are based on the Edit AttendanceQuery (Form). I can get the records I need without the CourseNumber and Section, but it all goes downhill when I include them.
I am building a select query which is grabbing data from multiple tables with items being linked by a unique field "Certificate_ID".
I have created joins between table A and Table B, and Table A and Table C, linking both by Certificate_ID
All have the join property set to select all Records from table A, but only those from Table B and Table C where the joined fields are equal.
Table A has 5000 records. Am I correct to assume that my query should only return a max of 5000 records as well? When I select Certificate_ID from A and another column from B it only gives me the 5000 unique records. When I add in a column from Table C it is however returning something like 7500 records, with several being duplicates with the same data in every column.
Why it is choosing to duplicate records and give me more than I want. I am sure I am overlooking something simple.
I have two queries, both delete from the same table and both have joins..This one works:
Code: DELETE RequestCheck.* FROM( SELECT TOP 3 PayRequest.RequestID, PayRequest.GBPAmount, PayRequest.Currency, PayRequest.RequestDate FROM PayRequest WHERE (((PayRequest.Currency)="EUR Euro") AND ((PayRequest.RequestDate)>#11/16/2014#)) ORDER BY PayRequest.GBPAmount ) AS TOP3 INNER JOIN RequestCheck ON TOP3.RequestID = RequestCheck.RequestID WHERE (((RequestCheck.CheckID)=8));
This one does not:
Code: DELETE RequestCheck.* FROM 300_tblDeleteRequestCheck INNER JOIN RequestCheck ON [300_tblDeleteRequestCheck].RCID = RequestCheck.RCID;
My boss and I are in a jam. We have been using Access to run a reporting process, but one of our tables will exceed the maximum fields allotted this month. Our thought, dump the table into SQL Server and use the GUI interface provided in Access Projects.
Unfortunately, the query designer seems to have a few drawbacks. The one that effects us the most is in using UPDATE queries where more than one table is used to determine records to be updated. In attempting it, we get the message: "The designer does not graphically support the Optional FROM clause SQL construct".
Now I know we can manually create Update Queries, but we often need 1,000+ in a short period of time. Manually punching in all the fields involved and other code just isn't timely.
Question: Is there some alternative, service pack or anything else that would allow us to graphically create these Update Queries using Access Projects or even SQL Server 7.0? I would hate to have to scrap all the work we did over something that seems so minor.
I just restructured my DB and I was wondering if anyone can give me some advice on whether or not my joins/relations are correct. I left some joins/relations out because I wasn't sure what relation I should use.
Is it possible to have IIf function which checks if there is a text1 in another field and if true writes text following text1 in to field containing IIf function.
Example(field1 containing text1)
Na avtocesti Maribor - Ljubljana pred priključkom Blagovica v smeri Ljubljane je zaradi prometne nesreče zaprt vozni pas.
I want to check if it contains red text and if true, write blue text.
field1.................................field2 ***v smeri A***.................A......... ****proti smeri B****.........0........
I am trying to update a memofield from a form.My file.. sign_midi.asp has this partial code.First I pick up the record I want to update. And then present it in a form. " <tr> <td align="right" height=10 valign="top"><b>Beskrivning :</b></td> <td align="left" height=10 valign="top" width=250><TEXTAREA WRAP="soft" name="M1" cols="65" rows="8"><% = rs("text")%>
