If this correct use of Select

Hi there

I want to use the following statement in the RowSource for a report but am 
unsure of whether it's correct:

SELECT *, *
FROM T_Participants RIGHT JOIN T_General ON T_Participants.[Student_ID] = 
T_General[StudentID_FK]; 

Does this look correct to anyone?

Thank you in advance for your help.
0
Utf
6/5/2010 2:29:32 AM
access.modulesdaovba 1670 articles. 0 followers. Follow

3 Replies
1105 Views

Similar Articles

[PageSpeed] 59

On Fri, 4 Jun 2010 19:29:32 -0700, forest8 <forest8@discussions.microsoft.com>
wrote:

>Hi there
>
>I want to use the following statement in the RowSource for a report but am 
>unsure of whether it's correct:
>
>SELECT *, *
>FROM T_Participants RIGHT JOIN T_General ON T_Participants.[Student_ID] = 
>T_General[StudentID_FK]; 
>
>Does this look correct to anyone?

Not really. The SELECT * syntax means to select all fields (from all tables);
so by using the asterisk twice you're saying SELECT each field twice. I expect
Access will assign an alias fieldname to one of the duplicates. The RIGHT JOIN
will cause all records in T_General to be included, whether or not they have a
match in T_Participants; if they do have a match you'll get all the field
values in T_Participants, otherwise those fields will be in the report's
recordsource with NULL values.

Could you explain what fields are in the two tables, and what you intend this
query to accomplish?
-- 

             John W. Vinson [MVP]
0
John
6/5/2010 5:54:27 AM
I was planning on using the select in the rowsource of a report.



"John W. Vinson" wrote:

> On Fri, 4 Jun 2010 19:29:32 -0700, forest8 <forest8@discussions.microsoft.com>
> wrote:
> 
> >Hi there
> >
> >I want to use the following statement in the RowSource for a report but am 
> >unsure of whether it's correct:
> >
> >SELECT *, *
> >FROM T_Participants RIGHT JOIN T_General ON T_Participants.[Student_ID] = 
> >T_General[StudentID_FK]; 
> >
> >Does this look correct to anyone?
> 
> Not really. The SELECT * syntax means to select all fields (from all tables);
> so by using the asterisk twice you're saying SELECT each field twice. I expect
> Access will assign an alias fieldname to one of the duplicates. The RIGHT JOIN
> will cause all records in T_General to be included, whether or not they have a
> match in T_Participants; if they do have a match you'll get all the field
> values in T_Participants, otherwise those fields will be in the report's
> recordsource with NULL values.
> 
> Could you explain what fields are in the two tables, and what you intend this
> query to accomplish?
> -- 
> 
>              John W. Vinson [MVP]
> .
> 
0
Utf
6/6/2010 2:50:04 AM
On Sat, 5 Jun 2010 19:50:04 -0700, forest8 <forest8@discussions.microsoft.com>
wrote:

Fine; what's in the table, and what do you want to see in the report? 

Remember *you* can see your database. I cannot.

>I was planning on using the select in the rowsource of a report.
>
>
>
>"John W. Vinson" wrote:
>
>> On Fri, 4 Jun 2010 19:29:32 -0700, forest8 <forest8@discussions.microsoft.com>
>> wrote:
>> 
>> >Hi there
>> >
>> >I want to use the following statement in the RowSource for a report but am 
>> >unsure of whether it's correct:
>> >
>> >SELECT *, *
>> >FROM T_Participants RIGHT JOIN T_General ON T_Participants.[Student_ID] = 
>> >T_General[StudentID_FK]; 
>> >
>> >Does this look correct to anyone?
>> 
>> Not really. The SELECT * syntax means to select all fields (from all tables);
>> so by using the asterisk twice you're saying SELECT each field twice. I expect
>> Access will assign an alias fieldname to one of the duplicates. The RIGHT JOIN
>> will cause all records in T_General to be included, whether or not they have a
>> match in T_Participants; if they do have a match you'll get all the field
>> values in T_Participants, otherwise those fields will be in the report's
>> recordsource with NULL values.
>> 
>> Could you explain what fields are in the two tables, and what you intend this
>> query to accomplish?
>> -- 
>> 
>>              John W. Vinson [MVP]
>> .
>> 
-- 

             John W. Vinson [MVP]
