|
|||||||||
|
|||||||||
| |||||||||
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
Dev Shed Forums Sponsor:
|
|
Be the architects of evolution and help create the mobile internet future. It’s your move---enter to win here! |
|
#1
April 25th, 2004, 12:27 PM
|
|||
|
|||
|
MS query help
This is for a college project: I am trying to average all records of the same ID but also have it do the same for all of the IDs within the same query. Is their a way of doing this?
From Tables Sow/Gilt ID Litter NumberBornLive Thanks Chris
|
|
#2
April 25th, 2004, 02:01 PM
|
|||
|
|||
|
what DBMS?
Did you set up the tables exactly as you have in your post??... TablleName (Columns) Sow/Gilt (ID) Litter (NumberBornLive) There's a fundamental flaw in the way you have these setup. As is, there is no way to relate fields from either table. You need to have a field/column that relates the two tables...a relationship. For example, put an ID field in the 'Litter' table that way we can relate what Sow/Gilt is tied to the specific number in the NumberBornLive field... TablleName (Columns) Sow/Gilt (ID) Litter (SowGilt_ID, NumberBornLive) ...the Sow/Gilt.ID would then = the Litter.SowGilt_ID field. Until you do that, we really can't come up w/your solution.
|
|
#3
April 25th, 2004, 02:17 PM
|
|||
|
|||
|
I am using MS Access 2000 and 2003.
Here is the structure of my DB: Boar BID(PK) DateEnteredHerd CulledDate CulledReason UID(FK) Sow/Gilt ID(PK) DateEnteredHerd Genetics CulledDate CulledReason DiedDate DiedReason UID(FK) Services SID(PK) ID(FK) BID(FK) DateBred DateToFarrow FailedToFarrow BreedingComments UID(FK) Litter LID(PK) DateFarrowed NumberBornLive DateWeaned NumberWeaned Mastitis Stillborn Mummies SID(FK) UID(FK) PreWeaningDeaths PID(PK) LID(FK) DateDied Cause UID(FK) UserID UID(PK) FirstName LastName Last edited by KillerKeller_jr : April 25th, 2004 at 02:26 PM.
|
|
#4
April 25th, 2004, 02:42 PM
|
|||
|
|||
|
Ok cool...so for each ID in the Sow/Gilt table, you want the AVG of NumberBornLive in the Litter table?
Last edited by Username=NULL : April 25th, 2004 at 02:52 PM.
|
|
#5
April 25th, 2004, 02:50 PM
|
|||
|
|||
|
Quote:
Yes, correct although I am also going to do it for the NumberWeaned too. Thanks.
|
|
#6
April 25th, 2004, 02:53 PM
|
|||
|
|||
|
OK, sorry, was editing as you were typing, I can get it...just have a question...
In the Services table you have 'ID' as a FK, what is it a FK of?...possibly the Sow/Gilt.ID?
|
|
#7
April 25th, 2004, 03:49 PM
|
|||
|
|||
|
Quote:
You are correct, it is a FK of Sow/Gilt.
|
|
#8
April 25th, 2004, 07:38 PM
|
|||
|
|||
|
Give this a try, see if it works...
Code:
Select distinct SG.ID, AVG(L.NumberBornLive) as AvgBornLive
from Sow/Gilt SG
inner join Services S
(inner join Litter L on S.SID = L.SID)
on SG.ID = S.ID
group by SG.ID
Last edited by Username=NULL : April 25th, 2004 at 07:41 PM.
|
|
#9
April 25th, 2004, 07:51 PM
|
|||
|
|||
|
It doesn't seem to like the / in SOW/GILT in the from clause. Should I change it or what. Must be MS Access that doesn't like it.
|
|
#10
April 25th, 2004, 09:13 PM
|
|||
|
|||
|
You can get around that by using brackets around the table name. Give this a try...
Code:
Select distinct SG.ID, AVG(L.NumberBornLive) as AvgBornLive
from [Sow/Gilt] SG
inner join Services S
(inner join Litter L on S.SID = L.SID)
on SG.ID = S.ID
group by SG.ID
...but ya I would probably change that table name, I was actually gonna suggest it but if it works it works. I rarely user any special characters in my table or field names though...and I think most would agree it's a popular convention. Last edited by Username=NULL : April 25th, 2004 at 10:57 PM. Reason: Double checked and yes, it is possible...
|
|
| Viewing: Dev Shed Forums > Databases > MS SQL Development > MS query help |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
