Repeated Values in One-to-Many Query (1 Viewer)

[DFlynn24]

Good Morning,

I'm in the process of reformatting a database at work, which contains drug-related incidents. There are two tables; incident/victim information and packaging color. The goal of my query is to be able to specify a date range and return all incidents for that time period.

However, because many incidents contain more than one packaging colors, I get repeating values for the incident/victim info table. I have thought about reducing the number of fields in the query, but they are all necessary, as we frequently need to pivot table the data and make charts, graphs, etc. The repeating values obviously throw off the numbers when manipulating the data later on.

Is there any way to make the fields blank after the first time they are displayed? So that the Date, name, etc. only display once. Maybe a Group By? I really have no idea

I've attached a test file here, with the two tables and a quick query. Any help is greatly appreciated!

 

[The_Doc_Man]

It is the nature of the beast that when you join a repetitive child table to a parent, the data in the child field IS repeated. However, my question is this: Are you showing the data as a form, a report, or in datasheet view?

In datasheet view, this repetition is going to occur. It would be tricky to suppress it, though you might wish to look up CONDITIONAL FORMATTING as a search topic here in the forum. I rarely used that myself because I used another method as noted below.

In a report, you can group things into group-headers and detail lines. Stuff you put in the group header doesn't repeat, but stuff in the detail lines DOES. That means you could achieve your desired effect by putting your "annoyingly repeated" data in the header and the desired repetition in the details.

On a form, you put the details in a sub-form and put the common data on the main form. So only put the repeating data in sub-forms.

 

[DFlynn24]

I am using the datasheet view. I would like to try to avoid using a report for two reasons: one, often times we will apply filters to see incidents over an extended period of time (by month, for example) by county, township, etc. Second, is that we often export the data to Excel and reports I have created in the past have not exported cleanly.

It would be ideal to just have the cells of those fields be blank after the first time thye are displayed, but it seems like that may not be possible, correct?

 

[theDBguy]

It would be ideal to just have the cells of those fields be blank after the first time thye are displayed, but it seems like that may not be possible, correct?

Hi. Unfortunately, that's not going to be easy to do (probably impossible) in Access. Each row of a datasheet is considered a record, and an empty "cell" would lose its meaning for that row. In Access, relationships between rows don't really exist. Even if you can do this in Excel, when you filter the column, what happens to the rows with "empty" cells? Wouldn't they also get lost and the meaning of the data is affected? Just a thought...

 

[DFlynn24]

I would keep my CaseID field so I don't think it would get completely lost. The repeating of names, dates, etc. is the real problem because it throws off analysis that gets conducted down the line

 

[theDBguy]

I would keep my CaseID field so I don't think it would get completely lost. The repeating of names, dates, etc. is the real problem because it throws off analysis that gets conducted down the line

Unfortunately, if you want to use Access, you may just have to "live with" the fact that the datasheet will show repeated information. Sorry.

 

[theDBguy]

The repeating of names, dates, etc. is the real problem because it throws off analysis that gets conducted down the line

By the way, I'm not sure I understand this part, but you don't have to explain it to me. I just want to say Access databases are used to analyze information all the time, so I can't picture your situation where these additional data is counter productive. Cheers!

 

[DFlynn24]

Well if I want to Pivot Table the data and see how many incidents a certain town had, it would inflate the numbers. If one incident had seven different kinds of packaging present it would count the town seven times when it should only be counted once

 

[plog]

If you can demonstrate your issue with data we can help you get exactly where you want to go. Provide 2 sets of data:

A. Starting data from your database. You have already done that with the database you posted.

B. Expected results of A. Using the data you posted show us exactly what you want to end up with. Don't explain it, show us with data.

 

[theDBguy]

Well if I want to Pivot Table the data and see how many incidents a certain town had, it would inflate the numbers. If one incident had seven different kinds of packaging present it would count the town seven times when it should only be counted once

Well, since Access doesn't have a Distinct Count option, you might consider using SQL Server, if that's what you need. Either that or do the analysis in steps, within what Access can provide. Just my 2 cents...

 

[DFlynn24]

theDBguy, I do appreciate your input! Plod, no problem see the attached spreadsheet. If you need further clarification just let me know.

 

[MajP]

Seems to me you are making an issue where there is not any. If you need charts and pivot tables that do not need the child records than use a different query for those. For that query instead of listing the colors simply provide a count of colors. It seems you want this one single output to do everything. I do not understand that. That is like saying you want one and only one query in access to answer all of your questions.

 

[DFlynn24]

MajP, but I do need the child records... I, and our partners, want to see which victims had which type of packaging in what places. Many of the incidents have multiple types of packaging. I want to be able to pull a month's worth of this data and easily be able to export it and e-mail it to someone that makes a request.

