Help creating summary report based off of criteria

[bfriend5]

Hi everyone,

I'm having difficulty coming up with a way to create a summary report for a specific criteria. My database tracks test samples that a lab evaluates at different time intervals which are not always the same; some samples could be evaluated at different weeks or some can be evaluated at one month, three months, and six months. Every individual instance of testing is entered separately into the database, and I would like to create a report that can group all of that data together, but only output that specific evaluated sample. Is this possible? Can any of you point me in the correct direction to figure out how to accomplish this? Or if you know of a better way to go about doing it I'm definitely open to suggestions. I feel like I'm not searching the correct terms in google as I haven't come across a solution as of yet. Thanks.

 

[Ranman256]

Well if you have Sample123 to test, that is stored in the master table and all of its info.
A second tTest table would hold all testing done on tSample. infinite # of tests to report on.
tTest
--------
[dateOfTest]
[StartTime]
[EndTime]
[Result]

 

[bfriend5]

Thank you, I should have clarified a bit more in my original post because my data is more spread out than just a master table and a secondary test table. Here are my tables and the fields from those tables I'd want to include in a summary report:

Table / Fields

tblEvaluatedSample / ReferenceNumber, DateTested, Scope, SuccessCriteria, TestResult, Conclusion

tblClassification / Class1, Class2, Class3
' prioritizes samples between arbitrary in-house classification model

tblSampleTyp / SampleTyp
' This is in a one-to-many relationship with tblClassification because there can be many types for one classification

tblProduct / Product, ProductCode, ManufactureDate, LotNumber
' separate from the evaluated sample

tblTestCriteria / DesignOfExperiment, PassingScore
' many sample will follow the same criteria, but it does change

tblRequester / Requester

tblContactInfo / ContactName, ContactEmail


I was assuming that some sort of query would be the way to go about doing it. I just don't know if it would be based on union query or a join query with the correct criteria or a where clause.

 

[vbaInet]

Perhaps you can show us some sample data and then also show us how you would like it summarised.

 

[Ranman256]

Definitely, bring in the primary and secondary tables into the query.
then add on any ancillary tables connected to their matching fields...tRequestor, tContacts.

 

[bfriend5]

Perhaps you can show us some sample data and then also show us how you would like it summarised.

As in just give you examples of what the data might be or a picture or a form? Or something else entirely?

 

[bfriend5]

Definitely, bring in the primary and secondary tables into the query.
then add on any ancillary tables connected to their matching fields...tRequestor, tContacts.

When adding ancillary fields do you just add the FK from that table, or do you need the PK from the original table to be included as well?

 

[vbaInet]

In a spreadsheet, show the before and the after.

 

[bfriend5]

In a spreadsheet, show the before and the after.

I attached a small excel spreadsheet showing how the data is compiled and how I'd like it to be grouped in a summary report. I hope this is what you're asking for, if not, I'll try again!

 

[vbaInet]

Clear as mud, good example.

Have you been able to pull in all the data from the different tables or is that what Ranman256 is helping you with?

You'll eventually need a crosstab query to get the summarised data. There are other ways but it requires coding. See the link below for more on crosstab queries:

http://www.fmsinc.com/Microsoftaccess/query/crosstab-report/index.html

 

[bfriend5]

Clear as mud

So, not good? If that's not what you meant I can try again..

I can pull the total data together, I just don't know how to structure the parameters/criteria to get specific Evaluated Samples based off of user input.

Thank you for the link to that site, I'll look over all of that information about crosstab queries and hopefully be able to put something together.

 

[vbaInet]

Clear as mud meaning, what you uploaded was a good example

To structure the input you can do it in two ways:

1. Using a parameter query
2. Using a form to get the parameters and passing those values to your query

To keep it simple for now, look into option 1.

 

[bfriend5]

Okay, thanks for the help! I'll focus on reading that site and looking up more information on parameter queries.

 

[vbaInet]

I'll give you a hand with the search, here's a good link:

http://www.fontstuff.com/access/acctut01.htm

Any questions, ask away.