Selection of records (1 Viewer)

Sam Summers

Registered User.
Local time
Today, 22:13
Joined
Sep 17, 2001
Messages
939
Hi All,

I'm not sure how to search for this hence the thread?

I have five items that form of Kit number 1

Using the query as in the image i am wanting to just display the kit Location History but at the moment it displays the location history for every item for every location?

So i am getting 20 records instead of 4 records if you know what i mean?

I think its simple but i have tried a few things and am missing something as usual?

Many thanks in advance
 

Attachments

  • Screenshot_1.png
    Screenshot_1.png
    22.6 KB · Views: 49

MajP

You've got your good things, and you've got mine.
Local time
Today, 18:13
Joined
May 21, 2018
Messages
8,463
You need to put the kit number in the criteria row for the kit number field.
 

Sam Summers

Registered User.
Local time
Today, 22:13
Joined
Sep 17, 2001
Messages
939
Hi and thanks for replying.

I thought we had it then but still no?

It no looks like the attached screenshot
 

Attachments

  • Screenshot_2.png
    Screenshot_2.png
    27.2 KB · Views: 41

jdraw

Super Moderator
Staff member
Local time
Today, 18:13
Joined
Jan 23, 2006
Messages
15,364
Sam,
You are showing the query design. Can you also post a picture of the result of the query?
 

Sam Summers

Registered User.
Local time
Today, 22:13
Joined
Sep 17, 2001
Messages
939
Many Thanks guys for your help!

I have attached the result and also the SQL if that helps?
 

Attachments

  • Result.png
    Result.png
    28.6 KB · Views: 45
  • SQL.png
    SQL.png
    9.5 KB · Views: 42

jdraw

Super Moderator
Staff member
Local time
Today, 18:13
Joined
Jan 23, 2006
Messages
15,364
Sam,
I looked at the result. All deal with Kit #1. Seems that JobNo and MobilisedTo both have "STORES" as values---Is there an issue with this (bad data), and some MobilisedTO are not valued??
What would you want as proper output?
 

Sam Summers

Registered User.
Local time
Today, 22:13
Joined
Sep 17, 2001
Messages
939
The KitNo comes from a form.

I'm sure theres a conflict somewhere?

Just not quite sure how to crack it yet.

I can display location history for single items but wanted to add this enhancement for a whole kit?
 

jdraw

Super Moderator
Staff member
Local time
Today, 18:13
Joined
Jan 23, 2006
Messages
15,364
Sam,

Can you mock up a sample of what output you would like from the data you have shown?
 

Sam Summers

Registered User.
Local time
Today, 22:13
Joined
Sep 17, 2001
Messages
939
Well in the 'Mobilised to' Field it would just have one of each location.

basically what it is doing now is showing the location for every item of equipment in Kit 1 instead of showing each location where Kit 1 has been.

So there are 5 items in Kit 1 and so we are seeing 'Mobilised to', 5 times for every item in the kit.

Hope that makes sense?
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 17:13
Joined
Feb 28, 2001
Messages
27,001
If I understand your question, the problem is "where Kit 1 is now." You don't have a date exclusion so the problem may be that you are asking the question "Where has kit 1 been" as opposed to "Where is kit 1 now?"

Take a look at your picture of the design grid for KitLocationHistoryQry. You have four tables in the mix. I have no issue with the structure, but will discuss the question you asked based on that structure. You have five pieces of equipment but only one kit number. That kit has been four places. Therefore you get 20 results.

Remember that Access and SQL Set Theory are basically combinatorics engines. Your only selection criterion is based on the tail-end table of a four step relationship. So the question Access asks is "What are all the combinations of joined records that contain the stated selection criterion." And two of those tables have many-to-one relationships. AccessOnHire to Equipment gives you 5 combinations that lead to KitLookup (from having 5 items?) and then you didn't constrain the locations by date, so that gives you 4 records x the 5 paths.

My advice is therefore two-fold. First, you want to figure out something to include in your WHERE clause to limit location to "current" location. I don't know how you show that in your table, but that is why you have the full history. Second, since you are showing the kit but NOT the individual kit members, perhaps you need to employ a DISTINCT keyword in that query. You might have to play with it a bit, but that should clear up the mess.

I should also add that it is possible for you to use exactly the query you have but to put a layered query that selects records from this one, and have the layered query do the date and DISTINCT selections.
 

Sam Summers

Registered User.
Local time
Today, 22:13
Joined
Sep 17, 2001
Messages
939
Thank you for that.

Yes i need to have a good look at it but i think like you said - something along the lines of DISTINCT might be the way?

I shall see what i can figure out and if i do at some stage i will post my solution here.

Thanks all for your input
 

Users who are viewing this thread

Top Bottom