little help with queries (1 Viewer)

[SantoR]

i have two table A , table B
table A has ID(primary key), dept, ....
table B has ID(Primary Key), type, date....

let say i have dept as D1, D2, D3 in 10, 20 , 30 numbers in table A
ie count(D1) is 10....

table B might have a certain record corresponding to ID in table A let say
it has 1 records with type = 'general' and date= some date

so like that

i am looking for a query that will give me output as

:between a selected date range
(number of records in table B of certain type)/(total number of records in that dept in table A)

ie
1/10 in the example case

for certain type selected
D1 : 1/10
D2 :....
D3 :....

 

[SantoR]

i m using query

SELECT TableA.Dept, Round(Sum(IIf([TableB].[Type]='general',1,0))/Count(*)*100,2) AS General Rate]
FROM TableA LEFT JOIN TableB ON TableA.ID = TableB.ID
WHERE ((TableA.Dept) Like 'D1' Or (TableA.Dept) Like 'D2' Or (TableA.Dept) Like 'D3') AND ((TableB.Date) Between [Forms]![MainForm]![Sub_DisplayFm]![From_Dt] And [Forms]![MainForm]![Sub_DisplayFm]![To_dt]))
GROUP BY TableA.Dept;

if i dont give date range then it works fine

but with date range it is not giving right result...because result set is getting decreased (it is calulating 1/1 and not 1/10 for D1)

can you help me correct this query , where to put the date criteria so that it considers the date in table B but not in table A

 

[vbaInet]

SantoR, give some examples with data. Show us some sample data in TableA and some sample data in TableB.

 

[MarkK]

I would not expect a link between two primary keys in two different tables. That is essentially a one-to-one relationship, and then you might as well just merge it all into a single table, and save on headaches.

 

[vbaInet]

MarkK, I was also thinking that it's a one-to-one relationship but the mention of "10, 20, 30 numbers" and the LEFT JOIN made me think that it might be a one-to-many relationship and just not properly explained.

 

[MarkK]

(it is calulating 1/1 and not 1/10 for D1)

Sounds like it is behaving like 1:1, but I see your point.

But to me, tableB defines no discrete type of object. TableA should be named tDepartment, but what do we name TableB? It has a type (of what, who knows) and a date. That does not comprise an "object" that we would expect a table to describe. I think this is at the core of the conceptual problem, that tableB doesn't describe anything, and so then how is it related to anything? What is it?

But Type and Date don't describe a Department either. How does the type of a Department change over time?

*stumped*

 

[SantoR]

your questions are good...
tableA has a ID's which falls under a dept.
since tableB (Scrap details) has lot of other data..so i have kept it separate form TableA

solutions is

SELECT TableA.Dept, Round(Sum(IIf([temp_TableB].[Type]='general',1,0))/Count(*)*100,2) AS General Rate]
FROM TableA 
LEFT JOIN (SELECT TableB.ID, TableB.Type FROM TableB
WHERE (((TableB.Date Between [Forms]![MainForm]![Sub_DisplayFm]![From_Dt] And [Forms]![MainForm]![Sub_DisplayFm]![To_dt])) GROUP BY TableB.ID, TableB.Type) as temp_TableB 
ON TableA.ID = [temp_TableB] .ID
WHERE ((TableA.Dept) Like 'D1' Or (TableA.Dept) Like 'D2' Or (TableA.Dept) Like 'D3') 
GROUP BY TableA.Dept;

TableA contains ID and dept and lots of other details related to ID
TableB contains scrap details when was a ID scrapped.