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,
[code]....
Hi,
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
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.
Code:
=DLookUp("[JobName]","Jobs","ID=" & [tboJob])
JobName is the field I want to show from the table Jobs.
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?
View 7 Replies View RelatedWithin a query, I'd like to reference another query field based on a date specified as a parameter.
In my query, there are fields for each month: [January],[February], etc.
I have a field titled [Current Month], based on the parameter [As Of Date]. So if when running the query, the parameter pops up and I type 5/6/2013, it knows that the month is May. I know how to return May in the current month field (format([As Of Date],"MMMM"). But how to I return the value that is in the May column?
I want to populate a table with field:date and field ref nr.(7 fixed ProductID's)
But only weekday and to 3 years from now. (every weekday)
Example
field:ID | date | fixed.ref.nr
--------------------------------
1 | 7/4/2015 | 1001
2 | 8/4/2015 | 1002
3 | 9/4/2015 | 1003
4 | 10/4/2015 | 1004
5 | 13/4/2015 | 1005
6 | 14/4/2015 | 1006
7 | 15/4/2015 | 1007
8 | 16/4/2015 | 1001
9 | 17/4/2015 | 1002
10 | 18/4/2015 | 1003
.... (ID field is autonumber field and not important..)
How to do this with a append query or code?
Hi,
Wonder if anyone might be able to help me out. In the detail section of Access report, there is three subreports laid out as follows
subreport1
subreport2
subreport3
Depending on the certain criteria, subreport1 and subreport2 may appear in the report. subreport3 will always appear on the report.
The problem that i'm having now is when subreport1 and subreport2 do not appear in the report, thet seem to occupy the space. Hence, when viewing the report, there is a huge gap. My question is whether there is a way of removing the gap there.
Thank you in advance
I've found one thread on this forum which appeared to cover the problem I'm having, but it was from a few years ago and stopped rather abruptly.
I have a combo box with five fields. The row source for the box is shown below and includes a value that I obtain from dividing one figure by another. All is well until I try to order the box's contents by this value, at which time I'm getting an 'Overflow' message.
The full query is as follows, with the problem area in itallics.
str_Source6 = "SELECT TOP " & li_Top & " FormatPercent((rogerreport.mntasts/rogerreport.allasts),2), " & _
"rogerreport.ria, " & _
"tbl_iainfo.IAFirstName, " & _
"tbl_iainfo.IALastname, " & _
"tbl_iainfo.NesbittBr " & _
"FROM rogerreport INNER JOIN tbl_iainfo " & _
"ON rogerreport.ria = tbl_iainfo.IACode " & _
"WHERE ([Period] >= #" & CDate(cbo_startdate) & "# " & _
"and [Period] <= #" & CDate(cbo_enddate) & "#) " & _
"ORDER BY (rogerreport.mntasts/rogerreport.allasts) DESC;"
[Forms]![Top50]![cbo_Mntasts-Allasts].RowSource = str_Source6
[Forms]![Top50]![cbo_Mntasts-Allasts].Requery
If anyone has any suggestions on what to alter to order the results correctly I would be very appreciative.
I have a calculated field Labeled [Hours/Month], simply enough, it is calculated by dividing one field [Hours] by the other field [Months].
The field calculates just fine as 0 or null values are not part of the data set. However, I am trying to write a query that parses this data where the calculation (Hours/Month) is greater than (750/36 or 20.83).
I would expect to just be able to plug ">750/36" into the criteria, but this gives an overflow error, as does ">20.83". I don't want to have to write this data set to a table before being able to add this simple criteria, why this is happening?
I have a separate front and back end for my database. I`m trying to merge them into one application, for easier distribution to users, but I`ve found that things that worked previously now produce errors, even though all I`ve done is import the tables as opposed to linking to them.
For example, the line:
Forms!Referrals!Paycode = str_Paycode
works perfectly if using linked tables, but if the links are all removed and the exact same tables are imported, I get the message 'Object invalid or no longer set'.
Is there some difference between linked and 'in-built' tables that I'm missing?
Hi all,
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")
Can anyone help please.
Cheers,
Spinkung. :)
circular references caused by alias 'filed name' inquiry definition select list? What should i do
thanks
I have (for this post) 2 tables.
department
====================
pk | d_id
--------------------------
| d_fund
fk1 | d_status_code_id
| d_department
fk2 | d_building_id
fk3 | d_contact_id
fk4 | d_note_id
| d_modified
| d_user
contact
====================
pk | c_id
--------------------------
| c_banner
| c_last_name
| c_first_name
| c_middle_initial
| c_job_title
fk1 | c_department_id
| c_department_text
| c_email
| c_phone
| c_modified
| c_user
As you can see there is a circular reference between them as each need the other's pk to ref a contact and department respectfully.
This will cause a problem during data entry will it not?
And if so, would removing the "req" status on either on remove the issue?
thanks
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?
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.
Regards,
Keith.
Is there a way for a SQL QueryDef object in Access to look up the name of itself in the SQL and return that as a value in the result set? Ex:
Code:
SELECT [foo],
[bar],
[something]
@NameOfQueryDef
FROM [table]
How to reference current row in SQL string? I have a qst while using RowSource. I have 3 tables:
[1] CITY
col: _value _name
[2] STREET
col: _city _value _name
[3] ADDRESS
cal: city street
The problem ocurred when i designed ADDRESS table:
The RowSource of [city] is "SELECT _name, _value FROM CITY".
And I set the binding-column as 2, so that _name can be displayed and the real value is _value(integer).
But here comes the trouble: how can i set the RowSource of [street]?
The _value of STREET table depends on _city, and _city is _value in CITY table.
E.g. _name of STREET is "Wall Street" when (_city,_value) = (1,1),
But "Lincoln Avenue" when (_city,_value) = (2,1)
I want to use "SELECT _name, _value FROM STREET WHERE _city=CurrentRow.city" as RowSource, but what can replace the "CurrentRow"?
I have a query that I referenced it to a combo box in a form. In my form I created a combo box which has a list of VendorName and I want my query to opens a VendorName report based on whatever chooses in the combo box so that I use the combo box name in the criteria of the VendorName in my query. Somehow I get an error which says "The Microsoft Office Access database does not organize "[Forms]![Form1]![Vendor_Name]" as a valid field name or expression".
View 10 Replies View RelatedAccess 2010. I have a query that I referenced it to a combo box in a form.
If I rename the combo box, save it, and then rename it back to the same name it works fine for a while and then stops working again.
I have a query which gets information from 2x tables where the I'd on one table is the reference number on the second table.I would like to know how I can remove the duplicates on my reference number field?
View 3 Replies View RelatedI have a table I'm trying to query information out of. Key fields are below:
RecNo (Key)
ParentRecNo
Description
I need to have a "record" in the query show both the description of itself, as well as it's parent. I was hoping to use Dlookup directly in the query design. If there is no other way, I suppose I could create a temp table and look through the recordset in VBA and dump them into the temp table, but like I said; quick and dirty was the hope here, it's for my own use, and the tables aren't large 50-100 records is what I'll be pulling out when I use this.
Basically what I have is a database for tracking/logging parcels that arrive to the office. I want to be able to generate a reference number based on the date of arrival: i.e. the reference number should be ddmmyy### where ### is a sequential number. I know that I could just use the primary key's autonumber, for the sequential number but if I do this then the sequence will not restart at 1 on each date and because we receive a lot of parcels the reference number will grow to be too big to print out on the collection slips in just a few months.
two tables (one with the date and staff on duty that day and the other with the parcel's info') with a one to many relationship
I also have a query (Named: FullLog) that picks up the following data from the tables:
Name - Description - Size - TrackingInfo' - Staff - DateReceived - Count
The field named Count is a DCount function that I used to find out the number of times each date is repeated. This is the Expression that I used:
Count: DCount("*","FullLog","DateReceived = " & [DateReceived]) [Note that DateReceived is first converted into a string using CStr()]
This is as far as I have been able to get, I have been looking for weeks for a solution to this problem but I have yet to find one. I don't even know if the DCount function is the correct way of doing it, I did read somewhere that this produces a very slow query.
Effectively what I want to be able to get is something of that resembles the following
DateReceived - ReferenceNo
051013 051013001
051013 051013002
051013 051013003
061013 061013001
061013 061013002
071013 071013001
071013 071013002
071013 071013003
071013 071013004
071013 071013005
081013 081013001
081013 081013002
091013 091013001
101013 101013001
I have a form bound to a query. The query result has a field that I would like to reference, however I do not want the field on my form. Is there a way to reference a field in the query results that is not on the form?
View 3 Replies View RelatedHey Guys!
I have a problem concerning the ability to make a reference to another column in the same query.
I have two columns. Column 1 works perfectly, but in column 2 I want the outcome of column1 (coachingdato1) inserted instead of #17-07-2006#.
How is that done???
Thanks in advance!
//JR
Column1:
coachingdato1: (SELECT Max(coaching.dato) FROM coaching WHERE sælgerdata.sælgernr = coaching.sælgernr)
Column2:
coaching1: (SELECT coaching.[1] FROM coaching WHERE sælgerdata.sælgernr = coaching.sælgernr AND #17-07-2006# = coaching.dato)
I have a query that currently calculates a rating based on various values entered into the database. This rating corresponds to a Quality Index value based in another table. I tried to set up a query that links the rating value in the query to the Rating in the table, however, when I run this query, I don't get all the results I should. Is there some other way to make this work? I thought it would be simple to link the rating value that is calculated to the rating in the table and have it report the Quality Index value associated with that value. Please help! :confused:
View 5 Replies View RelatedHi,
Trying to run an append query for a specific record on a subform but won't recognise the subform control.
The Master form is fmSickEdit do I have to reference the Master file in the SQL?
The SQL for the query is:
INSERT INTO tblDisLetter ( SickID, DateAdvisedDisciplinary )
SELECT tblSick.SickID, Date() AS [Date]
FROM tblSick
WHERE (((tblSick.SickID)=[Forms]![fmsubSickListEditVersion]![SickID]));
Thanks