Update Records With A Circular Many To Many Relationship
Feb 27, 2006
Hi Foks,
I'm sure this has been asked and answered many times but I'm not having much success in searching for it as I can't think what to put in the search engine. My apologies in advance for the longwindedness of this posting.
Anyway, I have a database for tracking projects. As the projects evolve, they are sometimes amalgamated, split, dependant on other projects or implemented under a common contract with one or more other projects. Thus, a project can have none-specific relationships with multiple other projects.
To accomodate this:
I have a juction table, tbl_Related_Projects, with fields Project_ID and Related_Project_ID set up as a joint PK.
The PK of tbl_Prj_Details has 1-to-many relationships to both of these fields to create a "circular" many-to-many relationship between the tbl_Prj_Details.Project_ID field and itself.
A comment field in tbl_Related_Projects describes the nature of the relationship between the projects.
;) I hope I you're still with me and following this!;)
On my fdlg_Prj_Details, I have a subform for tbl_Related_Projects. A user can select related projects from a drop down list (Combo Box). So far so good.
Here (finaly :eek: ) is the question: When a user selects a related project (Project B) on the related project subform for Project A, how do I get the reverse relationship to show for Project B i.e. Project A is to appear in the related projects subform of the Project B record, along with the same comment?
I can't even begin to think how to achieve this but as it is a relationship issue, it must be what relational databases are all about and have been solved many times.
Currently a user must select the appropriate project in the related project subform for each record and enter the comment twice. The possibility and liklihood is that the relationship will not always be noted for both ends i.e. A will be related to B, but B not related to A.
:cool: Small caveat for any helpers here, I can copy, cut, paste and stumble/modify code, but at this early stage in my database carreer (ha!), I cannot write it from scratch - too many traps for beginners.
Having re-read this a number of times before posting, it is occuring to me that this is a more complicated problem than I thought it was. Many thanks in advance for any help and advice.
I have a query which i'm trying to link to a listbox on a form. One of the colums i'm trying to get in the listbox is from a lookup. It should return either "in" or "out" but is returning the number values 1 & 2.
so.... i'm trying to write some criteria in my query that says if=1 then "in" etc
Here's what i've got but i'm getting a circular ref error. In-Spec / Out of Spec: IIf([In-Spec / Out of Spec]=1,"IN","OUT")
Noob! I am building an inventory db. I need a query(s) that uses a Qty available to add inventory added, subtract inventory used and put the final total back into Qty Available. I'd prefer to do in all in one query if possible. Any suggestions?
I've created a db I plan on using for pricing out parts, the issue I'm having is that I'm getting a circular reference error. The way my db is setup is like this Partstbl contains the information for all parts made. Some of these parts can have subassemblies and these parts will also be in the "partstbl". In another table called "Subassemblytbl" I choice the parts from the "parttbl". Parttbl has a one to many relationship with "subassemblytbl" The error happens when I created a query that was to calculate the part cost. This cost would then be displayed in the subassembly subform on my main form. I'm having some difficulty explaining this if anyone can help I'd really appreciate it
This is how I have my database set up currently, except the part highlighted in red.
I was asked to see if I can use the CAT (one) to narrow down the CLIN (to many) and this is how I think it should be structured. Is it okay to have this kind of circular relationship within the database?
They are related with a one-to-many relationship. The related field is "InvoiceId"
I want to create a query that shows selected fields from tblInvoice and the sum of field "PrepaymentValue" from table tblInvoicePrePayments.
I want to be able to edit the fields from tblInvoice in the query.
If I create a totals query (qryInvoicePrePaymentsSum) on tblInvoicePrePayments, using the fields InvoiceId and PrepaymentValue, and then create another query with tblInvoice & qryInvoicePrePaymentsSum, the fields in tblInvoice cannot be updated!
I created a query that pulls information from 2 other queries and everything was going fine until I saved the query. I now get a circular reference error
SELECT [CashValue Link Query1].Facility, [CashValue Link Query1].[Financial Class], [CashValue Link Query1].Date, [CashValue Link Query1].Date, [CashValue Link Query1].[Total A/R], [CashValue Link Query1].Current,
I have 3 tables: 1. customer 2. DVD collection 3. Borrow
The aim of this system is to create a home borrowing system.
The customer table has a one to many relationship with the borrow table, while the dvd collection has a one to one with borrow. I can create a new book with no problem, and even view these on a form. Here my proble arises.
I ahve another form to return a DVD. I view the records from the borrow table, and once found what i want there is a button which deletes the record from the borrow table. Sadly, when i press this button, not only does it delete the record in the borrow table, but it will also delete the record from the DVD collection table.
I have tried everything i can think of, and i don't want to change to change the design as i have found this mnethod very easy to generate reports and the like. Any help??
Thanks
EDIT: the button i used was from the wizard that comes up when you create a button.
I'm working on my first report and am trying to get my textbox, tboJob to show a field value and not the ID. I have tried the following Dlookup in my control source with no luck.
I have a db using 3 large tables with 1:1 relationships. Before you question my normalization, I stress that these tables are strictly used to temporarily store user input information for a highly automated form with several hundred fields. All of the bound fields enable the form to be reopened and retain the information originally entered. The information is then transferred to a report and converted to a .pdf where it is filed; these 3 table's records are deleted weekly with a delete query. Other than a few combobox row source queries, these 3 tables are not connected to the rest of my db. At this point the form is functioning perfectly.
What I am trying to do is save about 3-5 of the partial (records) on 2 of these tables for indefinitely, no longer needed, or overwritten. Reason being is that partial information (records) on this form can be recalled and duplicated into a new form, saving the user time from having to completely re-enter some fields that will not change week to week.
So far, my best idea is to have 2 duplicate tables that store these records (based on a checkbox on a scheduled query event) that can be recalled and written back into the 2 main tables with a new primary key when needed. Append queries only work with one table at a time, and I need to maintain the integrity of these records 1:1 across the 2 tables with their primary keys intact.
I have a table that holds UK Postcodes and a customer table that holds customers.
I am trying to create a relationship between the 2 so when I enter a postcode in the customer table this is then related to the postcode table.
The main problem I have is that there is a lot of duplicate postcodes in postcode table so the primary key is simply a number as you will see in the picture.
I have created a linked Excel table in Access 2010 called 'tblExcelLinked' and I have a form called 'ASB Log Form' for the purposes of presenting the data in a more readable manner that is easier to view, plus link other fields of data that are not directly related to the 'tblExcelLinked'.
Because there is no unique ID in the 'tblExcelLinked' to create a relationship, I have created a table called 'tblASB', which allows me to add other table data linked from same d/b.
I now want to update the 'tblASB' with data from the 'tblExcelLinked', but only append new records from 'tblExcelLinked', but my inadequate append query is duplicating the records each time I run it, rather than just adding the new ones.
Once sorted my next challenge is a macro so that this runs automatically rather than being manually triggered.
I have built a contacts manager based on an Outlook export (I know I can link the two programs, but I wanted to customize the contacts a bit further). I built the database in a way that phone numbers, addresses, etc. allow for multiple entries for each person under the same column using a foreign key. The only difference is a field called "Type" which designates the entry for Home, Work, etc. The issue I ran into is when I queried the data I get multiple entries for the same record. As you are all likely aware, Access repeats the data in a query for each different case. For example if Bob Jones has a personal and work phone number, Access will show the results in a query like this:
1. Bob Jones 555-555-5555 Personal 1. Bob Jones 555-222-2222 Work 2. Jerry Smith 555-111-1111 Personal
If I kept the data in Access it wouldn't necessarily be an issue. However, I want to be able to export the data in an excel file. How do I get Excel to return the data so it looks like this:
1. Bob Jones 555-555-5555 Personal 555-222-2222 Work 2. Jerry Smith 555-111-1111 Personal
I have got a query that updates details from one table2 to table1, "Reference" is the primary key and this is what the query uses to determine which need updating.
It all works great but if table2 contains a record in "Reference" that is not in table1 i just want it to ignore it, currently it just seeems to add them.
I have a table called tblCompanies. When a company acquires another company, I need a method by which the acquired company's CompanyID (PK) can be updated to the new company's CompanyID (PK). I also need to be able to update all related CompanyIDs (FKs) to the new value in related tables.
In cases in which the new company does not have an existing record, there is no problem: the company name simply gets changed to the new company and the existing CompanyID is maintained. I then use an audit table and Track Changes function to keep track of the company name data and a union query to keep the old names in the selection lists.
The problem is when both companies already have existing records in the table.
So, let's say I have records for Company A and Company B. Company A merges with Company B and Company B is now the main record. What is the best, simplest and easiest way to update the CompanyID (PK) from A to B and change the CompanyID (FK) to the new value in all related tables?
I am envisioning a pop-up form that directs the user to select the new company and then an update query happens behind the scenes... but exactly how does the criteria for the update query get selected and how do all the related tables get updated? My vba skills are pretty basic, will I need extensive coding to do something like this?
Erm, I know this sound a bit silly....but I woudered if there was a way of adding anew record to a table using vb code?
My table is called tblTransmissions
The fields are TransmissionCode, AutoNumber TransmissionDate, Date/Time TransmissionTime, Date/Time TransmissionStatus / Text TransactionCode / Number (Linked to another table by One-To-Many)
When I hit a command button, I would like to update the recordset by firstly adding a new record. Then set Date & Time to Now, Then TransmissionStatus to a control on a form, same with the Transaction Code.
Normally I would place the values on a form and update the values that way, but just wondered if this was possible!
I am chasing a quick solution to what I am sure is a simple thing.
In my database I have a table of items that have to be held for different periods of time (one item type kept for 5 years, another for 3 etc). In another table, linked to the first through the item type, I have dates entered against records which, using the first table, will calculate the date I can get rid of the item.
My problem is that the period of time for some items to be held has now changed, and I want to change all the relevant records to update to the correct disposal date.
I have tried just changing the period items are held for (in the form), and that will provide the correct date for future records, however I want to refresh the old records as well so they are all correct.
Is there an easy way to achieve this without going through each individual record.
What I am doing is attempting to update a table through a form. I have been reading up on update queries, but I am not quite understanding how to use them. I have a table containing consumables that we constantly issue and receive. I have a form for each of these functions. Example: I order two ink cartridges from a vendor through a purchase order. When they arrive, I would like to enter the qty. I am receiving, then have it automatically add it to the table value. I don't know where to implement the arithmetic. Also, for PO's with multiple items, what's the best way to update the table with those?
The problem is I removed my primary key (if you can even use a primary key for this) and there's no record number option on the query. I just need 800, it doesn't matter which 800.
I was thinking of adding a field with a series of unique identifiers, but I don't know how to autofill sequentially.
Hi all I have a table "tblEmployee" which has "name, employee_no, manager, etc" I recieve from HR a updated spreadsheet which can have new employees or just updates. ie new manager name. I can run an update query to update changes based on the employee_no which is constant. How can i add new starters from this list ?
I am a new user to MS Access and i'm creating a stores account for my works, however i'm having problems with the updating of records after colleagues have been issued with kit. I have three table (employees, kitlist and issuelist) what i want is that when i issue kit to employees this updates the item in the kit list to reflect that i have issued an item, ie. take the number away from the units in stock. This is probably really easy however cannot find a solution to this problem.
I have a couple questions I hope that you can help me with. I have an asp page that is retrieving a bunch of records from an Access Database based on a field value (in my case its based on Department). They are then placed in a table format in the asp page. The user can then modify one or many records. Everything was working fine and then we upgraded our IIS Service from IIS5 to IIS6. Once we did that I started getting errors. I was able to update certain departments and not others. This told me the code was good and that the database permissions etc were good. It has to do specifically with the value or similar. I looked further and came to realize that it appears there were to many records to update. The Departments with a few records updated fine. THe ones with 500-1000 were erroring out. I went into the database and deleted the records from 1000 to 500 and everything works.
Does anyone know if there is a record limit to update?
Is there a function on ASP that I can use like Me.Dirty that will search first for updates and then just update the single record?
Is this a result of IIS6?
Here is a bit of my code: This is the area that it is bombing out on....I am not an expert and believe I might have hit an ARRAY size limit. Does that make sense?