0
John
6/6/2010 8:17:46 PM
Reply:

Similar Artilces:

Can the retail management software be used to help build an e-com.
I am about to open a retail store and also want to set up a web site. Is there the capability of integrating info from the retail files into the website? New West technology has a great package "down in new orleans" <down in new orleans@discussions.microsoft.com> wrote in message news:97A30580-A242-43DB-89FF-EF44FE70A7A1@microsoft.com... >I am about to open a retail store and also want to set up a web site. Is > there the capability of integrating info from the retail files into the > website? www.nitroasl.com "down in new orleans" <down in new...

Why is Publisher crashing when I select Print Preview?
I've used Print Preview before, but now the whole program closes when I select it. It doesn't make a difference whether I select it on the toolbar or from the dropdown menus. Any thoughts. Go to the manufacturer's web site of your graphics card and look around for an updated driver. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Leah S-W" <Leah S-W@discussions.microsoft.com> wrote in message news:4B027FA0-7425-4CF9-94BE-0CA15A05FF45@microsoft.com... > I've used Print Preview before, but now the ...

Lost Ability to Include Selected Text in Replies
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel I no longer seem to be able to select some text in a message, and begin a reply to that message quoting only the selected text. Either in HTML or plain text, the selected text does not appear. I have tried all Preferences>Compose, Reply permutations. Either all of the in-coming message is included, or none is included. Does anyone have a similar issue? On 3/2/10 11:04 AM, AndyBobyarchick@officeformac.com wrote: > I no longer seem to be able to select some text in a message, and > begin a reply to ...

Using the CASE function in SmartList Builder
I need to create a calculated field in SmartList Builder to print the Payroll Transaction History:UPR TRX Amount when the Payroll Transaction History:Payroll Code is not equal to certain values. Here is the sql query I would run to select those results: SELECT UPRTRXAM FROM UPR30300 WHERE PAYROLCD NOT IN ('STD', 'LTD', 'XMAS', 'BONUS', 'CAR', 'CLOTH', 'ADDPAY'). Normally I would use restrictions to restrict the whole SmartList object but I need those records to be available for another column. I haven't really worked with t...

Hanging/freezing when using connected mailboxes
I'm at a law firm and many of the secretaries have their attorneys mailboxes hooked into their outlook. Even with new PCs and a fresh install of office '03 their outlook sessions tend to freeze and crash very often when interacting with their attorney mailboxes. Sometimes they get right in with no issues. Cached mode only seems to make it slower. I was wondering if anyone knew any tricks to making this tolerable. We use office '03 with XP OS and '03 exchange server that's across the U.S. . Submitted using http://www.outlookforums.com ...

faq: use position of CList can find the element?
Hi, In CList , we can use variable of type POSITION to GetAt(),SetAt(),etc. Now if we use it like this: funtion1(POSITION currentpos, POSITION& previouspos) { };//in function1 return the value of previouspos funtion1 returns the value of previouspos, can I use it to GetAt(previouspos) the element in another function2: function2() { .... mypoint=mysegment.mypointlist.GetAt(previouspos); .... }; .... does it worked? I mean , wether the position of CList is soly and fixed when the CList created? Thanks. Thank you very much! :) Thank this newsgroup very much! :) Visual Studio ...

Using Transactions
Hi, I have tried using a transaction to make sure that changes to a number of tables are completed successfully, or all rolled back. However, I cannot get the transaction to rollback, and the changes are applied regardless. My test code below deletes all the records from a series of tables. Even when I select No at the end, all my tables end up empty! Any help is very much appreciated! Cheers, Jim 'set workspace for transaction Set wrkDefault = DBEngine.Workspaces(0) MyArray(0) = "tbl_Users" MyArray(1) = "tbl_Applicants" MyArray(2) = "t...

Secure Messaging (signed and encrypted) with external/interdivisional Recipients using Outlook 2003/Exchange 2003
I currently have to evaluate a solution for a signed and encrypted Exchange 2003 (SP2)Outlook 2003 (SP2) messaging environment, externally to the internet and "network internally" with other divisions (which are not trusted into our Win2003 Forest/Exchange 2003 Org.). MS PKI is in place and is used within our Forest/Exchange Org for secure messaging. My questions here are: Are there any possibilities for external, secure messaging (digitally signed and encrypted) using the existing MS PKI Infrastructure? Would a MS PKI cross certification infrastructure work with external internet ...

