Updateable Query

Why can't access just read my mind.  It would be easier.

I have 2 table 
     tblStudents-Stores Name, ID#s, Ethnicity, Etc
     tblTests - Stores which particular tests the students should take Math, 
Reading, Science Etc.

Currently tblTests is empty.  I would like to set up a query that links the 
tblStudents with tblTests via the LocID number with all records from 
tblStudent showing.  LocIDis the primary key for both tables.  When I tried 
to link them it makes the query uneditable.  I looked on the site and tried 
to include all the fields from tblTests and only last and first name from 
tblStudents, to no avail.

Anyone willing to attempt it?

SELECT tblStudents.Last, tblStudents.First, tblWatch.ID, tblWatch.LocID, 
tblWatch.StudentWatch, tblWatch.MathWatch, tblWatch.ReadWatch, 
tblWatch.WriteWatch, tblWatch.SciWatch, tblWatch.SSWatch, 
tblWatch.NewStuWatch, tblWatch.Watch2, tblWatch.Watch3
FROM tblWatch RIGHT JOIN tblStudents ON tblWatch.LocID = tblStudents.LocID;

-- 
Thanks As Always
Rip
0
Utf
9/10/2007 6:48:12 PM
access.queries 6343 articles. 1 followers. Follow

1 Replies
981 Views

Similar Articles

[PageSpeed] 26

On Mon, 10 Sep 2007 11:48:12 -0700, Ripper <Ripper@discussions.microsoft.com>
wrote:

>Why can't access just read my mind.  It would be easier.
>
>I have 2 table 
>     tblStudents-Stores Name, ID#s, Ethnicity, Etc
>     tblTests - Stores which particular tests the students should take Math, 
>Reading, Science Etc.

You need THREE tables, not two, if each student should take multiple tests. 

tblStudents
  StudentID <Primary Key>
  LastName
  FirstName
  <other biographical data>

tblTests
  TestID <primary Key>
  TestName <e.g. Math, Reading, ...>
   <other info about the test itself>

tblRequirements
  StudentID <link to tblStudents>
  TestID <link to tblTests>
  <other info about THIS student and THIS test, e.g. date needed, date taken,
score on the test, etc.>

You could use a Form based on tblStudents with a subform based on
tblRequirements to enter multiple tests per student.

             John W. Vinson [MVP]
0
John
9/11/2007 12:47:03 AM
Reply:

Similar Artilces:

slow loading, slow updating
I am using MS Money in a non-standard application. I am maintaining ledgersin checkbook fashion for 100 accounts, each set up as a separate checking account. Each of those has 3 transactions set up as automatic bill pays. Other than that, there are very few entries, averaging 6 per year at most. The program takes about 5 minutes to load. Corrections, such as posting bill payments, takes approx 10 seconds to cycle through: Open, edit, enter, wait........ Open, edit, enter, wait.....etc. I am not doing any online updating or anything like that. Is there anything I can do to speed this ...

Network connection for query
G'day there One and All, I'm currently trying to adapt a few of the Dept's workbooks to suit our district. There is a single master book, to produce reports, that is available to all on a read only basis and read write to the relevent manager. There are also a number of data entry books that are identical to each other and that provide the data from each division. The master book uses a query to obtain data from the division's books and thereby build the reports. The data books are read write for everyone with only a single sheet from each providing data for the que...

Add to a query Expression!
Overdue: IIf((DateDiff("d",[MyDate],Date()) Between 30 And 60) And ([Payable]>1),"1 Month",Null) How would I add theses 3 expressions into the expression above.............Thanks for any Help.........Bob Between 60 And 90) And ([Payable]>1),"2 Months",Null Between 90 And 120) And ([Payable]>1),"3 Months",Null Between 120 And 2000) And ([Payable]>1),"3 Months Plus",Null Hi Bob I find the easiest way to do this is to build the expression one component at a time. I use Editpad lite which is like notepad. You can use a tes...

