Report for single record

I have a report that shows much data about a single record that I am trying 
to reuse from a previous database.  In my new DB, the project manager is set 
in an intermediate table between project data and contact data.

How do I reference a table outside the forms data source?

Can I run a query in a form without having to build a query?
0
Utf
2/6/2008 8:59:01 PM
access.reports 4434 articles. 0 followers. Follow

3 Replies
619 Views

Similar Articles

[PageSpeed] 10

Several possibilities.

If you just need to look up a single value from another table so you can 
limit the report to the correct value, DLookup() should do that for you. 
See:
    Getting a value from a table: DLookup()
at:
    http://allenbrowne.com/casu-07.html

After retrieving the correct value from the table, you can then open the 
report filtered to just that one record by using the WhereCondition of 
OpenReport. Here's an example:
    Print the record in the form
at:
    http://allenbrowne.com/casu-15.html

Alternatively it may be possible to create a query that uses both your main 
table and the lookup table, and use this query as the RecordSource for the 
report. If you can do that, you can skip the DLookup() step above, and just 
inlcude the other table's value in the WhereCondition.

A third possibility is to use a subquery, but I that's more complex and less 
efficient. More info:
    http://allenbrowne.com/subquery-01.html

It is also possible to write the SQL property of the QueryDef, but you 
probably don't need to go that far.

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"MeSteve" <MeSteve@discussions.microsoft.com> wrote in message
news:F8EAA963-EF2B-46F4-A627-4852CD4FDF50@microsoft.com...
>I have a report that shows much data about a single record that I am trying
> to reuse from a previous database.  In my new DB, the project manager is 
> set
> in an intermediate table between project data and contact data.
>
> How do I reference a table outside the forms data source?
>
> Can I run a query in a form without having to build a query? 

0
Allen
2/7/2008 12:10:10 AM
I am trying to use the DLookup method.  Here's where I got to:

I used Dlookup as the source for the control on the report.

=DLookUp("ContactName","tbl_Contacts",DLookUp("ContactID","tbl_ContactProject","RoleID = 1"))
--not really word wrapped--

This returns the first person it finds with a RoleID of 1 (I think that's 
what its doing)
regardless of the filter set on the report.  I also switched from using a 
macro to using VBA you linked to.  Any guidance?

"Allen Browne" wrote:

