Not sure what I am doing wrong but I tried to reply via email and apparently it did not go through. It appears that when I hit reply in my email program the recipient is NR (no reply). But I know earlier in the thread I did reply via email. Maybe I messed the thread up by replying first via email and then on the web? And now I can only reply via the Web?
Anyway, let me first apologize for giving initial misinformation. Crystal, thank you for the reply. Those were not actually my field names, rather descriptive terms. I will use actual field names in the future. John, I apologize profusely to you. I do not have a join table. I have master and detail tables. I confused myself because in the details table there are many sample points and many parameters, and any parameter may be associated with any sample point and vice versa.
I have redesigned the tables and fields, and here is what I have now:
tblServiceReports:
ServiceReportID
CustomerID
EquipmentID (equipment which will have one or more water sampling points)
SampleDate
ReportDate
AccountManagerID
tblServiceReportDetails
ServiceReportDetailID
ServiceReportID
SamplePointID (will be one or more sample points)
ParameterID (will be several parameters for each of the sample points)
Result
Thinking that a Template table is the way to go, I created template master and template detail files:
tblServiceTemplates
ServiceTemplateID
CustomerID
EquipmentID
TemplateDescription (e.g. "template for weekly servicing of ABC's raw water clarifier)
AccountManagerID
tblServiceTemplateDetails
ServiceTemplateDetailID
ServiceTemplateID
SamplePointID
ParameterID
Result
So instead of having to create each individual ServiceReportDetail record by selecting a SamplePoint, then selecting a Parameter, and then entering a result, I want to have all of the SamplePoints and Parameters already available so that I only have to enter a Result.
I think I need Templates to do this, but I do not know how to get the data from the template into the related ServiceReport master and ServiceReport details files. I envision the Result field in the ServiceTemplateDetails to be a temporary place holder for the result. That is, after each time the template is used to collect the results and the results are moved to the tblServiceReportDetails, the Results field will be cleared from all of the Template details records. That is to say the TemplateDetail records will be used over and over.
I have a Templates form that contains the Template master fields and with a subform that contains all the Template detail fields. I can choose the Template master record and then enter the results in the Results field. I put a couple of Command buttons on the form and assume I will need to code some actions to those Command buttons.
Can you help me with that or am I going the wrong direction?
Thanks, David
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
David-
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
Now that I think more about it in the cold light of day, you don't need a template table at all. In your linking table between Analyses and Sample Points, add a column (can be a simple Yes/No) that you use to mark (with Yes) which samples are the "standard" or "default" for a given analysis. When the user picks an analysis, populate the lab report details with all those rows in the linking table that are marked yes. If you give me a layout of all your tables, I can help you write the code to do that.
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
(Paris, France)
On Dec 7, 2016, at 4:34 AM, david.pratt@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
OK. Thanks for telling me about the message history. this is my first time and I did not know the difference.
I apologize but I am not following about the table design. Isn't the "extra table" between 4 and 5 the Templates table that I originally asked about?
I am thinking that I need perhaps two additional tables; one that is the Templates table and one that is the Template details table. The template table would contain the sample identification and the details table would contain the analyses associated with that sample Template?
I will create those tables and then come back with hopefully a more focused question.
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
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
(Paris, France)
On Dec 6, 2016, at 9:21 PM, david.pratt@... [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
(Paris, France)
On Dec 6, 2016, at 8:45 PM, David Pratt david.pratt@... [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)
__._,_.___
Posted by: david.pratt@outlook.com
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (8) |
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.
.
__,_._,___
Tidak ada komentar:
Posting Komentar