Saving count or DCount result to query
Dear All, As we know,we can perform DCount function in a continuos form. But,can we save it result to a query.? Clearer like this : When I open my form ( filter on ) and there are 5 records in my form ,and my count function,return 5 as the result.Then access mark these 5 records and then in the query saving the count result.But it seem we have to know ,to which group these records bound to,isn't it?Excatly maybe we count grouping in a query. Cause if I perform count field in a query it just return me value 1 for each record. And that is not excatly what i want.Please help Thank's ...

Web Query Formatting not ready for Production
I do web queries from several internal systems and I continually run into problems where the returned data is formatted to Microsoft's wishes instead of my formatting requirements. 2 Sample are: 1) one column is cage code, a 5 digit alphanumeric number (sometimes starting with a zero). System want to remove the first zero even if I set the column to text. 2) Part Numbers column comes in all shapes and formats as a string, some starting with multiple zeros, some having all numbers with "E" in the middle, etc. When importing, even if column set to text, web query...

Unable to update bank sign-in information
I am using Money Plus Deluxe v. 17.0.120.1415. After many months of successfully downloading transactions from Juniper Bank, the bank required me to change my password, which I did on the bank's Web site. This resulted in Money no longer being able to download transactions, giving the error message, "Your sign-in information doesn't match the bank's records. Verify your sign-in name and password with your bank or brokerage, and then provide your sign-in information again." The problem is that when I follow the link to do this, and enter and confirm the new pass...

Cube returns dimension from whole table, not from query?
Hello, in excel, I get data from sql server 2k. I create a query like this: select ... from tblCustomers, tblSelection where tblCustomers.customerid = tblSelection.id The table tblCustomers has thousands of customer id's; but the table "tblSelection" has only a few - those that need to be included in this particular Excel report. When I run the query in MS Query, it works correctly, i.e. it returns only the selected customers. However, then I create OLAP cube in MS Query, save it as file, and return the cube to Excel. In the resulting pivot table, Customers include not ...

Unlinked Tables affecting Query?
I have a simple SELECT query which is based on two tables with an INNER JOIN on the VehicleID field. I noticed however, that if I added another table to the QBE grid - even though I didn't link the table to my query's 2 source tables in any way, and even though the query's SQL remained unchanged - the query output changed?? It returned the same records, but repeated hundreds of times? Can anyone explain why just the presence of an unlinked table in the QBE grid would affect a query? I just can't explain it. Any help greatly appreciated.......Regards, Jason Jay If yo...

Unable to update/add records in connected SQL Server tables
I am using the following code to connect to an SQL server database. The connection 'works' in that the tables are fully visible. However, they are not update-able which defeats the object. datasource = "ODBC;Driver={SQL Server};Server=" & SelectServer & ";Database=XXXXX;UID=xxxxxx;PWD=xxxxxx" Set tbl1 = db.CreateTableDef(tTable, dbAttachSavePWD, srcTable, datasource) db.TableDefs.Append tbl1 ('SelectServer' is a variable to allow switching between the live and test databases) The username with its password has full...

Use Excel Spreadsheet to Update Access
Is there a way that I can have the information that is entered into an Excel Spreadsheet automatically update the fields in the Access Database? I have an access database where I created a query to pull down information into a spreadsheet for people to update. Then I have to put that data back in the database. The query is taken from several tables. Please HELP! Tina You should be able to do it by hand. If the records are new update the spreadsheet then copy the data. Swith the Access and open the query and choose Edit, Paste Append. The data will be added to the relevent tables ...

Querying for duplicate records...
Can anyone tell me if there is a way of running a query specifically for duplicate records!? I have Field1 which is a numerical field (no duplicates), and Field2 which is a General field (duplicates allowed). I was wondering if there's a way to run a query to only display records where Field2 exists (in whole, or part) in multiple records!? Thanx! Try this as it seems to be what you are asking for --- SELECT Your_Table.x, Your_Table_1.x FROM Your_Table, Your_Table AS Your_Table_1 WHERE (((Your_Table_1.x) Is Not Null) AND ((Your_Table.ID)<>[Your_Table_1].[ID])) GROUP BY Your_Table...

