Double Value Shoes in query

[nhkhurum]

Hello,
I have Table item code

ItemId (Pri Key)
ItemDesc
Unit

2nd table

Employedata
EmployeeID (Pri Key)
EmployeeName
3rd table
Issue Table

EmployeeID
ItemCode
QtyIssue
4thTable
EmployeeID
Itemcode
QtyRec



When i enter data and run query double value sho in rec qty actualy in table only one record enter

EmloyeeID	ItemCode	Isse_QtyIsse	Rec_QtyIsse
1	1	8	7
1	1	7	7

plz resove this issue
noor

[June7]

Post the query SQL statement or provide db for analysis.

[Dal Jeanis]

From your layout, I'd say your database design isn't going to work.

You are issuing items to an employee and recording that in Table 3.

When you receive the items bacl, you are recording that in Table 4.

There are no dates and no unique identifiers on the records, so you can only either sum up what the employee gets and returns, or report all the individual transactions without knowing which one occurred when. You also have no way to tell, if the employee checked out several different sets of things, and returned several different sets of things, which set returned was the one that was short.

Also, whenever you put together your queries, if the same employee has checked out the same 10 items three times and returned them all three times, unless you are careful in your query design, instead of 30 checkouts and 30 returns, you may get 90 checkouts and 90 returns. This is probably what you are experiencing now.

 tblItems
   ItemId       (Pri Key)
   ItemDesc     Text
   Unit         (number?)

tblEmployees
   EmployeeID   (Pri Key)
   EmployeeName Text

tblIssues
   IssueID      (AutoKey)
   IssueDate    Date 
   EmployeeID   FK to tblEmployees
   ItemCode     FK to tblItems
   QtyIssued    Number

tblReturns
   ReturnID     (AutoKey)
   ReturnDate   Date 
   EmployeeID   FK to tblEmployees
   Itemcode     FK to tblItems
   QtyRecd      Number

Given the above layout, then by summing the amounts issued, then "LEFT JOINing" to the sum of amounts returned, you will get the correct answers. The query would look like this:

