combine two tables in a query

[Guest]

i have 2 queries that share the fields "who" "when" and "amount". One
reports the value of daily labour (time out - time in * hourly wage) for the
employee (who). While the other shows the amount paid on a particular date.
When I create a query that includes these 2 queries and links the "who" field
between them I get a result that shows 5664 records where are only 260 when
you add the record count from the 2 queries that I'm using here. I'd like to
have a query that would report the amount owed out at any given time.

 

[Guest]

Just as an addition to the note.

I can't help but wonder about what relates - what doesn't. I ran through my
data and regardless of the steps that I take - I get the wrong results.

How can I get the result that I'm looking for? Is running a query the wrong
path?

If there would be some link to some appropriate online trainging - I'd
appreciate it.

 

[Guest]

Wes,

When you get results like yours, it usually means there is an issue with the
way you are joining the two tables together. Can you give a small clip of
the two tables?

Example:

Table 1
EmpID EmpHours
1 40
2 40

Table 2

EmpID EmpPay
1 8.50
2 10.00

This will allow me to better see your problem. Thanks.

Wade

 

[Guest]

that is what i'm thinking. hopefully, you will be able to direct me in the
right area.

here is a sample

TABLE 1
EmpID Date Hours Pay (this is calculated)
1 6/1/07 5 $50
1 6/2/07 10 $100
1 6/3/07 7 $70

TABLE 2
EmpID Date Payment
1 5/31/07 -$200
1 6/3/07 -$30


The desired output would be as follows:

1 5/31/07 -$200
1 6/1/07 $50
1 6/2/07 $100
1 6/3/07 $70
1 6/3/07 -$30

The result showing an overpayment of $10

Does that help?

Wes

 

[Guest]

Thanks.

I've been stuck in the rut of creating my queries in the design view of the
system. I'd like to become more comfortable with the script based method.
Could you direct me to a training material source for this?

that is what i'm thinking. hopefully, you will be able to direct me in the
right area.

here is a sample

TABLE 1
EmpID Date Hours Pay (this is calculated)
1 6/1/07 5 $50
1 6/2/07 10 $100
1 6/3/07 7 $70

TABLE 2
EmpID Date Payment
1 5/31/07 -$200
1 6/3/07 -$30


The desired output would be as follows:

1 5/31/07 -$200
1 6/1/07 $50
1 6/2/07 $100
1 6/3/07 $70
1 6/3/07 -$30

The result showing an overpayment of $10

Does that help?

Wes
:

Wes,

Two Queries.

Query1:

SELECT T1.EmpID
,T1.[Date]
,<your calculation here> AS Payment
FROM [Table 1] AS T1
UNION ALL
SELECT T2.EmpID
,T2.[Date]
,T2.Payment
FROM [Table 2] AS T2

(UNION ALL retains all rows. If you have dups you wish to remove,
remove ALL.)

Query2:

SELECT Q1.EmpID
,SUM(Q1.Payment)
FROM Query1 AS Q1
GROUP BY Q1.EmpID


Sincerely,

Chris O.

 

[Chris2]

that is what i'm thinking. hopefully, you will be able to direct me in the
right area.

here is a sample

TABLE 1
EmpID Date Hours Pay (this is calculated)
1 6/1/07 5 $50
1 6/2/07 10 $100
1 6/3/07 7 $70

TABLE 2
EmpID Date Payment
1 5/31/07 -$200
1 6/3/07 -$30


The desired output would be as follows:

1 5/31/07 -$200
1 6/1/07 $50
1 6/2/07 $100
1 6/3/07 $70
1 6/3/07 -$30

The result showing an overpayment of $10

Does that help?

Wes
:

Wes,

Two Queries.

Query1:

SELECT T1.EmpID
,T1.[Date]
,<your calculation here> AS Payment
FROM [Table 1] AS T1
UNION ALL
SELECT T2.EmpID
,T2.[Date]
,T2.Payment
FROM [Table 2] AS T2

(UNION ALL retains all rows. If you have dups you wish to remove,
remove ALL.)

Query2:

SELECT Q1.EmpID
,SUM(Q1.Payment)
FROM Query1 AS Q1
GROUP BY Q1.EmpID


Sincerely,

Chris O.

 

[Chris2]

that is what i'm thinking. hopefully, you will be able to

direct me
in the

right area.

here is a sample

TABLE 1
EmpID Date Hours Pay (this is calculated)
1 6/1/07 5 $50
1 6/2/07 10 $100
1 6/3/07 7 $70

TABLE 2
EmpID Date Payment
1 5/31/07 -$200
1 6/3/07 -$30


The desired output would be as follows:

1 5/31/07 -$200
1 6/1/07 $50
1 6/2/07 $100
1 6/3/07 $70
1 6/3/07 -$30

The result showing an overpayment of $10

Does that help?

Wes
:

Wes,

Two Queries.

Query1:

SELECT T1.EmpID
,T1.[Date]
,<your calculation here> AS Payment
FROM [Table 1] AS T1
UNION ALL
SELECT T2.EmpID
,T2.[Date]
,T2.Payment
FROM [Table 2] AS T2

(UNION ALL retains all rows. If you have dups you wish to remove,
remove ALL.)

Query2:

SELECT Q1.EmpID
,SUM(Q1.Payment)
FROM Query1 AS Q1
GROUP BY Q1.EmpID


Sincerely,

Chris O.

Thanks.

I've been stuck in the rut of creating my queries in the design view of the
system. I'd like to become more comfortable with the script based method.
Could you direct me to a training material source for this?

:

Wes,

You are welcome.

If you are pre-MS Access 2007, then open MS Access Help, and switch to
the Contents tab. There should be a book/node for "Microsoft JET SQL
Reference". Its dry and dusty, but it's the core. You'd refer to it
for answers on specific questions, not for how to learn SQL in MS
Access (It should also be on your harddrive somewhere as
JETSQL40.chm)

A couple of good beginning references are:

SQL Queries for Mere Mortals by Michael J. Hernandez, John L. Viescas

Database Design for Mere Mortals by Michael J. Hernandez

SQL Tutorial:

http://www.w3schools.com/sql/default.asp

(Remember that there are some differences between MS Access and the
standard SQL taught here. If something you learn here doesn't work in
MS Access, refer to JETSQL40.chm for the final word.)


And some websites for MS Access:

http://www.mvps.org/access
http://allenbrowne.com/
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#Top
http://www.rogersaccesslibrary.com/


Sincerely,

Chris O.