Last row query
I have several formulas that work correctly by referencing the data in a column as $A$100 as 100 is the last row with data in Column A. Is there a way that will work in any formulas of the above type, where I can replace $A$100 with some other expression that will access all the rows in Column A without me having to check for the last row number and changing the formula? Thanks Brian Tozer take a look at dynamic ranges: http://cpearson.com/excel/named.htm#Dynamic In article <cho6j9$5ij$1@lust.ihug.co.nz>, "KiwiBrian" <briantoz@ihug.co.nz> wrote: > I ...

Removing Access External Data Queries from Excel
Hello Folks, I have a Excel Spreadsheet with Microsoft Access external data query links. I would like to create a snapshot in time of the data in the Excel Spreadsheet. I do this now by manually going to each external data query cell (there are 8 of them), right click the cell, select Data Range Properties, then uncheck the Save Query Definition, it asks me to confirm and press OK. Now the external link is disconnected saving the last data query value in the cell. The interesting thing here is when I try to record via the excel macro recorder nothing shows up in the macro recorder v...

can you update a charts data range automatically in Excel?
In a spreadsheet with multiple charts, is it possible to update the data range and supporting calculations for the charts when you paste new data into the spreadsheet. Calvin If your question is, can you change the data to update the charts, Yes. If not please clarify the question Thanks Vyenktesh "Cal@tech-etch" wrote: > In a spreadsheet with multiple charts, is it possible to update the data > range and supporting calculations for the charts when you paste new data into > the spreadsheet. > > Calvin Calvin - You can set up the charts to use dynamic range...

[ANN] Office 2008 12.0.1 update
Hi All, The Office 20089 12.0.1 update is now available on Mactopia: http://www.microsoft.com/mac/downloads.mspx It should soon also be available through Auto-update. 114.1MB!!! The KB describing the update is not yet live on the MS site so at this time I do not yet have the release note, but it will be available here: http://go.microsoft.com/fwlink/?LinkId=108496 The updater is a .mpkg package (so it is installable through over a network through ARD :-) ). Corentin -- --- Mac:MS MVP http://www.cortig.net/wordpress/ --- http://www.mvps.org - http://...

Microsoft AutoUpdate in Trash after Updating Office 2008
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: intel I've installed and updated my Office today. I removed my previous office (2004) by using the remove tool and then I trashed the Microsoft AutoUpdate and the XML Converter. Then I inserted the CD for the new version and installed. And then I ran Microsoft AutoUpdate and got both a new updater and an update for office. Then I noticed my trash had something. It was the Microsoft AutoUpdate. I dragged it towards the Applications because there was no MAU there. Then I ran it and it keeps downloading a new version and tr...

Update custom made picklist with the help of sdk
Hi, I have made my own picklists and when I create eg. an incident with the help of SDK I would like to update my picklists as well with the corresponding value. I have made three picklists for an incident(case), but I can't figure out how to update it from the SDK. Can anyone help me on this one? This is how I do it in my xml query: (int)Microsoft.CRM.Flags.ObjectType.otIncident In contact I have just made one picklist and the update seems to work alright. That's why I suspect one can only have one custom picklist. Can any one confirm? Thanks, As far as I know, there are no...

G5 has no OS9
Just bought a G5 to replace a dead powerbook & I am trying to install Office OSX. However, I originally bought Office 2001 (OS9 version) & then bought an upgrade to OSX - anybody any ideas how to get out of this ? I can't install the OS9 version because the G5's have no OS9 & I can't just use my upgrade disk.... It may be faster than fast, but I can't install the software at the moment !! Regards Paul Hopkins On 10/16/03 12:26 PM, in article BBB4AF7D.1007E%info@the-image-works.com, "Paul & Aoibheann Hopkins" <info@the-image-works.com> wrote:...

