Select Count and Group Query (1 Viewer)

[GaryM]

I have built a database that shows the purchase of items that have serial numbers, and so are unique. The database shows the purchase oe each item, and subsequent sale, including "Date In" and Date Out".

Each "item" however has a unique transaction reference (Stock No.)
I would like to be able to show what items are currently in stock, and therein lies my problem.

I am able to use a select count to find all the instances where the stock number there are two stock numbers (ie In and then Out), but have been unable to find a way to filter the records in a query, to show the stock currently held.

Can anyone give me a pointer as to what terms I should use to these, so that only Single instances of the "Stock No." appear in a table, as that would show the current stock held.
I have tried numerous ways to achieve this but I have reached a dead end. I am not experienced in writing with SQL, any advice would be appreciated.

 

[namliam]

something like:
Select stockno, count(*)
from yourtable
Group by stockno
having count(*) =1

???

Also please read and understand: http://www.access-programmers.co.uk/forums/showthread.php?t=225837&highlight=name+convention

 

[GaryM]

Thank you for your suggestion and the additional reading with regards Access convention, been looking for something like that for a while. Unfortunately work beckons for the next day or two, so will come back to you with my results as soon as I can,
Thanks again

 

[namliam]

The naming convention is good practice for most databases, not just access

 

[GaryM]

Please have some patience with me as I am a newbie. I have tried to apply the criteria as you have suggested, and the results are correct. The query returns all the single entries. However If I try and now add Customers details, address etc to that query, I immediately get the second entry added to the query.
The default action when I add another field is for access to suggest I "Group by" the entry.
If I remove "Group by" from that field, I get an error message.

I have spent some time trying to sort this, and I appear to be so nearly there, could I ask if you could give me just a little more guidance on how to add other fields to the query.
I have attached two screen shots to show where I am so far.
Thanks in anticipation

 

[namliam]

I see you havent taken to the Naming convention yet, which is fine for this database because it is probably already quite involved and a lot of work to re-hash it all.
However do keep it in mind for future databases please?!

Yes you need to have the group by there, the thing is you add the surname so you want to see each customer or something right? so why wouldnt you expect the 2 rows as output?

 

[GaryM]

You are right about the naming convention, this database would have to be re-written from scratch, something I intend to do when I know that all the aspects of it work.
The purpose of the query is to show current stock along with the person / company it was purchased from. It may well be that this person will appear in other fields, but I only want to show the "single" returns with associated details.

 

[namliam]

What if your stock was purchased from 2 companies? Then you will get 2 "piles" of stock...
I.e.
Bought 100 from Company A
Bought 100 from Company B
Sold 50

In stock 150 mixed from Company A and Company B

 

[GaryM]

I understand what you are saying, but the stock number only ever refers to one item, never multiple (Each item has a serial number, and is unique).
The purpose of this DB is to accept the item into stock, and if sold not to show it in the current stock levels.
Potentially, the same item could be "re-purchased" back into stock, and should that happen a new Stock No. is issued to reflect this. There will never be multiples as you describe.

 

[namliam]

Then if there shouldnt be multiples, adding the surname should be unique as well... Hence no duplicate records should exist....

Yet you say you get double lines, so then I dont understand what is going on?

 

[GaryM]

Namliam,
really appreciate your help, it is so hard to explain. It is quite possible that the same supplier or customer will appear in the database numerous times. I only want to pull out the stock currently held, and where it was purchased from.
I would like to be able to show you the DB so you have a clearer idea of what I am trying to convey (very badly in words).
I do not wish to post the DB in the forum, so I am not sure how I can get any further with this.
:banghead:

 

[namliam]

... database numerous times....
This might indicate a bad design, if that isnt the same PK for the supplier

Can you take your database, strip it down to only the tables involved and enter some mockup data?

 

[namliam]

Tried sending you a PM, but I cant appearently

 

[GaryM]

Seems like I have ground to a halt then.
Thanks for trying to PM it would have been useful

Gary

 

[namliam]

Maybe because you actively turned PM off or due to there are some restrictions on this forum to users at or below 10 posts (I believe)

 

[GaryM]

I haven't turned PM off, guess it is because I am still under 10 posts, Thanks for trying