Multi-Table Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have 5 tables that students will enter data on in a "game" I designed. There is a button that will randomly open forms for the students to work on. Therefore I might have 2 entries from one form and none from another

What I need is a query that will pick each record from those tables that was entered today, each record will have a date field, AND add up the points received on each record. Points are stored in a field called PointsEarned. Same on every table

Tables are: tblAddress, tblDate, tblMonth, tblWeek, tblPhon

I cannot figure out how and I've worked on this on and off for two days. I just can't see how to do it in my head. Help would be greatly appreciated

Thanks as Always
RIP
 
I have 5 tables that students will enter data on in a "game" I designed. There is a button that will randomly open forms for the students to work on. Therefore I might have 2 entries from one form and none from another.

What I need is a query that will pick each record from those tables that was entered today, each record will have a date field, AND add up the points received on each record. Points are stored in a field called PointsEarned. Same on every table.

Tables are: tblAddress, tblDate, tblMonth, tblWeek, tblPhone

I cannot figure out how and I've worked on this on and off for two days. I just can't see how to do it in my head. Help would be greatly appreciated.
Click to expand...

A very basic principle is that a given type of data should have ONE
place where it should be stored. If you have "points" stored in all
five tables you're violating this principle! How are these tables
structured?

If this is really what you want to do you can use a UNION query. See
the online help for UNION; you'll need to go to the SQL window to
create it, it'll be something like

SELECT PointsEarned FROM tblAddress
WHERE tblAddress.datefield = Date()
UNION ALL
SELECT PointsEarned FROM tblDate
WHERE tblDate.datefield = Date()
UNION ALL
SELECT PointsEarned FROM tblMonth
WHERE tblMonth.datefield = Date()
UNION ALL
< etc etc >

You could then base a Totals query on this Union query.
 
Man, and I thought I was designing it right too. <sigh

Each table has a specific series of fields to store data

tblAddress stores the address for the student to type in, the studentid, a date field, and a PointEarned field. The points earned field is a random number between 2 and 10 and is created in the field's default value using this formula:
=Int((9*Rnd())+2

Is there a better way to store the points earned

tblPhone stores phone numbers in the same way, tblweek stores the days of the week in the same way. Each table had a points earned for that specific record.

I really appreciate the help

RIP
 
tblAddress stores the address for the student to type in, the studentid, a date field, and a PointEarned field. The points earned field is a random number between 2 and 10 and is created in the field's default value using this formula:
=Int((9*Rnd())+2)

Is there a better way to store the points earned?

tblPhone stores phone numbers in the same way, tblweek stores the days of the week in the same way. Each table had a points earned for that specific record.
Click to expand...

I guess I'm REALLY confused then.

What's the idea? that the student earns a random number of points for
having an address, another random number for having a telephone? Are
the points attributes of the address, or of the student? How are you
using the points?

The UNION query will let you add up the address-points and the
phone-points and all the other points, if that's what you want!
 
Back
Top