Running Sum used in calculation in Report
I have 12 columns, two are running sums (work great), I need those two running sum calculations to multiply together and that result in another column. I have tried a number of solutions and I'm still stuck. I hope I'm giving enough information. In the below example 2, 81, 4 and 84 are results of running sums of other columns. 2 81 here I need the result of 2*81 4 84 here I need the result of 4*84 I would expect that if the totals were needed in the same report section, all you would need to do is reference the names of the text boxes in an expression like: ...

changing cell value depending on list selection
I have a long coloumn of data which is entered by selecting from a dro down list (data/validation/list)i want a numerical value to b associated with each value from the drop down list. I would also lik this value to be dependent on another value in another cell. ie: A B C one build 1 two three build 2 Coloumns A and B are drop down lists and C is a numerical value tha appears only when Build is in coloumn B and the value depends on wha is entered in coloumn A. Hope this isnt too confusing! Thanks Rober -- Message posted...

Re-direct without Exchange using Outlook XP?
Is there a way to have Outlook XP redirect email without owning Exchange? I want to buy a Blackberry, and I want to keep my existing email account. thanks in advancee. Please respond here to the group. My email address is not listed so as to escape at least some spam. :) What do you mean "re-direct?" You can use Rules Wizard to forward mail if that is what you mean. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Having searched the archives and finding no answer, News <blarg@blarg.com> asked: | Is there a way to have Outlo...

For Each cell In Selection.....
This is a multi-part message in MIME format. ------=_NextPart_000_002B_01CA80FF.54EDB200 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable The selection contains 1 column The number of rows is variable If i want to execute the "For Each cell In Selection" command, but i = want the execute this for 1 row less than the actual selection...(the = last row is not to be executed) How do i do this ? Thanx, Luc ------=_NextPart_000_002B_01CA80FF.54EDB200 Content-Type: text/html; charset="iso-8859-1" C...

Using zeros as placeholders
Is there a formula to add zeros (or any character) to a cell with a fixed width? I am currently using a formula, =A1&REPT(" ",20-LEN(A1)), which will extend the character length of a cell to 20, this works for items that are left justified and entries that contain fewer than 20 characters just have spaces to fill up the remaining charaters. What I am looking for is a way to add characters to the front of an entry and still enforce the fixed length. Example: A cell contains 123456789 I would like the cell to have a fixed length of 20 chracters, and the "missing" c...

"select user_name(), current_user";
Hello! I just wonder when I run my web site and connect to the database by using the connectionstring I have in my program added this select statement "select user_name(), current_user"; and the result is dbo. So I just wonder what is that control what is written when using this statement "select user_name(), current_user"; //Tony Tony Johansson (johansson.andersson@telia.com) writes: > I just wonder when I run my web site and connect to the database by using > the connectionstring I have in my program > added this select statement &quo...

Outlook 2003 SP2
Hi all, I would like to know if, in order to use a signature that includes a small picture (company logo), it is necessary to use word as email editor. I would really like to avoid using word ! I tried today and it didn't seem to work (the picture was not displayed, just the boring red x showed up). Thanks in advance. barabba72@hotmail.com <barabba72@hotmail.com> wrote: > I would like to know if, in order to use a signature that includes a > small picture (company logo), it is necessary to use word as email > editor. I would really like to avoid using word ! See this:...

select products to order->in other sheet total num of that product reduced
Hi, I'm excel beginner,and I'm facing a problem I cannot solve. I want to enter some values(IDs,numbers) in rows of one sheet,and (immediately or on button click) the corresponding row(by ID) in the other sheet to be modified.How can i do that? _More_detailed_explanation_ I attached a very small .xls example of what I'm trying to do: - I have two worksheets,one is a simple product list,other a simple order list - Problem: in the order sheet i select *product ID* (from a list),and number of products i order; in products sheet total number of that product should be reduced for th...

Help with expression builder in a select Query
I am a beginer of Access. I have a database that consists of 4 columns/fields of dates. These dates/fields can be filled or can be blank. I call them A, B, C, D. Now I want to make a query or report to retrieve the date data in the column/field of A. The criteria for this: all the dates in field A must be later /larger than those in field B or C or D if B or C or D contains any value. I queried the A,B,C,D from the table that contains these fields. The first criteria I wrote is "A is not Null". But then I want to say if B or C or D is not blank, A must be ...

