#### Problem creating data entry form

I've solved what I thought were tougher problems that this, but the
seeming simplicity of the concept here has me stumped.  Here's the
problem:

I've created a little database for my spouse, a school teacher.  In
this database are simple tools for keeping track of student info and
their parents or guardians.  There's a table for keeping track of the
various tests & assignments and their respective values and
weightings.  I now need to create a form by which the test scores can
be entered.

Firstly, each student is identified by a unique StudentID autonumber
in the StudentInfo table.  The various assignments are similarly
assigned a ScoreID autonumber in the ScoreItems table.  I have another
StudentScores table with only the fields ScoreID, StudentID, and Score
in which I had planned to simply match the various student scores to
the various tests by their respective ID numbers.

Now, to make thinks simple for the spouse, I'd like to create a form
(and underlying operations) by which clicking on a specific test will
open a (continuous) form listing all of the students assigned to that
respective class (by a ClassID field), along with a blank field for
entering the grades.  It sounded simple until I tried to do it.
Here's what I ran into (and, finally, the real question):

In order to create a data source that includes both the students'
whole names and the ScoreID's, I need to use inner joins in the query;
and I end up with a recordset that is not updatable, so I can't use
direct entry on the form.

Okay I think, when a test is selected, I'll generate a new table (Make
Table Query) containing the names of all students in the applicable
class, then use that table as the Data Source for the form.  That hit
a snag because the Make Table Query will not allow me to create a
table that contains two AutoNumber fields (StudentID and ScoreID), but
both of these fields are necessary.

I thought I was being smart by setting up the data tables the way I
did, but now I'm re-thinking that.  I'm only an advanced novice at
this stuff, so entirely open to the thought I'm overlooking something
simple.  Any good ideas on how to facilitate this?


 0
rb608
7/19/2007 7:39:22 PM
access 16762 articles. 3 followers.

1 Replies
569 Views

Similar Articles

[PageSpeed] 17

The basic idea will be to execute an Append query statement that adds the
students and the ScoreID to the StudentScores table.

If every student must complete every assessment, that would be something
like this:

strSql = "INSERT INTO StudentScores ( StudentID, ScoreID ) " & _
"SELECT StudentInfo.StudentID, " & 99 & " AS ScoreID " & _
"FROM StudentInfo;"
dbEngine(0)(0).Execute strSql, dbFailOnError
Me.Requery

In practice, it probably needs a WHERE clause to ensure:
a) only the desired students are added, and
b) a student is not added more than once.

To achieve (a), the database will need to know which students need to
complete which assignments.

To achieve (b), you probably want to add a subquery to the WHERE clause.

--
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.

"rb608" <junkmail608@verizon.net> wrote in message
>
> I've solved what I thought were tougher problems that this, but the
> seeming simplicity of the concept here has me stumped.  Here's the
> problem:
>
> I've created a little database for my spouse, a school teacher.  In
> this database are simple tools for keeping track of student info and
> their parents or guardians.  There's a table for keeping track of the
> various tests & assignments and their respective values and
> weightings.  I now need to create a form by which the test scores can
> be entered.
>
> Firstly, each student is identified by a unique StudentID autonumber
> in the StudentInfo table.  The various assignments are similarly
> assigned a ScoreID autonumber in the ScoreItems table.  I have another
> StudentScores table with only the fields ScoreID, StudentID, and Score
> in which I had planned to simply match the various student scores to
> the various tests by their respective ID numbers.
>
> Now, to make thinks simple for the spouse, I'd like to create a form
> (and underlying operations) by which clicking on a specific test will
> open a (continuous) form listing all of the students assigned to that
> respective class (by a ClassID field), along with a blank field for
> entering the grades.  It sounded simple until I tried to do it.
> Here's what I ran into (and, finally, the real question):
>
> In order to create a data source that includes both the students'
> whole names and the ScoreID's, I need to use inner joins in the query;
> and I end up with a recordset that is not updatable, so I can't use
> direct entry on the form.
>
> Okay I think, when a test is selected, I'll generate a new table (Make
> Table Query) containing the names of all students in the applicable
> class, then use that table as the Data Source for the form.  That hit
> a snag because the Make Table Query will not allow me to create a
> table that contains two AutoNumber fields (StudentID and ScoreID), but
> both of these fields are necessary.
>
> I thought I was being smart by setting up the data tables the way I
> did, but now I'm re-thinking that.  I'm only an advanced novice at
> this stuff, so entirely open to the thought I'm overlooking something
> simple.  Any good ideas on how to facilitate this?


 0