And I don't want one query to answer all of my questions. This output I want is excluding dozens of different fields I know are not feasible.

 

[MajP]

However you can definately do what you are asking

Do a group by getting the first color

SELECT 
 tblCase.caseID, 
 tblCase.TheDate, 
 tblCase.IncidentLocation, tblCase.Town,OtherFields
 tblCase.State, 
  First(tblPackage.PackageColor) AS FirstColor
FROM 
 tblCase 
INNER JOIN 
  tblPackage ON tblCase.caseID = tblPackage.CaseID_FK
GROUP BY tblCase.caseID, tblCase.TheDate, tblCase.IncidentLocation, tblCase.Town, tblCase.State, otherFields;

Do a right join on the child table and join on caseID and firstColor

SELECT 
 qryFirstColor.caseID, 
 qryFirstColor.TheDate, 
 qryFirstColor.IncidentLocation, 
 qryFirstColor.Town, 
 qryFirstColor.State, 
 tblPackage.PackageColor
FROM 
 qryFirstColor 
RIGHT JOIN 
 tblPackage ON (qryFirstColor.FirstColor = tblPackage.PackageColor) AND (qryFirstColor.caseID = tblPackage.CaseID_FK);

Note, I did not model all the fields in the table.

 

[DFlynn24]

I'll have to give that a try. Thank you for taking the time out to write that out.

 

[plog]

First, you should only use alphanumeric characters in table and field names. That means no spaces--it just makes coding and querying that much more difficult.

Second, what you want to do is achievable in a query, but is hacky. The best way to get the format you want would be in a report object with the proper grouping.

With that said, the below code will give you exatly what you want with the database you provided:

SELECT [Incident/Victim Info].CaseID, Min(Packaging.[Package Color]) AS MinColor
FROM [Incident/Victim Info] INNER JOIN Packaging ON [Incident/Victim Info].CaseID = Packaging.CaseID
GROUP BY [Incident/Victim Info].CaseID;

Paste that into a query and name it "sub1". It finds the "first" color for each CaseID. With that you can hide/show the appropriate fields for every record in the final query.

SELECT [Incident/Victim Info].CaseID, IIf(IsNull([MinColor]),"",[Incident Location]) AS LocationData, IIf(IsNull([MinColor]),"",[Town]) AS TownData, IIf(IsNull([MinColor]),"",[State]) AS StateData, IIf(IsNull([MinColor]),"",[Victim Name]) AS VictimData, IIf(IsNull([MinColor]),"",[Age]) AS AgeData, IIf(IsNull([MinColor]),"",[Gender]) AS GenderData, IIf(IsNull([MinColor]),"",[Date Entered]) AS EnteredData, Packaging.[Package Color]
FROM [Incident/Victim Info] INNER JOIN (Packaging LEFT JOIN sub1 ON (Packaging.[Package Color] = sub1.MinColor) AND (Packaging.CaseID = sub1.CaseID)) ON [Incident/Victim Info].CaseID = Packaging.CaseID
ORDER BY [Incident/Victim Info].CaseID, Packaging.[Package Color];

PAste the above SQL into a query and it will produce the results like you want. Again though, its a hack and you should really use a report to control how data is displayed. Reports can be exported to .rtf files and often can be customized to export to Excel as you want as well.

 

[MajP]

To make this work I used tblPackage with all of the caseID_FK in correct order. In reality you need to use a query where tblPackage is sorted by CaseID_FK and by packageColor. Every place I used tblPackage you need to replace with qrySortedColors (below)

SELECT 
 tblPackage.CaseID_FK, 
 tblPackage.ID, 
 tblPackage.PackageColor
FROM 
 tblPackage
ORDER BY 
 tblPackage.CaseID_FK, tblPackage.ID;

 

[Pat Hartman]

, and our partners, want to see which victims had which type of packaging in what places.

The nice thing about a relational database rather than Excel is that your presentation layer and data layer are not welded together. Simply use different queries for different reports. And make sure to use subforms and subreports to isolate child data. That also eliminates the repetition of the parent data.

 

[DFlynn24]

Plog your query worked like a charm, thank you very much. Just one question: one field I included in the second query's SELECT clause was the incident date. However, when I run the query and am in the datasheet I do not have the ability to apply a date filter because Access isn't recognizing the new field ( IIf(IsNull([MinColor]),"",[Date]) AS Incident_Date) as Date/Time. Do you know of any way to change the datatype?

 

[plog]

You don't apply criteria to that calculated field. You bring down [Date], uncheck it so it doesn't appear in the query results and apply the criteria underneath it.