Removing duplicate records in query

[PNGPort]

I’m trying to write a query that shows all the container movements. Yet when I run the query “qryFullHistory” I get a duplicate value for container “Off Island”. I’ve tried adding some criteria that says that the DateRequested has to be between the ImportDate and ExportDate but that doesn’t seem to work. There are duplicate entries for container “Off Island” in “tblContainerDetails” as the same container has arrived and left and then returned on another voyage. Yet there is no entry for the second voyage in the tblMEMRContainer.

A brief description of the tables is:
tblMEMR – Movement requests details
tblMEMRContainers – the containers that were moved on the movement request. There can be more than 1 container for each request.
tblContainerDetails – details and dates for the container when it arrived and when it left

There are other tables but these are the 3 that are used in the query.

Your help is greatly appreciated.

 

[JHB]

Which one should not be in the result from the query and why?

 

[PNGPort]

The record for container "Off Island", voyage 453 should not be showing a MEMRID as it doesn't have a matching record in tbleMEMRContainers. The records in this table for the container relate to the previous voyage (465). The DateRequested on the tbleMEMR must be between the import and export date of the container. (We can't move a container once it has been exported.)

The purpose of the query is to show all the container records from tblContainerDetails and matching MEMRID, where there is one. If there is no matching MEMRID for the container then it still needs to show in the query.

I hope that clarifies the question.

 

[namliam]

Really text IDs?? Really?

Off Island / 453 in tblContainerDetails has te Key "Off Island" which is exactly the same as the Off Island / 465... Since they have the same key, they have the same MemrID from tblMEMRContainers (43)

The database is doing exactly what you are telling it to do, sounds like a design issue to me.

 

[PNGPort]

What's girl suppose to do when a container ID is a mix of numbers and letters?

Thanks for the explanation on why my query doesn't work. I've have a rethink on the design.

 

[namliam]

well typicaly a GUY uses autonumber fields inside the database to use as (hidden) ID/PK/FK instead of using a random string. Just like you do with your tblContainerStatus

Worse even is that you text fields all are defined as a 255 char field instead of the required 20 or maybe 30/40/50

Another tip:
JobComplete should be a derived field from DateCompleted, if that is filled then obviously job is completed...

 

[PNGPort]

Yes I agree the 255 characters was a bit excessive. And yes, if the date completed is filled then the job is complete. I'll update the database.

I understand the "neatness" of using AutoNumber as a primary key but why wouldn't you use a field that is unique identifier for all of the infomration - in this case the container ID. (And yes, I should read more of the forum before I ask this question.)

 

[namliam]

A lot of (as it is called) natural keys, which is deemed to be "everlasting" or "never changing" somehow ends up changing anyways... Having the user work work with a natural key like a container name "Off Island" or anything simular is fine.... A database should work with a key that is GUARANTEED to be unique and never changing.

By its very definition a natural key can always change.... if only because it will make sence to the human using the keys....
I.e. Containers called
Sea Container 1
Sea Container 2
Sea Container 3
Sea Container 4

Container 2 is lost for some reason or another, for some time the humans will remember this and leave it blank... At some point someone is going to reshift it to be 1,2,3 or buy a new one and recall it 2 again.
Or perhaps people like acronyms and start using SC1,2,3,4 etc etc etc... many many reasons that the natural key may change, be reused....

On top of which there is a performance gain to work with numbers instead of texts both in process speed when using queries and all that... as well as most likely if you use the keys more and more a storage gain as well.

So many pro's to using a proper key

 

[PNGPort]

Thanks for the explanation. Have started redesigning the database with numeric primary keys.