Allen
7/20/2007 2:18:46 AM

Similar Artilces:

UI Data Validation using XML Schema
Has anybody done UI data validation usinf XML Schemas. Any pointers will help. Thanks! ...

forms/templates

Change paths to linked external data
Hello. I have a database that has 3 external links in it: 1 is to a table in another Access Database. One is to an excel spread sheet. And, the third is to a .csv file. Is there a way in VBA to change the paths to these? Basically, i want to set up a form where the user can specify the new paths in text boxes if any of these files are moved. Anybody help me out? (Access 2007) cyb3rwolf wrote: >Hello. I have a database that has 3 external links in it: 1 is to a table >in another Access Database. One is to an excel spread sheet. And, the third >is to a .csv...

Combo box filter before form opens
frmSearchType with cboType and cmdOpenForm which opens frmEntryEdit. frmEntryEdit uses cboEntryNum to allow user to select from the filtered records. This is working correctly, however if no records fit the filter, I get an open frmEntryEdit, with no records in the cboEntryNum. I would rather have a message box that states "No Data fits the criteria", than have frmEntry Edit open. Is this possible? -- Teach me to fish! Thanks for the help. Pax, M m stroup wrote: >frmSearchType with cboType and cmdOpenForm which opens frmEntryEdit. >frmEntryEdit use...

Something destroying Money data
My MS Money 2007 software was working fine, and then a few months ago, there were transactions missing. I went for backups, and it seemed every time I opened a MOney file there were fewer transactions--it was truly weird. Thinking a file had been accidentally deleted, I used a file recovery program and retrieved file and backup - also no data. So I just put the whole thing aside and out of mind, but tried again today and searched all partitions for mny and mbf files.. Same result, if anything fewer transactions. Is there some virus or other malware that eats Money files? I guess I can d...

XML into DataSet
Hi. I need some help. I have an xml file what will contain hundreds of groups and will have an associated xsd. My overall goal is to have a web application that allows a user to input a groupID from a webform, the application will than find that groupID in the xml file and display all the related information for that group only. I have read my xml into a DataSet and have been able to successful find groupID and display some of the information for that particular group. But I have been unable to navigate to the groupsItems node and subsequently the details of each groupItem found in gro...

Assume I have a column of numbers, in a file named Source. (range is B2:B10). These cells are linked to a column in a file named Destination(range is E2:E10). If I add a new row 5 to the source, is there a way I can get the previously linked cells in Destination to continue to display the correct values. After adding the new row 5 in source, rows in Destination below row 5 display values from rows below (in the Source file). I hope this is not too confusing a question (I am confused). If it of importance, the linking was done using Paste Link in the Paste Special dialog. Thanks. Hi PA Yo...

Form printing problem
Hi all, I have a form that holds customer records which we have been printing out for filing. I put a print record command button at the top of the form and this has been working perfectly for at least the last four years printing on plain paper using auto selection on the printer. Recently the form record has started printing onto header paper. It doesn't matter whether I change the properties on the printer it keeps printing to headed paper and the only way round was to put plain paper into the headed paper tray. We have now a new printer in the office which has been set as my defa...

I chose the form "Newsletter - email". I have created a 3 page newsletter. Now, I have NO IDEA how to send it out as an email. I do not want to send it as an attachment. I cannot figure out which "save as" format or what I need to do so that i can email this newsletter. Is there a website that goes through how to do this? Am I correct when I say that it has to be html in order for me to send it as email? That is not one of the options. Any help you give me would be greatly appreciated. I am using 2003 Publisher. I created the book "Tri-Puzzles for Genesis - I"...