Multiple messages for single selection change in list control
Hi, In the handler of LVN_ITEMCHANGING (OnItemChanging...()), how can I deduce which messages belong to the same event? I am interested in the change of an item selection state, but it seems that an irregular number of messages are sent for one such event. In some cases I would like to substitute *pResult = TRUE in order to cancel the change, but I still receive the rest of the messages for that same event. For those messages I should also set *pResult = TRUE, but how can I know which messages belong to the same event? I cannot figure out the logic of the number of messages sent for a single ...

How to Select large amount of data in a colum that equals column from another table.
Ive got a huge table and I want to select the data in one column that is equal to that of a column in another table? is there an easy way to do this? Thanks One way, if you know the parameters of the original, is to type the references in the name box and hit <Enter>. For example, click in the name box and enter: E1:E65000 Then <Enter>. -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Jennifer Burnel" <jenburnel@babooshka.net> wro...

CF Stop If True not working correctly
Hi, I have a problem with Stop If True for Conditional Formatting not working correctly in Excel 2007. It seems as though Stop If True is always on even when the check box is not checked. I have a very simple demonstration, at least on my machine. Open a new workbook. Enter the values 1, 2, 3 in cells A1, A2, and A3 respectively. Select those three cells. Select New Rule from the Conditional Formatting menu Create a rule of "Format only cells that contain" Make the rule as follows: Cell Value Is greater than 1 Fill is (select ...

Using MDIChildFrame for different views of document
I have two separate questions: Question 1: ----------- How do I use my MDIChildFrame derived class in an MDI application to show different views of the same document? This is what I want: 1. When the document is initially opened, it will be opened in a MDIChildFrame-derived window with the "default" view. 2. In response to certain commands/menus, another child window should open and show another view of the same document. There may be more than one view. How do I do this? Question 2: ----------- Due to the application having many views and the corresponding GUI classes, the appli...

Using XmlSchemaSet with DataContractSerializer
Hi! Is the DataContractSerializer supposed to work with an XmlSchemaSet object? I am having problems getting them to work together. Here is some code that illustrates the problem. First, we define a type Blark that is used to generate an XmlSchemaSet using the XsdDataContractExporter. The type HasSchema is a type that contains an XmlSchemaSet as DataMember. We create an instance of HasSchema with the above generated XmlSchemaSet. Then we serialize and deserialize this instance using the DataContractSerializer. Before serialization, the XmlSchemaSet has Count = 3, but after ser...

Stop from Selecting a Protected Cell
Hi all, I am using XL2002. I have protected a cell by locking it - Format Cells>Protection tab>Locked. Then, I protect the worksheet - Tools >Protection>Protect Worksheet>without ticking on either Select Locked Cell or Select Unlocked Cell>type the password>OK. Then, I save the workbook. I protect the cell so that users cannot change the formula in it or even select the cell. I save the workbook. BUT when I open that workbook in other computers (with Excel 97/2000/2002), I can still able to select that cell eventhough I cannot write in it. Why does this happen? How to ...

Selecting ICONS for Macros in Excel 2007
Once you have customized the QUICK ACCESS TOOLBAR by adding icons to run various macros, is there a way that you can replace the icb=ons with your own icons? If yes! How? Thanks Hi, Have a read of Ron de Bruin's page on the subject. http://www.rondebruin.nl/imageqat.htm Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "pcor" <pcor@discussions.microsoft.com> wrote in message news:4487FAB6-A626-4F94-A1B6-2BDE646CC214@microsoft.com... > Once you have customized the QUICK ACCESS TOOLBAR by adding icons to run > various macros, is there a ...

Random Selection of Records
I have a table named Table1 that has 200,000 records. Is there a way to randomly select 40,000 records from this table ? Thank you in advance. carl wrote: >I have a table named Table1 that has 200,000 records. Is there a way to >randomly select 40,000 records from this table ? In your code, use the Randomize statement before using the query: SELECT TOP 40000 * FROM Table1 ORDER BY Rnd(1) -- Marsh MVP [MS Access] On Thu, 11 Mar 2010 06:02:10 -0800, carl <carl@discussions.microsoft.com> wrote: >I have a table named Table1 that has 200,000 records...