> Several possibilities.
> 
> If you just need to look up a single value from another table so you can 
> limit the report to the correct value, DLookup() should do that for you. 
> See:
>     Getting a value from a table: DLookup()
> at:
>     http://allenbrowne.com/casu-07.html
> 
> After retrieving the correct value from the table, you can then open the 
> report filtered to just that one record by using the WhereCondition of 
> OpenReport. Here's an example:
>     Print the record in the form
> at:
>     http://allenbrowne.com/casu-15.html
> 
> Alternatively it may be possible to create a query that uses both your main 
> table and the lookup table, and use this query as the RecordSource for the 
> report. If you can do that, you can skip the DLookup() step above, and just 
> inlcude the other table's value in the WhereCondition.
> 
> A third possibility is to use a subquery, but I that's more complex and less 
> efficient. More info:
>     http://allenbrowne.com/subquery-01.html
> 
> It is also possible to write the SQL property of the QueryDef, but you 
> probably don't need to go that far.
> 
> -- 
> Allen Browne - Microsoft MVP.  Perth, Western Australia
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
> 
> "MeSteve" <MeSteve@discussions.microsoft.com> wrote in message
> news:F8EAA963-EF2B-46F4-A627-4852CD4FDF50@microsoft.com...
> >I have a report that shows much data about a single record that I am trying
> > to reuse from a previous database.  In my new DB, the project manager is 
> > set
> > in an intermediate table between project data and contact data.
> >
> > How do I reference a table outside the forms data source?
> >
> > Can I run a query in a form without having to build a query? 
> 
> 
0
Utf
2/7/2008 2:59:00 PM
Don't yo uhave the ContactID and RoleID in the report?
If so:
    =DLookUp("ContactName","tbl_Contacts",
    "(ContactID = " & [ContactID] & ") AND (RoleID = " & [RoleID] & ")")

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"MeSteve" <MeSteve@discussions.microsoft.com> wrote in message
news:49BFD278-ED2E-4DEA-ADAF-3BE969AAF17C@microsoft.com...
>I am trying to use the DLookup method.  Here's where I got to:
>
> I used Dlookup as the source for the control on the report.
>
> =DLookUp("ContactName","tbl_Contacts",DLookUp("ContactID","tbl_ContactProject","RoleID 
> = 1"))
> --not really word wrapped--
>
> This returns the first person it finds with a RoleID of 1 (I think that's
> what its doing)
> regardless of the filter set on the report.  I also switched from using a
> macro to using VBA you linked to.  Any guidance?
>
> "Allen Browne" wrote:
>
>> Several possibilities.
>>
>> If you just need to look up a single value from another table so you can
>> limit the report to the correct value, DLookup() should do that for you.
>> See:
>>     Getting a value from a table: DLookup()
>> at:
>>     http://allenbrowne.com/casu-07.html
>>
>> After retrieving the correct value from the table, you can then open the
>> report filtered to just that one record by using the WhereCondition of
>> OpenReport. Here's an example:
>>     Print the record in the form
>> at:
>>     http://allenbrowne.com/casu-15.html
>>
>> Alternatively it may be possible to create a query that uses both your 
>> main
>> table and the lookup table, and use this query as the RecordSource for 
>> the
>> report. If you can do that, you can skip the DLookup() step above, and 
>> just
>> inlcude the other table's value in the WhereCondition.
>>
>> A third possibility is to use a subquery, but I that's more complex and 
>> less
>> efficient. More info:
>>     http://allenbrowne.com/subquery-01.html
>>
>> It is also possible to write the SQL property of the QueryDef, but you
>> probably don't need to go that far.
>>
>> "MeSteve" <MeSteve@discussions.microsoft.com> wrote in message
>> news:F8EAA963-EF2B-46F4-A627-4852CD4FDF50@microsoft.com...
>> >I have a report that shows much data about a single record that I am 
>> >trying
>> > to reuse from a previous database.  In my new DB, the project manager 
>> > is
>> > set
>> > in an intermediate table between project data and contact data.
>> >
>> > How do I reference a table outside the forms data source?
>> >
>> > Can I run a query in a form without having to build a query? 

0
Allen
2/7/2008 3:53:48 PM
Reply:

Similar Artilces:

Can't edit publisher_address_list after appending records with Acc
With Office 2000 if I create a publisher_address_list and then use Access to append records to it, I cannot use the Edit publisher_address_list feature in Publisher to edit or add records. Is there a setting I need to change? ...

Parameter query in Access 2003 report
I want to create a parameter query that asks the user to select one, many, or all values from a field when opening a report. I would like the selection to be available from a list, radio box, or pull down menu to minimize spelling errors, etc. Unfortunately the list is of names which number about 100. Can this be done? If so, could someone please point me to the help pages that describe this process? My solution seems clunky but it is the only way I can see how to do it. Any suggestions as to how else might I best approach this? Thanks in advance Debbie See my sample database that shows ...

Single corrupted mail item, how to delete it?
Hi, Running EX2k3 on a 2k server. Everytime I do my backup I am getting an "The item \\servername\microsoft exchange mailbox\username top of information store inbox (a whole bunch of asian characters then normal email heading ) is corrupt, file skipped" I 've gone to the users inbox and there is not an email with a whole bunch of asian characters nor with the english after the asian characters. Is there a way to scan a single users mailbox for corrupted items and fix it? Maybe export to a PST and do something like that? Thanks R not possible to scan for one mailbox co...

Item record in RM00101
Does anyone know how an item record is entered into RM00101? I only have one in there and I can't figure out how it got there. Thanks. Chris, Do you mean there is an inventory item in there? If so, most likely there was either a data entry error or an import direct to table error. If that's not what you mean, can you please elaborate? -- Victoria Yudin Dynamics GP MVP Want to use Crystal Reports with GP? http://www.flex-solutions.com/gpreports.html blog: http://www.victoriayudin.com "Chris" <Chris@discussions.microsoft.com> wrote in message news:AEA12C29-07...

User Frequency Report
Is there a way to get a user frequency report in Exchange 2000? I need to determine what users are not using their email to be disabled. Thanks! ...

Report filter question
Hi, I've done filters many times before, but something is not right and I can not see it. My report is running of a query which has NO selection criteria in it. It is just a logical view of a table. I have the following code in my report (it is hard coded only because I'm trying to debug the code": Me.Filter = "(((tblMailingList.MemType)='MT-S'))" Me.FilterOn = True I know that the filter code is correct because I went into design query and put MT-S for the Criteria in the field MemType and view the resulting SQL cod...

Pivot Table Report: Getting average of difference of two date columns
Hello I have data in excel sheet as: WORK_ID PROJECT START_DT END_DT 1001 P1 1/1/2006 1/10/2006 1002 P1 1/25/2006 2/20/2006 1003 P1 3/15/2006 3/20/2006 I want to have a report that will give me average time taken to finish a work in a project. That is, I have to take average of (end_dt - start_dt) for a project group. How do I do this in a pivot table report. When I use a calculate field to get the days worked for a work_id using DATEDIF(START_DT, END_DT, "D") then excel computes only sum on that (pivot table report ...

Access 2007 ControlSource code in Report causing shut down
The following code worked successfully in Access 2002 & 2003 but for some reason, the lines that contain ControlSource are causing Access 2007 to shut down and error out. Any suggestions you have would be greatly appreciated. In short, I’m using a combo box on a form “ReportCenter” to change the GroupLevel and textbox values on a report. If I comment out the lines with ControlSource the code runs successfully, if any one of them are uncommented, Access 2007 shuts down and restarts. 'Group 1 If Forms![ReportCenter]!T2GroupCheck1 = True And Forms![ReportCenter]!T2GroupDescendC...

VBA to count periods in a single cell
I am looking to count the number of periods within a single cell using vba. For instance if I have 4 cells that contain the following: 5M09-0000700.01.10 5M09-0000700.01.10.10 5M09-0000700.01.10.10.10 5M09-0000700.01.10.10.20 Thus the output of the code for the first cell should be '2', the second cell output would be '3', and the third/fourht cells would be '4' because that many period characters were encountered. Data within these cells will contain letters, numbers, periods, a few spaces and the "-" dash symbol. Any help is greatly appre...

tax software report
I forgot to say that I am working with Money 2002. I did override of Money calculated in Tax Estimator. Then Tax Software Report would not load. I did a restore backup of Money 2002 and got message that because changes to tax were saved I would have to manually restore Tax Software Report. I can find no information in help in Money as to how you manually restore Tax Software Report. Any suggestions would be greatly appreciated. With all of the problems I have had with Money 2002 over the years, I am afraid to spend money on the Money 2004. Will upgrading be a wise investment? Thanks...

Crytal reports error in 1.0
Hi, I installed CRM 1.0 on a new server (W2K Enterprise) and I get the following error when trying to access a sales report. Any ideas??? File Repository Server Input is down. [On Cache/Page Server: SHEFFIELD.pageserver] I can confirm that Crystal Input File Repository service (along with all other Crystal services) is running. I could find no reference to this error in PartnerSource. Thanks, Lindsay. ...

Problems with Historical Aged Trial Balance Report
I want to use this report in Reports>Sales>Trial Balance After I configured it but there are some customer that appear with the data in the wrong column (current/0-30 days) according their payment’s form. Thank in advance, Jose G. Jose, Could you provide more infomation? What data appears in the wrong column, invoices? credit memos? Also, what payment methods are you referring to? How are they setup? Best regards, -- MG.- Mariano Gomez, MIS, MCP, PMP Maximum Global Business, LLC http://www.maximumglobalbusiness.com "jose garcia" wrote: > I want to use this repo...

VENDOR CHECK REGISTER REPORT
Can anyone tell me where to find this report Brian, There is no separate Vendor check register report. You can always take the payment register for vendors by going to Smartlist, Payables Transactions, Apply filter to return those transactions by document type - Payments, you'll get the vendor payments alone. You can further apply date filters and checkbook filters to take the payments by date, by bank. You can then save it as Favorite, so everytime you need not modify the same. -- Thanks Janakiram M.P. MCP-GP http://janakirammp.blogspot.com "Brian" wrote: > Can a...

error in reporting service
when i restart the crm server the crysatl APS service allows stop and it must be started manualy in the event vewier i found tis errore after restarting the crm server source: Crystal_CrystalAPS event id : 35101 The root server reported an error Initialization Failure. (Reason: Unable to connect to the database using the provided connection string. Reason: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open database requested in login 'Adventure_Works_Cycle_CRMCRYSTAL'. Login fails.). do you have an idea thanx Are CRM and SQL installed on the same box? If so, my gue...

reporting a/c adjustments
I am using Money 2000. When I update the balance of my loan accounts I assign a category for the adjustment, but the adjustments do not show up in my reports. Any suggestions? ...

non delivery reports
I have upgraded a server from server 2003 to SBS 2003. I have installed and configured exchange and all seems to be working ok. The problem I am having is I cannot get a copy of Non-Delivery Reports to be copied to any of my users mailboxes (I have tried them all I have also creating aliases but still no joy) upon further investigation I discovered that is I un-tick allow non delivery reports under Internet Message Formats, Default, Advanced, I will still get an NDR returned to sender. I have restarted Microsoft Exchange Routing Engine and SMTP services and also rebooted the server. I h...

How do I put a landscape table into a portrait report
I am writing a report in portait and want to place a table that is in landscape into the report. . . how do I do this? Thanks, See http://word.mvps.org/FAQs/Formatting/LandscapeSection.htm. Although the stated purpose of this article is to explain how to put a portrait page number (or header/footer) on a landscape page, it does start by explaining how to create the required landscape section for your table. -- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope, Alabama USA http://word.mvps.org "Gwen" <Gwen@discussions.microsoft.com> wrote...

Filter eliminating records without and detail records
I need a filter to be able to hide records that don't have any records in the detail form. My example is Member Master and Event Detail. If there is no data in the Event Detail, I need to hide that Member Master record. Change the Can Shrink property in the form to yes. "Jeannie" wrote: > I need a filter to be able to hide records that don't have any records in the > detail form. My example is Member Master and Event Detail. If there is no > data in the Event Detail, I need to hide that Member Master record. I think you have a main form bound to the [Memb...

Deleting blank records when aform is closed or a button is click.
We are have a somewhat of an annoying problem with one of our database forms. Everytime we open the form it creates a new record, even if you close the form or cancelled it. We are trying to figure out a way to not create a blank record in our database everytime the form is opened. I tried: DoCmd.RunCommand acCmdDeleteRecord DoCmd.Close acForm, Me.Name DoCmd.OpenForm "Invoice Database Launch", acNormal but the blank record is still showing up. So, is there a way to tell access not to create a record if the form is close or the cancel button is clicked. Thanks "Ayo&qu...

crosstab report colum headings
Hi, I have a crosstab report based on Duane Hookom's crosstab database. It works very well and I appreciate all the help I received last year in getting it to work. Now, I have a lot of data to report and I am trying to condense it down to as few pages as possible. I can get the data (detail) to be 10 columns wide in the report and still be readable. However, the last two column headings wrap into the next row even though there is plenty (at least it appears that way) of width room left on the page. I have tried various things, but really do not know how to make it n...

new appointment --> record unavailable error
When trying to create a new appointment, upon save get the error: "The record that you are requesting is currently unavailable. Either the record was not found or you do not have sufficient security permissions to view it." Upon clicking "Ok" I get the error: "General failure in scheduling engine" with a "Ignore and Save" button which will save the appointment, but still marks the appointment as having a General failure in scheduling engine from then on. I am going crazy on this one, anyone a suggestion? thanks, Robert the first error might be ...

Salvage Result
Does anybody know how to fix a money file ? I mean, if the salvage tool doesn't work ? Do you have any problems besides this message? Many people do not have problems but get this message. Given this, some of us just consider that a spurious message and get on with life. "Persio Aran" <Persio Aran@discussions.microsoft.com> wrote in message news:EB7AA917-9119-4A30-B723-E5C5DB5FB4CF@microsoft.com... > Does anybody know how to fix a money file ? I mean, if the salvage tool > doesn't work ? ...

Removing Item Site Records
Is there a way to remove unused Item Site Records? (item quantities maintenance window) all at one time? We have contracts that continuously end, so we will never use the component again, All these records have zero inventory. We have about 200,000 records that are empty. -- Doug If you have access to Customer Source search it. There is a SQL script to remove them. Not sure if I can post the script here, but basically you delete records from IV00102 where the Locncode = 'your site' and the qtys are zero "Doug" wrote: > Is there a way to r...

Error after importing organisation
Hi, After importing one organisation into another, we are getting the error 'Record is Unavailable. The selected record was not found or you do not have sufficient permissions to view it'. We get this when just trying to load/access the home page on crm 4.0. Any suggestions? Many thanks Matt ...

Captial Gains Report Omits Sale of Bonds
I sold 2 corporate bonds in 2007. They were reported by my brokerage on a 1099-B along with 4 mutual funds that were sold at the same time. When I ran the Capital Gains report in Money 2007, the funds showed up, but not the bonds. All of the transactions were entered at the time they were transacted. Any reason why? Any setting that needs to be tweaked? Thanks -- David Schwartz Commack, NY In microsoft.public.money, David Schwartz wrote: >I sold 2 corporate bonds in 2007. They were reported by my brokerage on a >1099-B along with 4 mutual >funds that were sold at the s...