Because you have a field named MemberType in both the Addresses and MemberTypes table, I assigned an "alias" name (AS MemberTypeDesc) to the field coming from the MemberTypes table so that the names would be unique. When you link two or more tables in a query, you need to JOIN them on related key values so that the query data is upatable. I chose an INNER JOIN so that the query returns only rows that match in both tables. If you want to return all rows from Addresses and any matching rows from MemberTypes, change the syntax to
FROM MemberTypes RIGHT JOIN Addresses ON ... If you open your form in Design view, select the Record Source property, and click the Build (...) button next to the property, you'll see the query in the query design grid. The line you see drawn from MemberTypeID in MemberTypes to MemberType in Addresses is the result of the INNER JOIN specification. John Viescas, author "Building Microsoft Access Applications" "Microsoft Office Access 2003 Inside Out" "Running Microsoft Access 2000" "SQL Queries for Mere Mortals" http://www.viescas.com/ -----Original Message----- From: ms_access@yahoogroups.com [mailto:[EMAIL PROTECTED] On Behalf Of Hassan Sent: Sunday, November 06, 2005 7:03 PM To: ms_access@yahoogroups.com Subject: [ms_access] Re: Report Help! Once again thanks alot for helping me as i am helping them vlouteerly. I am not sure to why in the one sample row, I have number 1, i think it created that it self. I was able to make it work after following your directions. I notice you have used words like DESC and inner join in your SQL statement. Could you explain me to what are their function?? Thanks again --- In ms_access@yahoogroups.com, "John Viescas" <[EMAIL PROTECTED]> wrote: > > I see MemberType is text, but in the one sample row, you have the number 1 > in that field. If 1 = Yearly Member, then change MemberType to Number, Long > Integer. On the Lookup tab, make these settings: > > Display Control: Combo Box > Row Source Type: Table/Query > Row Source: MemberTypes > Bound Column: 1 > Column Count: 2 > Column Heads: No > Column Widths: 0";1" > > Now change the Record Source of your report to: > > SELECT Addresses.*, MemberTypes.MemberType AS MemberTypeDesc FROM > MemberTypes INNER JOIN Addresses ON MemberTypes.MemberTypeID = > Addresses.MemberType WHERE (((Addresses.Status)="Active")); > > And finally, change the Control Source of the Type text box to > MemberTypeDesc. > > John Viescas, author > "Building Microsoft Access Applications" > "Microsoft Office Access 2003 Inside Out" > "Running Microsoft Access 2000" > "SQL Queries for Mere Mortals" > http://www.viescas.com/ > > > -----Original Message----- > From: ms_access@yahoogroups.com [mailto:[EMAIL PROTECTED] On Behalf > Of Hassan > Sent: Sunday, November 06, 2005 3:40 PM > To: ms_access@yahoogroups.com > Subject: [ms_access] Re: Report Help! > > Thanks John for a quick reply, > yuo are almost right data type is set as text. In the main properties of my > report i have this command which sees only active members, as my filter is > set for active only. > Record Source: > SELECT Addresses.* FROM Addresses WHERE (((Addresses.Status) ="Active")); > > filter: ([Status]=1) > And yes i am taking the active/expired field from another table. > Now how can i change my lookup to value list so i can try your command?? I > am new to access and trying very hard to built this. i am going to include > my database here for you. > http://www.megaupload.com/?d=P5AAU40M > > Thanks alot > > > > I would guess that you defined member types as a lookup, but the data type > > is a number. If the lookup is to another table, simply include > that table > > in your Record Source query and display the name, not the number. > If your > > lookup is a Value List, then you can use the Choose function: > > > > =Choose([MemberType], "Premium", "Full", "Newsletter") > > > > Make sure that you name the text box on your report that has the > above as > > its Control Source something other than "MemberType". > > > > > > ------------------------ Yahoo! Groups Sponsor -------------------- ~--> Get > Bzzzy! (real tools to help you find a job). Welcome to the Sweet Life. > http://us.click.yahoo.com/A77XvD/vlQLAA/TtwFAA/q7folB/TM > -------------------------------------------------------------------- ~-> > > > Yahoo! Groups Links > ------------------------ Yahoo! Groups Sponsor --------------------~--> Most low income households are not online. Help bridge the digital divide today! http://us.click.yahoo.com/cd_AJB/QnQLAA/TtwFAA/q7folB/TM --------------------------------------------------------------------~-> Yahoo! Groups Links ------------------------ Yahoo! Groups Sponsor --------------------~--> Most low income households are not online. Help bridge the digital divide today! http://us.click.yahoo.com/cd_AJB/QnQLAA/TtwFAA/q7folB/TM --------------------------------------------------------------------~-> Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/ms_access/ <*> To unsubscribe from this group, send an email to: [EMAIL PROTECTED] <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
