How do I prevent duplicate entries in many to many relationships?

[CGadda]

Got a problem that my introductory class in databases did not prepare me for. I have some many to many relationships that I have sort of resolved via associative entity tables. However, I have an issue where I get duplicative outputs in my queries.



The database I'm working on is intended to track hardware and software service level costs for a given computer S/N. It is possible to have multiple hardware and software services assigned to a single S/N. The problem I'm finding is how to avoid duplicative costs. in the attached examples I have (for this particular set of two devices) a single HW service cost and multiple SW service costs. While the SW costs are fine, Access insists on filling in the rows with the same HW cost to match with each SW cost, even though there is only ONE HW cost (obviously, when I try to show totals, my results in this example are all wrong because the HW cost is now tripled!)

Looks like I cannot insert attachments inline but they should be at the end of the post. The first screenshot shows the current output. The second is a simplified depiction of the portion of the database's relationship diagram, and the third is sort of an idealized "what I should see" as a query output. I'm hoping you all can provide tips as to how to fix this problem. Thanks!

[madpiet]

Don't totally understand the Totals tables, but anyway. (normally, that would just be a simple totals query).

The standard structure for a problem like this is something like this. (Say you have a set of license keys that you can reuse some number of times).

Computer(HWSerialNo (Primary Key), {other columns})

ComputerSoftware (HWSerialNo (primary key 1), SoftwareKey (primary key 2), InstallDate )

Software (SoftwareKey (primary key), software title, LicenseCost, # of installs covered)

then this is easy. If unit cost is related to Software, then this is crazy simple

Oh. "Preventing Dupllicates". "Reading is Fundamental!" Sorry, forgot that bit. You create a unique index on the combination of (HWSerialNo and SoftwareKey) in the ComputerSoftware table. (I think you CTRL-click or Shift-click the two columns and then you click the Primary Key button. And that will ensure that you can't add more than one license for a given piece of software more than once for each Computer.

SELECT cs.HWSerialNo, cs.SoftwareKey, cs.InstallDate, s.LicenseCost
FROM ComputerSoftware cs INNER JOIN Software s ON cs.SoftwareKey = s.SoftwareKey
INNER JOIN Computer c ON c.HWSerialNo = cs.HWSerialNo

then you can base counts etc on that query.

[mike60smart]

Hi
Are you able to upload a copy of your database?

[Micron]

Based on my interpretation of the relationships, it is wrong. I take it that "bridge" tables are meant to be junction tables. You're linking PK fields in junction tables to parent tables rather than pk in parent to fk in child, then between junction and parent/child. In one or more cases you have pk fields that are not joined to anything.

Aside from the relationships, did they not teach you to avoid using special characters in object names (save for underscore _ ) and to not store calculations in 99.9% of cases? They should have. Note that you cannot post db copies by cut and paste. You must follow the same process as how you got the images in your post (unless things have changed here). So if you meant to provide a db copy, most of the time it must be zipped and should be compacted first.

[June7]

That's the way query should work. Getting the second output in a query is difficult. Better to build a report. Use its Sorting & Grouping features and textbox "Hide Duplicates" property.

[June7]

Ooops, my suggestion will not fix the totals issue.

Maybe should be a report/subreport structure.

Really would help to have your data for analysis.

Get rid of those dollar signs from names.

[madpiet]

primary key of the junction table is wrong. It should be the combination of the two primary keys of the related tables.

The combination of (SystemCostUID, HardwareCostUID) in the junction table should be unique. (click on both, add primary key)

Then when you try to enter a duplicate, the table validation rules will reject the insert. (Generally speaking, do that for conditions that must ALWAYS be true.... and foreign keys is one of those... otherwise you deal with it in code... which in Access is a PITA).

[June7]

Doesn't have to designated as compound key, can just be compound unique index.

And duplicate pairs in table is not the issue asked about. It's about repetitive data in query joining tables.