one to many query

  • Thread starter Thread starter Sarah
  • Start date Start date
S

Sarah

Hi

Real Newbee here sorry.

Am trying to count the number of TempHead in the tblTempHeader Table where
there is a TempAU in tblTempExp but keep getting duplications (a count for
each of the children not the parent I guess). TempHead links to TempHead in
tblTempUsed Table. TempAU from tblTempUsed links to TempAU in tblExpType
Table. TempAU in tblExpType Table links to TempAU in tblTempExp. I find it
hard enough to describe it let alone work it out :)

So for every TempHead (this is unique per record) there can be multiple
TempAU and for TempAU there can be multiple TempAU in the tblTempExp table.

An E.g. would be for a car Type where you can have many models and each
model can be auto or manual (or something like that)

I need to know the number of Types of Manual cars.

Sarah
 
hi sarah, not quite sure about your structure...remember, your field/table
names are only meaningful to you. might be worth appending this question
with an "english" translation of what your database is about

anyhoos...sounds like your problem might be the lack of the word "distinct",
in your query. i dunno how to do this in the query builder, but if you open
your query in query builder, then go to VIEW | SQL VIEW, this will show you
the SQL structure of your query

....where it says "SELECT [names of fileds] FROM...etc

change it to read "SELECT DISTINCT ....

this should cure it...hopefully

jimaldo

ps can someone enlighten me as to how "distinct" sql translates in a query
builder? is it by "grouping"???
 
Thanx
I do have Select Distinct so it's probably just the way I'm trying to setup
the query, I have no idea what I'm doing in order to get what I want.

This database has a record of transactions (TempHead) and applies data to
another database. Each transaction has a variety of Templates (linked to
transaction table by TempHead) depending on what is to be done. Eg. Update
or Add. Using (TempAU) to link to a type where one of the types is an
exception action. I need the number of transactions that are exception
actions. I currently get about 4 x that number :( I am also counting by
Company derived from another table.

I have included all tables in the query. Company, ascending, group by.
TempHead, count. But don't know what to do with the other tables, do i need
a Where/How or What the hell am I doing here :)
Sarah
 
hmmmmmmmmm...have you defined relationships for ALL your tables? or tried
adding a "group" to your TempHead table? failing that, have you tried
screaming "WHY DON'T YOU WORK?" at the monitor? (expletives might help here
too!)

jimaldo
 
I do have Select Distinct so it's probably just the way I'm trying to setup
the query, I have no idea what I'm doing in order to get what I want.
Click to expand...

It would help us if you could open the query in SQL view and copy and paste
the SQL text to a message here. It may be necessary also to explain a bit
about the nature of the tables (primary keys, relationships, etc.).

John W. Vinson [MVP]
 
Back
Top