field returns a value if at least one matching record is found

I had posted this question in the Forms thread, but that was probably the 
wrong place.

TblPartNumInfo
TblDefectRpt

My query for PartNumInfo brings together lots of different tables including 
customers, contact info, status of jobs. A feature I need to add is a warning 
if at least one defect report exists for a given part number. It would be 
shown on the form as a conditional format of the text box where the part 
number is shown.

So I would like to add a field to my query that would return some value 
(like the part#ID) if at least one Defect Report exists for a part#. I 
created a separate Select Query & joined it to my main Query on the Part#ID 
field. Works great, but now the main query becomes uneditable. 

Tried using domain aggregate directly on form, which works but makes the 
form very sluggish, as it is a long list of part numbers. Any better way to 
do this? A subquery? Haven't tried one of those yet & don't know how. 

Thanks in advance!
0
Utf
5/16/2007 2:21:02 PM
access.queries 6343 articles. 1 followers. Follow

1 Replies
911 Views

Similar Articles

[PageSpeed] 19

Like your name. Check out my tag line.

If you are talking about forms, the best way would be a Defect Report 
subform or subreport. That way the Defect would be on the form, yet the data 
on the main form should remain editable. 
-- 
Jerry Whittle, Microsoft Access MVP 
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

"bicyclops" wrote:

> I had posted this question in the Forms thread, but that was probably the 
> wrong place.
> 
> TblPartNumInfo
> TblDefectRpt
> 
> My query for PartNumInfo brings together lots of different tables including 
> customers, contact info, status of jobs. A feature I need to add is a warning 
> if at least one defect report exists for a given part number. It would be 
> shown on the form as a conditional format of the text box where the part 
> number is shown.
> 
> So I would like to add a field to my query that would return some value 
> (like the part#ID) if at least one Defect Report exists for a part#. I 
> created a separate Select Query & joined it to my main Query on the Part#ID 
> field. Works great, but now the main query becomes uneditable. 
> 
> Tried using domain aggregate directly on form, which works but makes the 
> form very sluggish, as it is a long list of part numbers. Any better way to 
> do this? A subquery? Haven't tried one of those yet & don't know how. 
> 
> Thanks in advance!
0
Utf
5/16/2007 4:34:04 PM
Reply:

Similar Artilces:

Can't get the proper display of a field in my report.
I have 2 tables, both using autonumbers for their primary key. The first table is for contacts (i.e. last name, first name, etc.). The second table is for businesses (business name, etc.) I have a field in my contacts table that has a number format so it can be used as a foreign key for the business table. I then set up the relationship between them & enforced referential integrity. When I run a query, I see the name of the business (after setting up a combo box) - no problem. When I run a report based on that query, a number is displayed (not the business name). Suggestions, pleas...

format changes when copying from one cell to another
I'm sure this is simple, but it is not clear to me... I am copying the contents of a group of cells that exist on one worksheet to another. In Sheet2, I tell cell A1 that it should equal Sheet1's A1 value. That works fine. But if the cell is blank on Sheet1, it comes up as "0" on Sheet2. I figured it was probably assuming numeric formatting, but when I right-click > format cell on both Sheet1 and Sheet2, both show up as "general", so why would Sheet2 take a blank value and populate it with "0" ?? I gather than you are putting a formula in one...

Move to match cells
A B C D E 1 000801025-8 009601025-3 2007 000801025-8 2007 2 000801026-9 009601026-4 2004 000801026-9 2004 3 000801028-1 009601028-6 2006 000801028-1 2006 4 000801036-8 009601036-3 2008 000801036-8 2008 5 000801044-5 009601044-0 2007 000801044-5 2007 6 000801044-5 009601055-0 2004 000801055-5 2004 7 000801055-5 009601064-8 2001 000801064-3 2001 8 000801055-5 009601091-2 2006 000801091-7 2006 9 000801064-3 009601095-6 2002 000801095-1 2002 10 000801091-7 009601097-8 1998 000801097-3 1998 11 000801091-7 009601098-9 2004 000801098-...

Controlling printed records when report bound to multiple tables
I created a report that uses the control from a form to generate a report based on that record's primary key. This form also has a subform which has relationships tied to the primary key for record identification and is linked to the main table. When preview the report the data from the subform either does not show up in the preview when using the filter [control]=[form]![control].[value] or makes multiple copies of the report equal to the number of entries in the subform's table. Is there any way around this? I have tried queries but have not found a way to use a f...

Receive mails from one adr. to several computers
Hello. I have a problem receiving mails from one adr. to 5 computers in a network. One of the computers get's all the mails. They should all receive the mails. All the machines run's win Xp and office Xp. Outlook on all computers are set to store the mails on the server, even though they have been received on a computer. When I check my WebMail, the messages are still there! Please help. Nima ...

Just saved file, cannot be found. ?
Even Global search ? Win 7 ... Whatz up? if you saved it, it should be in the mru list? what version of excel are you using? -- Gary Keramidas Excel 2003 "Boswell" <Boswell@discussions.microsoft.com> wrote in message news:E9D6ED74-ABC5-469D-9572-2D8AE93F36CD@microsoft.com... > Even Global search ? > > Win 7 ... > > Whatz up? one other question, how did you open it, outlook email? if that's the case, search will never find it. it is probably here: C:\Users\Your user name\AppData\Local\Microsoft\Windows\Temporary Intern...

Case select returning error when cell contains #N/A : how must i avoid this error
Title says it all Thanks, Luc maybe this will do what you want Sub test() If Not IsError(Range("C1").Value) Then Select Case Range("C1").Value Case 1 MsgBox "1" Case 2 MsgBox "2" End Select End If End Sub -- Gary Keramidas Excel 2003 "Luc" <lferr@live.be> wrote in message news:7AB07996-F1EE-41C1-8D9D-8A7DA54CED91@microsoft.com... > Title says it all > > Thanks, > > Luc Thanks for your help !!!!! Luc "Gary Keramidas" <GKeramidasAtMSN.com> schreef in berich...

Developing a robust database at one co. then using it at another c
Are there any legal implications of using the type of database listed above to perform similar functions for another company? The 2 company's are in 2 totally different industries and the new database will have to be modified to fit the requirements for the industry it is in. The database was developed at the old company. there is no such thing as a robust Jet database. Move to SQL Server if you want to build a solution that will work for the next decade. Jet is and always has been depecrated On Apr 7, 12:14=A0pm, BoaMan10 <BoaMa...@discussions.microsoft.com> w...

Transferring Field from Existing Table/limitations and change of d
Thank you in advance for your help! I have two Excel spreadsheets that I successfully imported into Access 2003 and created tables for. I need to add the field from one table to the other, but there is not a direct match in the relationship. The large table uses the Employee ID as the primary key. The smaller table contains one field that lists a subset of these Employee ID numbers (a selection of certain employees). I need to transfer this field to the larger table, but I do not know how to tell Access to match up the corresponding numbers (i.e., the large table lists all employees, bu...

How do I TRIM a field in existing fields....?
I didn't realize when I imported some data into my database that there were a bunch of spaces after all the data. I know that I can do a RTRIM the data in a query, but I don't want to have to remember every time I create a query to TRIM it. Do I have to use a query and make a temp table and then delete the data from my original table and then put the data back in from my temp table. I can do this, but wanted to know if there is a better way. Thanks Kelvin Kelvin A standard approach to importing (and "cleaning") data is to import/append to a temporary table, then ...

How to get TASK_ID field for summary tasks without using Project.a
I know for tasks which are not summary tasks we can get TASK_ID field using statusing web service. But i could nto find any other options than Project web service to get TASK_ID field for summary tasks and the top level project task. Problem of using Project web service is that in my custom sharedpoint web part where we are using PSI web services we get all the data required using Resource and Statusing web service for the logged in resource. But Statusing web service retrieves TASK_ID only for actual tasks and not for summary tasks. Now just to get TASK_ID of summary tas...

validation list or combo box dependant on cell value
Am i able to determine the values shown in either a validation list or combo box being dependant on a value in another cell? ie: Cell A1 = BOB then validation or combo box would then base it's list from the named range (or whatever the solution may be) based on Bob. if i was to change A1 to ROY then it would also change the underlying list? I have tried everything that i think SHOULD work but that it pretty limited... thanks in advance rich I'd start with Debra Dalgleish's site: http://contextures.com/xlDataVal02.html Richard Edwards wrote: > > Am i able to deter...

how to compare 2 values in a report (Invoice Total vs Payments)
while running a report how would I set a message "out of balance" if my invoice amount (Table 1 ) does not equal the value of my total payments (table 2). If the values were the same then no message would be printed. Thank You Create a new command button to check the report before you print it. You will have to enter code for the button along the lines of: If [Invoice Total] <> [Payments] Then MsgBox "This account is out of balance" Endif "Rita" wrote: > while running a report how would I set a message "out of balance" if > my invo...

if value not found in table ?
Hello all I need to display a msgbox if a value is not found in a table. Something like: If value not_in table.field then msgbix end if I know that code wont work is just an ilustration of what i am looking for Thanks in advance Use DLookup() to see if the value is in the table. If it's not found, the result will be Null. So, use IsNull() to test the result. Here's how to get your Dlookup() expression working: Getting a value from a table: DLookup() at: http://allenbrowne.com/casu-07.html -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access us...

Report repeats a field
Example: John Smith 2,14 5,27 3,18 John Smith 3,17 4,27 7,34 John Smith 1,22 6,57 8,92 I want that the report shows a name(John Smith) only one time like this: John Smith 2,14 5,27 3,18 3,17 4,27 7,34 1,22 6,57 8,92 The report get the informations from a Query that get the information from a table. One way to do this would be to set the "name" control's Hide Duplicates property to Yes. Another way to do this would be to use Sorting & Grouping, then Gro...

Help on adding values to a bar chart
Hello, Here is my problem. I currently have a bar chart that has 8 X-axis bars. I need to add 2 more to the X-axis. The labels for the x-axis are based on cells within another spreadsheet, but are not consecutive cells, they are spread apart. So when I go to add the two more cells, I can only add one. When I click on the second cell to add it to the list, all of the previous cells get unselected. I have even tried to manually add these two values in. Is there a limit to how many values can be on the X-axis? Please Help!!! Thanks. To select non-consecutive cells: select first cell, h...

Populating work sheet combox with another work sheet values
Hi All, I have a combox in my sheet(1) which i want to populate with values present in sheet(2). I can populate combox in sheet(1) by setting the "ListFillrange"property to the required ranges in the sheet(1) (assume in coulmn A i have values from A1: A5) But i am unable to populate same combox with sheet(2) values(assume in sheet(2) C column i have set of values from C1: C10). I want to populate without using any macros. Can anyone Please help me to solve this problem Thanks in advance -- sjayar ------------------------------------------------------------------------ sj...

multiple balances on one printout
I want to print all the months on one sheet. with a total, is this possible and if so how do I do so. Thanks ...

How to merge columns and rows into one cell besides using Merge and Center Icon?
I had posted this question before, but I couldn�t find this thread in any of the pages up till page 17. So I�m posting again. I want to merge all rows and columns starting from A1 to J2, with no lines in between into one cell. My text data value are in D1 and D2 respectively. Using Merge and Center Icon will only retain the upper-left most data, resulting the data in D2 to be deleted. So how to merge all rows and columns into one cell and yet prevent the data in D2 to be deleted? Any help will be greatly appreciated. ------------------------------------------------ ~~ Message posted from h...

deleting duplicate value in columns
What is the best method to delete rows containing dulicate (same number) numbers in a column (Excel 2000)? The workbook has over 30,000 rows which makes it impractical to sort and then delete the duplicates page by page. Thanks in advance. Hi Jim have a look at http://www.cpearson.com/excel/deleting.htm#DeleteDuplicateRows Frank Jim Brokenshire wrote: > What is the best method to delete rows containing dulicate (same > number) numbers in a column (Excel 2000)? The workbook has over > 30,000 rows which makes it impractical to sort and then delete the > duplicates page b...

Cond. Formatting depending of value in another sheet
Hi all, I've tried to do conditional formatting whether the cellvalue i another sheet is greater or smaller than the cellvalue I have in th active cell. I've tried to do "formula is" IF(B34<Sheet1!B34)and format, but it won work. Any help is apprecciated!! //Thoma -- Message posted from http://www.ExcelForum.com Hi Try: =B34<Sheet1!B34 -- Andy. "Jonsson >" <<Jonsson.1635tc@excelforum-nospam.com> wrote in message news:Jonsson.1635tc@excelforum-nospam.com... > Hi all, > > I've tried to do conditional formatting whether the ...

CRM API: Converting an Object Type value -> ObjectType
I've setup post callouts in CRM which notify me when a CRM object has been modified. CRM sends me the ObjectType value so I know what type of object has been modified. For example 1 for an Account, 132 for an Activity... I can't figure out however how to convert the number back into an ObjectType. In theory it should be very simple, but from what I can tell the ObjectType class is NOT an Enum, oddly enough. I've tried the following: int objectTypeValue = 2; // which is a Contact // returns otActivity test = (ObjectType) Enum.ToObject(typeof(ObjectType), objectTypeValue); // r...

FORM Calculated Field
I am trying to attempt a simple percentage calculation to provide our sales team for the Opportunity Entity. I have 3 fields 1. estimatedvalue (Est. Revenue)- money 2. closeprobability (Probability) - int 3. dsi_weightedrevenue (Weighted Revenue) - int I need the following calculation to populate my Weighted Revenue (int) field as a percent estimatedvalue X closeprobability = dsi_weightedrevenue I have tried the following script adding it to the OnLoad event of the form being sure to select the "Event is enabled" check box there as well. But after publishing I get nothing but a...

Auditing form on record change
HI, I have a form with multiple fields that I want to track changes to. I was able to create a auditing trail when users click on particular buttons however if they dont click on the buttons the auditing does not occur. What I am wanting to do is to run the audit when a user changes to a new record. I have tried the "on current" and "before update" but they didnt work. Any suggestions? Take a look at what Allen Browne has at http://www.allenbrowne.com/AppAudit.html -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "...

linking (many) multiple cells to one worksheet
Hi. I have created (so far) a work book for the sales people in my offic that shows: how many leads they have received (via mail, internet an other) vs. how many sales that person has made. The work book is set u so each month is on a separte work sheet and has its respective totals with links to another worksheet that links those totals to show ho each sales person has been doing for the entire year (i will call i the "total" worksheet). In each month (and on the 'total' worksheet there is also a row for "House" sales - which is a total of leads/sale received from...