Queries :: Select Count And Group Query
Sep 19, 2014
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.
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.
View Replies
May 11, 2014
I'm fairly new to Access. 's various select queries containing useful and useless results. I want to create a select query that will pick out all the useful figures into a 1 row table that can then be pasted into Excel.
e.g Existing Select Query 1 returns 1 row showing Average Age, Average Price, Total rainfall
Existing Select Query 2 returns 1 row showing Average Weight, Average Salary, Total snowfall
Existing Select Query 3 returns *2* rows: It returns Distance from London, Hours daylight and population for Town A and Town B
I want a select query that returns 1 row showing (6 items):
Total rainfall, Total snowfall, Town A Distance from London, Town A Population, Town B Distance from London, Town B Population.
I've been able to handle getting Total rainfall and Total snowfall. But I cant figure out how to get Town A Distance from London, Town A Population, Town B Distance from London, Town B Population to appear in the same row of the same query results as Total rainfall, Total snowfall.
View 3 Replies
View Related
Nov 30, 2007
Hello All,
I'm currently developing a football team website and I'm created a player profile bit of the website linking to my Access Database.
Currently I have the following :
selectCommand="SELECT [playername], [favouriteposition], [picture] FROM [player] WHERE [playername] ='Smith J'
This means I can display John Smiths Name, Fav Position and his Picture from my player table.
I also want to count his stats (goals, starts, subs) but am not sure how to do this.
I have a table called playermatchevents which records matchID, playerID, shirtnumber, goals, starts, subs, mom
goals is a straightforward Number datatype field, whereas starts,subs and mom are a Yes/No datatype field.
Basically I want to keep my select query, but I also want to add to it that it COUNTS the number of starts, subs, goals and mom that that player has.
Pardon me if I haven't used the correct wording, I'm still quite new to all of this.
I would be very grateful for any help.
Thanks,
covcitydv
View 6 Replies
View Related
Dec 3, 2004
Hey all!
I'm having issues trying to create a query that will take data from two tables and summarize it based on ID, Count(ID), Sum(Score), Sum(Xs). I don't have an issue doing the combining, just the summing and counting (actually, just the counting). I've tried playing directly with the SQL statement, but I keep getting errors.
This is what I have:
tblRoster
ID, Name, Sex, Youth
tblScores
ID, WeekNo, A1, A2, A3, A2X, A3X, B1, B2, B3, B2X, B3X
Sample Data for tblScores would look like this (scores actually are not the same, but you get the idea)
10, 1, 90, 89, 87, 3, 7, 87, 92, 99, 4, 7
10, 2, 90, 89, 87, 3, 7, 87, 92, 99, 4, 7
10, 3, 90, 89, 87, 3, 7, 87, 92, 99, 4, 7
10, 4, 90, 89, 87, 3, 7, 87, 92, 99, 4, 7
23, 1, 90, 89, 87, 3, 7, 87, 92, 99, 4, 7
23, 2, 90, 89, 87, 3, 7, 87, 92, 99, 4, 7
23, 3, 90, 89, 87, 3, 7, 87, 92, 99, 4, 7
23, 4, 90, 89, 87, 3, 7, 87, 92, 99, 4, 7
23, 5, 90, 89, 87, 3, 7, 87, 92, 99, 4, 7
67, 1, 90, 89, 87, 3, 7, 87, 92, 99, 4, 7
67, 2, 90, 89, 87, 3, 7, 87, 92, 99, 4, 7
67, 3, 90, 89, 87, 3, 7, 87, 92, 99, 4, 7
What I would like to do is summarize the info in the following manner:
ID, Name, Sex, Youth, Count(WeekNo), Sum(A1+A2+A3+B1+B2+B3), Sum(A2X+A3X+B2X+B3X) such that I would get the following results:
10, John Smith, M, , 4, 2176, 84
23, Jane Doe, F, , 5, 2720, 105
67, Johnny Jones, M, Y, 3, 1632, 63
I have been able to get the summed part done, but my attempts to do the count have failed. I know that the resulting query will be straight forward, but right now I'm cross-eyed.
Thanks.
-BT.
View 5 Replies
View Related
