Re: [MS_AccessPros] Input Form and Input Template Table Design

 

David=

You appear to be replying on the web.  When you do that, you need to be sure to click Show Message History so that those of us on email see the original thread.

Because you have one row per test in table 5 in your original post, you should have one row per test for each analysis for the "standard" tests.  When the user picks an analysis type, you need code in After Update of the combo box that picks the analysis to populate table 5.  Come to think of it, you need an extra table between 4 and 5 that lists for each sample the type of analysis required.

Fix that and come back with a complete list of the fields in each of your tables, and I'll try to help you with the code.

John Viescas, Author

Effective SQL

SQL Queries for Mere Mortals 

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications 

http://www.viescas.com/ 

(Paris, France)

On Dec 6, 2016, at 9:21 PM, david.pratt@outlook.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

My question may not be too vague, but apparently my skill set is too low to understand the advice.

I think I want the user to pick a sample template, not an analysis; e.g. "weekly raw water testing", which would be the description of a template record. But I don't know what fields to include in each template record.  Every template will not have the same number of analyses.  Should I just put in more fields for analyses than would ever be required?  And thus some fields would remain null and not get inserted?  Or is this leading me to another join table of some design.

And I am not experienced in this at all.  Could you give me some example coding of the insert query?  

I apologize for being "challenged" and I promise I will try to do as much work on my own as I can and not just ask this group for everything.

————————————————

David-

The template table sounds like the way to go.  When the user selects an analysis, pre-fill the lab reports table with the template rows.  You would need a bit of code to do that - run an INSERT query to add the appropriate rows from the template.  The user is then free to delete or add rows and record the results.

No, your question is not to vague or complex!

John Viescas, Author

Effective SQL

SQL Queries for Mere Mortals 

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications 

http://www.viescas.com/ 

(Paris, France)

On Dec 6, 2016, at 8:45 PM, David Pratt david.pratt@outlook.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Hi, I am new to the group and this is my first post so hopefully I will get this done in a manner that allows for easy response.

Access 2016

 

I am working on a database to record and report lab data.  I need to create an input form to enter the results of lab tests.  I have these tables:

1.       Table of analyses (many different analytical procedures may be performed)

2.       Table of sample points (many different sample points may be sampled)

3.       Join table for the two tables above since there is a many to many relationship

4.       Table of lab report headers (customerID, sample collection date, and Sample Collector ID)

5.       Table of lab report details (HeaderID, Analysis date, sample point ID, analysis ID, result)

 

Any of the analyses may be performed on any of the samples.  However, I have several sets of samples and analyses that are frequently repeated.  That is, certain sample points are collected every week and the same 6-8 analyses are performed on those samples each time.  I know how to make an input form based on the join table to enter data, but that requires the data entry to be a little time consuming, having to select the sample pointID and analysis ID each time to enter the result.  To simplify data entry for the repeated samples, I would like to make a form for each of those routine sample points that already includes the "normal" analyses so that the only manual data entry required is entering the result. 

 

I have no idea how to start.  Do I need to create another table? Table of " testing templates" or something of that nature?  And if so, what do I include in the table?

 

Is this too vague or too complex a question for this forum?

 

Thank you,

David  

__._,_.___

---

Posted by: John Viescas <johnv@msn.com>

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (4)

---

Have you tried the highest rated email app?

With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.

---

Visit Your Group

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___