SELECT 
  T1.EmployeeID,
  T1.ItemCode,
  T1.QtyIssued,
  NZ(T2.QtyRecd) As QtyRecd,
  T1.QtyIssued - NZ(
  T2.QtyRecd,
FROM
    (   (SELECT 
           T1.EmployeeID,
           T1.Itemcode,
           Sum(T1.QtyIssued) As QtyIssued
         FROM tblIssues AS T1
         GROUP BY 
           T1.EmployeeID,
           T1.Itemcode
         ) 
    LEFT JOIN
        (SELECT 
            T2.EmployeeID,
            T2.Itemcode,
            Sum(T2.QtyRecd) As QtyRecd
         FROM tblReturns AS T2
         GROUP BY 
            T2.EmployeeID,
            T2.Itemcode 
         ) 
    ON T1.EmployeeID = T2.EmployeeID
    AND T1.Itemcode = T2.Itemcode 
    );

The Dates and unique record IDs I added for the issued and returned tables are to allow different kinds of analysis later. For example, if a person has not returned all items they checked out, you will be able to determine on what return date the amount returned was not all that was checked out at that time.

This table and query design assumes that no employee will ever return an item they have not checked out. That's a procedural situation that you may want to consider. If it is possible that an employee may check in an item that another employee had checked out, then it would be wise to combine tables 3 and 4 and include a transaction type to determine what happened (checkin, checkout). This way, the query can become far simpler.

[June7]

If this is to track items checked out and in by employees, might explore the Lending Library database template.

[nhkhurum]

I have enclosed file. kindly fixed problem for me.

From your layout, I'd say your database design isn't going to work.

You are issuing items to an employee and recording that in Table 3.

When you receive the items bacl, you are recording that in Table 4.

There are no dates and no unique identifiers on the records, so you can only either sum up what the employee gets and returns, or report all the individual transactions without knowing which one occurred when. You also have no way to tell, if the employee checked out several different sets of things, and returned several different sets of things, which set returned was the one that was short.

Also, whenever you put together your queries, if the same employee has checked out the same 10 items three times and returned them all three times, unless you are careful in your query design, instead of 30 checkouts and 30 returns, you may get 90 checkouts and 90 returns. This is probably what you are experiencing now.

 tblItems
   ItemId       (Pri Key)
   ItemDesc     Text
   Unit         (number?)

tblEmployees
   EmployeeID   (Pri Key)
   EmployeeName Text

tblIssues
   IssueID      (AutoKey)
   IssueDate    Date 
   EmployeeID   FK to tblEmployees
   ItemCode     FK to tblItems
   QtyIssued    Number

tblReturns
   ReturnID     (AutoKey)
   ReturnDate   Date 
   EmployeeID   FK to tblEmployees
   Itemcode     FK to tblItems
   QtyRecd      Number

Given the above layout, then by summing the amounts issued, then "LEFT JOINing" to the sum of amounts returned, you will get the correct answers. The query would look like this:

SELECT 
  T1.EmployeeID,
  T1.ItemCode,
  T1.QtyIssued,
  NZ(T2.QtyRecd) As QtyRecd,
  T1.QtyIssued - NZ(
  T2.QtyRecd,
FROM
    (   (SELECT 
           T1.EmployeeID,
           T1.Itemcode,
           Sum(T1.QtyIssued) As QtyIssued
         FROM tblIssues AS T1
         GROUP BY 
           T1.EmployeeID,
           T1.Itemcode
         ) 
    LEFT JOIN
        (SELECT 
            T2.EmployeeID,
            T2.Itemcode,
            Sum(T2.QtyRecd) As QtyRecd
         FROM tblReturns AS T2
         GROUP BY 
            T2.EmployeeID,
            T2.Itemcode 
         ) 
    ON T1.EmployeeID = T2.EmployeeID
    AND T1.Itemcode = T2.Itemcode 
    );

The Dates and unique record IDs I added for the issued and returned tables are to allow different kinds of analysis later. For example, if a person has not returned all items they checked out, you will be able to determine on what return date the amount returned was not all that was checked out at that time.

This table and query design assumes that no employee will ever return an item they have not checked out. That's a procedural situation that you may want to consider. If it is possible that an employee may check in an item that another employee had checked out, then it would be wise to combine tables 3 and 4 and include a transaction type to determine what happened (checkin, checkout). This way, the query can become far simpler.

[nhkhurum]

Post the query SQL statement or provide db for analysis.

EmployeeID	EmployeeName	Craft	Area	ContactNo
1	noo

ItemID	ItemDescription	Unit	Remarks
1	dda

EmployeeID	Itemcode	QtyIssued	IssuedDate	Condition	Remarks
1	1	4

EmployeeID	Itemcode	QtyRec	RecDate	Condition	Remarks
1	1	4

plz send me after rsolving the issue

[orange]

Please describe WHAT you are trying to do in a few lines. Tells us what your tables represent.
Use the sample below as a guide.
This is the sort of info that would be helpful to readers, and will clarify things for you as well.

ZYX Laboratories requires an employee tracking database. They want to track information about employees, the employee's job history, and their certifications. Employee information includes first name, middle initial, last name, social security number, address, city, state, zip, home phone, cell phone, email address. Job history would include job title, job description, pay grade, pay range, salary, and date of promotion. For certifications, they want certification type and date achieved.
An employee can have multiple jobs over time, (ie, Analyst, Sr. Analyst, QA Administrator). Employees can also earn certifications necessary for their job.

From RogersAccessLIbrary

[nhkhurum]

asiscally i want to maintain personal inventory.

ItemCode table describe item of inventory
Employeedata table describe employee data

Recv table describle item received back from employee that he had taken from ware house
Issue table describe item issued to employee for his use.


When i try to run query. It shows same figure twice in rec table even in table only one record entered

[June7]

There are no queries in the posted database. Post the SQL statement of the query you attempted.

[nhkhurum]

Originally Posted by nhkhurum

EmployeeID	Itemcode	QtyIssued	IssuedDate	Condition	Remarks
1	1	5	11/15/2013

ItemID	ItemDescription	Unit
1	pen

EmployeeID	Itemcode	QtyRec	RecDate	Condition	Remarks
1	1	5	11/16/2013
1	1	4	11/17/2013

EmployeeID	EmployeeName	Craft	Area	ContactNo
1	noor	fab	admin

EmployeeID	Itemcode	QtyRec	RecDate	QtyIssued	IssuedDate
1	1	5	11/16/2013	5	11/15/2013
1	1	4	11/17/2013	5	11/15/2013

You can see Qty issued showing double transaction even we have entered one record in issue table,


i WANT IF EMPLOYEE 1 GET ITEM 1 (ISSUED) TWICE BOTHER TRANSACTION SHOW BUT IF HE RETURN ITEM 1 IT REFLECT AGAINST ITS CORRESPONDING DATE

[June7]

You still haven't posted the query SQL statement.

However, I expect the 'duplicate' is because you are joining the Issue and Rec tables. Rec table has two records that the Issue record will join with, hence the result of two records in query.

If you want to show the net difference between total issued and total received, must first do aggregate queries on each table. If you want to know the difference for each item for each employee, that gets a little complicated.

query EmpItems
SELECT EmployeeData.EmployeeID, Itemcode.ItemID
FROM EmployeeData, Itemcode;

query RecTotals
SELECT Rec.EmployeeID, Rec.Itemcode, Sum(Rec.QtyRec) AS SumOfQtyRec
FROM Rec
GROUP BY Rec.EmployeeID, Rec.Itemcode;

query IssTotals
SELECT Issuance.EmployeeID, Issuance.Itemcode, Sum(Issuance.QtyIssued) AS SumOfQtyIssued
FROM Issuance
GROUP BY Issuance.EmployeeID, Issuance.Itemcode;

query BalanceEmpItem
SELECT EmpItem.EmployeeID, EmpItem.ItemID, IssTotals.SumOfQtyIssued, RecTotals.SumOfQtyRec, Nz([SumOfQtyIssued],0)-Nz([SumOfQtyRec],0) AS Bal
FROM RecTotals RIGHT JOIN (IssTotals RIGHT JOIN EmpItem ON (IssTotals.Itemcode = EmpItem.ItemID) AND (IssTotals.EmployeeID = EmpItem.EmployeeID)) ON (RecTotals.Itemcode = EmpItem.ItemID) AND (RecTotals.EmployeeID = EmpItem.EmployeeID);

[nhkhurum]

can u plz apply this in database and send it to me

[June7]

I prefer not to. You can copy/paste those SQL statements into SQL view of query builder. The result will be 4 query objects in the navigation pane. That will be a better learning process.

[nhkhurum]

I have done but i want to see dat

Can i see data date wise issue and receive of specific employe against specific item

[June7]

Your posted db doesn't have data in it. So enter some records in the tables then open queries.

You might take a look at the Lending Library database template. Maybe it could be used for your tool check out/in db.