Referential Integrity in Queries
I have 3 tables all linked with referential integrity. After upgrading to Access 2007, the relationships are still enforced, but when I view a query, the join does not show the referential integrity. This is all of the queries by the way. If I delete the table and re-add it in the design view, it does show the proper join, but is there a better way. A query join is quite different from a relation between the tables. When you create a relation between tables, and then add the tables to the query, Access usually does make the join for you. In fact when you create the relation, you can...

Date query
This is a multi-part message in MIME format. ------=_NextPart_000_06C4_01CB0295.E90C4830 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi, I am using a census database to enter data from the 1901 UK census. To = calculate the year born the input age is subtracted from 1901. However, = when a child under one year old is enter, obviously an error shows. =20 What do I need to do to show 1901 when a child's age under one year is = entered. The age field can hold three numbers and one letter (h =3D = hours, d=3Ddays, ...

Update from Commerce Bank NJ/PA/NY/DE
Money users, I contacted my bank regarding my inability to perform updates since some of their security measures have changed. We tested online and he saw my incoming connection and determined that Money did infact recieve the OFX transfer, however the update fails. Is anyone else out there having problems with MS Money 2007 and Commerce Bank NJ/PA/NY/DE? I had this successfully working for years now, but now it doesn't update at all. My only method of udpating my account information in Money is to login to their web application and then download the OFX file and then manually i...

Chart Refresh/Update Issues
MS Excel 2000 SR-1 I am using an advanced filter on my chart's source data but the chart refuses to update automatically when I change the filter criteria. Calculation is set to automatic and the 'Plot Visible Cells" option is checked. I have tried activechart.refresh and the 'doevents' trick as well without success. Currently I have found a 'workaround' by setting the 'Plot Visible Cells' option on and off using the following code: Application.ScreenUpdating = False ActiveChart.PlotVisibleOnly = False ActiveChart.PlotVisibleOnly = True ...

Access 2007
Hello Access 2007 Google Groupmeisters, need your help and support on this one.... My Table: Field1 Field2 Field3 Field4 MPIID MPIID 07499750 MEDREC 06 000014180210 MEDREC OHMC 679558 MEDREC OHSC 0385633 MEDREC OSO OSO-679558 MPIID MPIID 08931110 MEDREC OSO OSO-1203758 The Select Stmt: SELECT Table1.Field2, Min(Table1.Field3) AS MinOfField3 FROM Table1 WHERE Table1.Field2 <> 'MPIID' GROUP BY Table1.Field2; The result of the stmt above: Field2 MinOfField3 06 000014180210 OHMC 679558 OHSC 0385633 OSO OSO-1203758 The issue: If you look at the l...

FW: Check this correction update which came from Microsoft Corporation
--cxkixanb Content-Type: multipart/related; boundary="dhmygeioknjp"; type="multipart/alternative" --dhmygeioknjp Content-Type: multipart/alternative; boundary="sgetbqnn" --sgetbqnn Content-Type: text/plain Content-Transfer-Encoding: quoted-printable Microsoft Partner this is the latest version of security update, the "November 2003, Cumulative Patch" update which resolves all known security vulnerabilities affecting MS Internet Explorer, MS Outlook and MS Outlook Express as well as three newly discovered vulnerabilities. Install now to continue keep...

Rollup Update 2
I see that there is an update for the client, server and for the exchange router. What is the correct order of installation, and can I do just one of the updates or do they all have to be done at the same time. For example, if I update the CRM server, do I also have to update the clients in order for them to work, or will the clients still work with the updated server even though they have not yet received the update? Thanks Jeff I believe it would still work even some of the updates are not done. It's just simply resolves a lot of issues on those 3 part (user/client, Server, Ex...