HowTo add row to series data
1) insert row with ShiftDown 2) apply new data to the newly emptied cells 3) preserve original chart series range 1) after this code row 2 is empty cells Excel.Worksheet chart = (Excel.Worksheet)book.Worksheets["Chart Data"]; Excel.Range rngToday = chart.get_Range("A2", "D2"); rngToday.Insert(Excel.XlInsertShiftDirection.xlShiftDown); 2) this puts todays date into A2 rngToday.NumberFormat = "MM/dd"; rngToday.Value = DateTime.Today; 3) OOPS - the chart data is now A3..A7 because of the insert above How do I reset the souce data to A...

I am running Exchange 2003 Standard on a Windows 2003 Server. I also have ISA 2000 on the same box. Everything is working fine. The problem I am having is getting mail out to a hotmail address. When I send then mail, I see it sits in the Queue for less then a minute then disappears. It never makes it to the Hotmail address nor do I get any return errors back in my client indicating the mail never made it to its destination. On Sun, 13 Nov 2005 15:51:04 -0800, "Kevin Mc" <Kevin Mc@discussions.microsoft.com> wrote: >I am running Exchange 2003 Standard on a Windows 200...

How can I create an invoicing database in Excel?
I would like to know how to create an invoicing database, or register in Excel. (Invoice + tax = total) Are there any templates free online? thanks How about a web search? http://office.microsoft.com/en-us/templates/default.aspx search for invoice -- Regards, Peo Sjoblom (No private emails please, for everyone's benefit keep the discussion in the newsgroup/forum) "Vcollins" <Vcollins@discussions.microsoft.com> wrote in message news:C46E9FE2-0B38-4468-9B00-126B7EE5C9A2@microsoft.com... >I would like to know how to create an invoicing database, or register in &...

Folks The background is that an Access MDE/ACCDE created by a developer is not conceptually the same as an Excel spreadsheet or Word document file created by the user. My Auto FE Updater utility automatically downloads any new version created by the developer from the server to the workstation as well as doing lots of other stuff such as creating shortcuts on desk tops. If this was a user created Access database file which resides in the users My Documents folder, or similar, than they are conceptually the same as a Word or Excel file. The problem is if the user right-clicks on...

XSD
Hi all, I defined a XML Schema... so I've a .xsd file. I defined the schema using "default" attribute... for example: <xs:element name="color" type="xs:string" default="red"/> Now, I would like to generate an empty XML file, from the XML Schema. How can I do that? Thanks a lot. Bye -- Stefano Giannone MCP .NET http://www.giasoft.it Stefano wrote: > I defined a XML Schema... so I've a .xsd file. > > I defined the schema using "default" attribute... for example: > <xs:element name="color" typ...

try again email forms
Hi there I have searched for the codes to email a form/report in a body of an email and have Access connect to MS outlook to choose who to email to. I have tried several different codes and do not understand code. None of the ones that I have tried worked. The last one I have tried is: Dim mess_body As String Dim appOutLook As Outlook.Application Dim MailOutLook As Outlook.MailItem Set appOutLook = CreateObject("Outlook.Application") Set MailOutLook = appOutLook.CreateItem(olMailItem) 'MsgBox (mess_body) mess_body = "The following Enrollment Form Order requires your...

When I try to open up an excel document via my documents it says that the same document is open already how can this be stopped Tina First try the standard fix(es)......Tools>Options>General uncheck "ignore other applications" OR Start>Run "excel.exe /regserver"(no quotes and note the space before the / mark). You may have to enter your full path to excel.exe....in that case surround with quotes as in..... "C:\mypath\to Excel\somewhere\excel.exe" /regserver Gord Dibben XL2002 On Sat, 15 Nov 2003 09:16:14 -0800, "Tina" <TinaSCovell...

Outlook/Exchange 2000 connection problem
Hi, I appologize if this is not the correct newsgroup. Situation is the following. From some workstations (Win 24 Pro SP4, Outlook 2000) in the network, when I create Exchange service and click "Check Name" button i get message "The name could not be resolved. The operation Failed." This happens both when I enter Exchange server name, and IP. If I try to resolve name that does not exist, the message is "The name could not be resolved. The name could not be matched to a name in address list." Ping and nslookup work fine, OWA also. I even tried to reinstall Windows...

