Can't Modify Fields In Form With Table INNER JOIN Query
Oct 5, 2004
Hello, exactly how Access 2002 and SQL operate is still a bit fuzzy to me.
In brief:
tableAccounts has fields for Signatory1ID and Signatory2ID, and other stuff
tableSignatories has ID for a primary key and has SignatoryTypeID as a field
tableSignatoryTypes has ID for a primary key and SignatoryType as a field
For each account, on a Form I want to show the Signatory and SignatoryType info for both signatory1 and signatory2. I have tried creating various queries and INNER JOINing the tables and/or queries together, but whenever I manage to get all the information displayed, none of it is editable. Access seems to lock the various text boxes and combo boxes.
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 have a simple Query which very satisfactorily exports data to a Tab Delimited Text File to upload to a website.
The field titles are dictated by the Table Field Names but I would like to modify these for the export. Is there a simple criteria code by which this can be achieved.
Where in Access 2010 I can modify the displayed fields of a Combo Box on a form. I need to format the display window and add criteria to one of the fields. Can't remember where I set this up and recently upgrading from Access 2007 isn't working.
i am very rookie with access.. infact i am doing my first project in access for a friend.. here is my doubt...
this frnd has toy making business.. he wants to keep track of this inventory... i have made one table (ItemId(PK), Item descripstion, noOff, parentItem(FK), stock).. noOff is the no of child items required for a particular parent item..
now i have to run a query to get all the items which have same parentItem.. for this i designed a form... have put 3 text boxes and a subform.. one to accept the parentItem.. 2nd to display its description.. 3rd one to accept no of parent items which i wud be the multiplying factor for the child item noOff.. (default is one..)
now i can retrive the parentItem id.. from the form and use it in the WHERE clause...
SELECT ItemData.ItemID, ItemData.ItemDescription, ItemData.noOff FROM ItemData WHERE (((ItemData.ParentItem)=[Forms]![Display Product Data]![txtProdId]));
but i want to retive the value in the No off: box and multiply it to all the child item's no off...
i have tried to multiply ItemData.noOff with [Forms]![Display Product Data]![txtNoOff] but did not got the desired results....
i need help with this... or if u know ne other way to do it... plese post a reply... thanks...
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 2 tables (tblItemsQB and tblItemInfo) that have a 1 to 1 relationship linked by the "PartNumber" field. The tblItemsQB table contains a description field and 2 price fields (this table is downloaded from Quickbooks). The tblItemInfo table contains additional fields with information on the part that isn't contained in Quickbooks, like OrderType, Phase, SubSystem.
I import the items from QB into tblItemsQB and then do an outer join with tblItemInfo to link all the information for the part. If I have created a new part in Quickbooks, when I do the outer join (I include all records from tblItemsQB) I can see the new part but since the part number doesn't exist yet in tblItemInfo, I can't add the information to the fields in that table.
What is the easiest way to create the new PartNumbers (those that exist in tblItemsQb but not in tblItemInfo) in the tblItemInfo table so I can add the other information to it?
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.
In my job we work with several raw data .txt files exported from Oracle ERP system. These data include information about: inventory, sales, backorders, purchase orders, forecasts, product line.
My goal is to put information from all of these imported txt files together to create an easy to use snapshot file.
The common field between al of these files is the item description. I have an issue where records are repeated for several fields every time the same item description is showed. For example for the same product description the january sales forecast QTY is repeated on several rows because there are several orders for this product description. Is there a way to make forecast QTY appear only once but keep the multiple orders and their information?
automatically adding modified date/time to a record as it changes..I have a work log database for a team of people to keep track of workflow (documents in/out, stage in the workflow, owner of the record, etc.). I have a number of records that I want to automatically update a corresponding date/time field when it is changed. For example, one part of the workflow tracks a document through the approval of 5 different people. So I have a five records that are yes/no flags for approval and five records that are date/time for when it was approved. Right now my team is manually entering the five date/time stamps, but I'd like to have it automatically update when the check the "yes" box and update that field in the record.
I have three tables that contain different columns but linked by a primary column call Name. I want to create a table where all these different columns in the three tables join to form a master table which can be updated regularly either through the master table or the smaller tables. The master table also has the primary column as Name.
If I update the master table with records, it should update the respective linked table and vice versa. I also want to link these tables to my SharePoint site.
Note: except the Name column, none of these tables have any other columns in common How do I go about this?
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.
I want to modify a table called FC_TEMP via adding some columns from another table called AVERAGE_TRP. Do I have to create a new query table and then insert the output into a new table?The following code shows me : Cannot execute a selected query.Is there a way to update this FC_TEMP table without creating another temporary table?
Code:
Public Sub Update() Dim strSQL As String strSQL = "SELECT FC_TEMP.*,AVERAGE_TRP.[Average_new_TRP_EUR],AVERAGE_TRP.[Average_old_TRP_EUR],AVERAGE_TRP.[Average_Margin] " & _ " FROM FC_TEMP " & _ " INNER JOIN AVERAGE_TRP ON FC_TEMP.[PRODUCT_ID] = AVERAGE_TRP.[PRODUCT_ID]"
I have been asked to maintain a directory for our local Scout district. It's currently in a spreadsheet, but is crying out to be a database.I have created a "group" table, this lists all the groups in the district.I have a 2nd table called "people" this holds details of all the people in the district.As some people can have a role in multiple groups, I have created a 3rd table called "link", this is to link the people to groups.
Having read various different posts on sub forms, I am still at a loss on how to create a form / subform to populate the link table.My initial thoughts were to have a combo box on the main form, listing the people and then have a list of groups on the sub form (the are only 24). I would have a check box next to each group and if checked, it mean that the person selected in the combo is associated with that group.However my issue is that the tick box would be a "yes/no" field and the entry in the link table needs to be a number.
Now I require to join these both, the table and the output of the query on the condition where query.fileName like table.fileName.
There is no key in this field. Why I need this because the table has the sort order which the user can change when needed, if I put the sort order in the query then each time there is a change then the query needs to updated which the user can go wrong. Also the filename in the query has date associated which changes every day so I need to pick the unique part of the file name and associate it with the query to get the output from query and sort order from the table.
I have 2 tables that are joined by a many to many table:
tblProductInfo - ProductID
tblProductLinerMM
- PLProductID (FK to [tblProductInfo].[ProductID]) - PLLinerID (FK to [tblLiner].[LinerID])
tblLiner
- LinerID
I have a range of products that each use 2 liners. An inner liner and an outer liner. I need to add 2 records per product to the tblProductLinerMM table.
for example
tblProductInfo has the following records:
- 2138557 - 2378954 - 4387657
tblLiner has 2 liners in particular that relate to these products:
- L5475 - L5468
I need to create the following records in tblProductLinerMM preferably with the use of a query :
im creating a form in design view but the icon to show the fields from the table that i want the fields to come from isnt being displayed; how do i get it to show?
I am fairly new to Acces 2010.I have two seperate tables hat I need to use to compare data. As you can see table A and table B have some of the same item numbers but they also have different item numbers that are not other table. Also some of the item numbers are duplicated in each table but that is okay because the cost of the item is different. Both tables contain item numbers for the products. I want all of Table A item numbers including the item numbers that are in table B. But I also want Table B item numbers except for the item numbers that are also in Table A. In the real raw data file some of the item number fields are blank but the other fields have values. How should I query these tables so that I achieve the correct results?
Table A Item Num Costof Item Supplier Sales Tax Purchase Month 1234 $1.00 Walmart $2.00 Dec 2013 2222 $4.00 Walmart $1.00 Dec 2013 2222 $2.00 Walmart $1.00 Dec 2013 1276 $3.00 Sams club $1.50 Dec 2013 7898 $5.00 Texaco $5.00 Dec 2013 4567 $3.50 Food Lion $1.00 Dec 2013
I create one database and the query and the forms for my clients in the sharing drive. Every time, the user open the form and select the field names and then execute the query depend on which fields the user select on the form, and then output to the screen. Here is the question, if there are multi user access the same form/query, one user pickup 3 fields to run the query, and another user pickup 5 fields to run the query. Becuase one user select 3 field names on the form, and then the code will modify the query contents, another user select the 6 field names on the form, and then the code will modify the query contents.
strSQL = "SELECT [Company Name],[Vendor Name],[Account],[Statement Number],[Amount] from [Statements] "
Dim cat As New ADOX.Catalog Dim cmd As New ADODB.Command Dim qry As ADOX.view cat.ActiveConnection = CurrentProject.Connection Set cmd = cat.Views("Statement Informations").Command cmd.CommandText = strSQL Set cat.Views("Statement Informations").Command = cmd
Set cat = Nothing DoCmd.OpenQuery "Statement Informations"
I used the above code to modify the existing query, "Statement Informations".
When I run it, it give out the error message:
Run-time error '3218': Could not update; currently locked.
And then, I went to queries section, and open the query "Statement Informations" directly, close the query, and then run the code again. Then, it works.
I don't know why. If I run it on next machine on other day, then it give out same error.
Say for instance I have a whole load of IP's in a table in the form of A.B.C.*, where and and B are fixed, C varies slightly and * can be anything from 1-255. Can I use a query to look at an IP and the append an IP record to a different table in the form of A.B.C.0. Basically, modifying the data so that whatever the value of *, it is changed to 0.
I have a report rptTeamPickStats which source is a query qryTeamPickStats
The query has columns such as name, id number etc which are GROUP BY and hours, cases as SUM and there is a DATE field where I originally had a WHERE statement specifying the from and to dates.
I want to be able to modify the where using VB so I don't have to have several queries.
The code below doesn't work (probably won't take you long to realise that) but just to show kind of what I'm after.
I want to query all the TLName where the date is between to dates.
It has happen to me many times that when I create specific queries I'm then not able to modify the values of em...
Do you know the possible causes of this?
Is it because I'm filtering? Ordering by? Grouping? Using fields of differents tables related 1-many? Calculated fields? Expressions? Modules?
----------
One specific case that has presented to me is a query that returns info from 1(main)-many(details) relationship. When I use the sorting of a field of the main table it doesn't allow me to edit values, tho when I delete this sorting it works fine.
Nevertheless, I also have another query which just returns values from one table and whether I use sorting or not, I can modify the values.
select IDno, LName + ', ' + FName as Name, course, Status, (select count(io.AC) from in_out_books io where io.IDno = b.IDno and io.DBorrowed >= '2008/03/01' and io.DBorrowed <= '2008/04/30' group by io.IDno ) as TotalBooks , (select sum(io.Penalty) from in_out_books io where io.IDno = b.IDno and io.DBorrowed >= '2008/03/01' and io.DBorrowed <= '2008/04/30' group by io.IDno) as Penalties from borrowers b
i also want to set the TotalBooks and Penalties to 0 when their values are null thanks
I am trying to create a form on Infopath 2007 based on a table in access 2007. I want my users to be able to fill in this form and submit it back to me in such a way that it will directly populate the table in the database. I know I can do this. However, I am unable to modify the infopath that access 2007 creates from the table. I want to change the date format. By default it takes up the date as "date and time" and I want it to be the date only.
Having problems getting dlookup to work in the control source field of a text box.
My form has fields : Catalog # (numeric value) and Country (drop down text selection).
I would like to query a table CatNameList for a name (text) if the catalog # and country find a match on the table. My field names on the CatNameList table are : Name, Number (to validate against the Catalog # entered on the form) and CName (to validate against the Country drop down on the form).
I am successfully able to populate the name from the CatNameList table on my form using lookup of the catalog # using this :
However, I will eventually have several catalog numbers that will be identical in the table CatNameList, thus why the country is important as the second criteria to be added into the dlookup.
I have tried for a few hours unsuccessfully to add the second portion to my dlookup.
This is what I have currently (not working) that I have been playing with, I'm sure I'm missing a quote mark, & or something simple.