:huh: hi looking for billionton bluetooth usb adptar 100mt class1 driver for vista 32b. please some body help me for that ..............[image: http://forum.gsmhosting.com/vbb/../ubb/eek.gif] thanks -- nelbulani ...

Macro with relative address problem XSL 2000
Basically I want to (and did at one time) create a macro that edits a number (zip code), inserts a leading zero then moves down one cell. Cell Contents A1 1234 A2 2345 A3 3456 With the cursor on A1 my steps are: Tools\Macro\Record a New Macro\z On Stop Recording Toolbar depress Relative Address, then Edit Home '0 enter Down Click Stop recoding Since the cursor is on a A2, Pressing CTRL+z will make the contents 01234, not the desired 02345. I've tried different combinations of when to depress the relative address button with no success. And double clicking the c...

Graph/Data
Version: 2008 Hello, <br><br>I am trying to update an existing graph with 2010 data. <br><br>The pre-existing data is automatically connected to update the graph in another tab in the workbook. <br><br>How can I make columns in the existing graph for my new 2010 data? <br><br>Any ideas? <br><br>Thanks, <br><br>Hayley In article <59bb3c20.-1@webcrossing.JaKIaxP2ac0>, HayleyB@officeformac.com wrote: > Version: 2008 > > Hello, <br><br>I am trying to update an existing graph with 2010 da...

Paycheck problem
Hi, there. I'm new to this group. My name is Daniel, I'm from Brazil and I use MS Money since 1999. But I've always had some problems with it. Now I'm using Money 2007 DeLuxe, and, besides the "% of income" display problem (that seems to be a bur, really), I'm having some issues about paychecks. Here we go: Let's assume my total income is 1000.00. My employee deposits me 400.00 every 10th day, and the other 600.00 every 25th day. My paycheck says the following: "Wage: 1000.00 Deduction: 400.00 Deposit: 600" Now, talking about Money: I have a depos...

Binary data question...
Hi all, Happy new year :)) I need to save a FILE (doc, zip, pdf...) into XML. �Can I save this? I find in google some examples, and use binary node... this is the best solution?? and can I save everytypes of files?... Thanks... PD.: Sorry for my english... :) Luis wrote: > I need to save a FILE (doc, zip, pdf...) into XML. �Can I save this? I find > in google some examples, and use binary node... this is the best solution?? > and can I save everytypes of files?... The only solution is to encode bynary data (e.g. using Base64). XmlTextWriter class has appropriate WriteB...

Clip Organiser Problems 2003
When ever I try to search for or load a clip art onto my publisher programme I recieve the following: "Clip Organiser cannot complete the operation The Server threw an exception ERROR Code 0X80010105" I have found that downloaded clips fail to load onto my computer leaving empty files. Any Ideas as to the problem - should I re-install the programme? Clip2002: "Clip Organizer Cannot Complete the Operation, Unspecified Error 0x80004005" Error Message When You Use Clip Organizer http://support.microsoft.com/default.aspx?scid=kb;en-us;328166 Not exactly the same error, but...

Format Cell Problem #2
Hi, Sorry for all these dumb questions, but I never had any problems with Excel 97. This one is apparently just too complicated for me at my old age. Anyway, thanks for all the help; really appreciate it. So, using Excel 2007, and XP I select a cell that has a number in it with 5 decimal places. I go to Format Cells, and select Number, and tell it to go to 4 decimal places. It won't, and continues to show 5 decimal places. This is also true if I try it on a column of numbers. What am I not doing right ? (I am sure that what appears in the cell is truly a number, and not text.) T...

"Dot" problem
When if hit the space bar in a document, I get "dots" between the words as opposed to blanks. I've looked everywhere and can't find the solution. Help! thanks!!! On Tue, 16 Mar 2010 03:43:01 -0700, MO <MO@discussions.microsoft.com> wrote: >When if hit the space bar in a document, I get "dots" between the words as >opposed to blanks. > >I've looked everywhere and can't find the solution. > >Help! > >thanks!!! If you also see paragraph marks (�), click the button with that symbol on it or press Ctrl+Sh...