Reporting dates from 2 tables

  • Thread starter Thread starter Vicki
  • Start date Start date
V

Vicki

Can one request the start date from a field in table one
and request the end date from a field in table two and get
results in a report?

OR is it possible to write a query that allows one to pull
a start and end date from 1 table and start and end date
from table 2 and display the results in a report?


Thank you
Vicki
 
Yes, both can be done, but the "how" depends on the way you've set up your
tables. I'll hazard a guess that you have something like this:
tblTable0
JobID
JobName
...
tblTable1
JobID (referencing tblTable0)
StartDate1
EndDateA
...
tblTable2
JobID (again referencing tblTable0)
StartDate2
EndDateB
...

For you first [or second] question you would create a report [or query], and
in the Properties window, type something like the following into "Record
Source" (on the Data tab) [for the query just type it into the SQL editor]:
SELECT * FROM tblTable1 INNER JOIN tblTable2 ON tblTable1.JobID =
tblTable2.JobID;
Note this assumes that your JobID is the corresponding field. If you're
using a different field to determine which records from tblTable1 belong
with which records from tblTable2, use that criterion in the "ON" statement.
Then you'll be able to pull StartDate1 and EndDateB (like your first
question--just choose those fields as the source for a bound text box) or
StartDate1-EndDateA and StartDate2-EndDateB (like your second question).
[If you're just looking to query for those and don't care about the rest,
replace "*" with "tblTable1.StartDate1, tblTable1.EndDateA,
tblTable2.StartDate2, tblTable2.EndDateB"]

If your table structure is very different and you can't figure this out,
please post your structure for better help.

-Stu
 
Thank you for your reply.

The first part worked wonderful!I just need to get the
[dateSubmitted] from table1 as a prompt for the start date
and [dateSubmitted] from table2 as a prompt for the end
date. I made the unbound text boxes on my report.But I
don't know how to prompt the user. I have never used the
SQL. Since the other worked can I add something similiar
in SQL?

WHERE ((([Task Table].[Date submitted]) Between [enter a
start date] And ([ Mod Table].[Date submitted ])AND
([Enter a end date]));
I am stumped. I don't know if I should be in the query
design or the report design.
Thank you!
-----Original Message-----
Yes, both can be done, but the "how" depends on the way you've set up your
tables. I'll hazard a guess that you have something like this:
tblTable0
JobID
JobName
...
tblTable1
JobID (referencing tblTable0)
StartDate1
EndDateA
...
tblTable2
JobID (again referencing tblTable0)
StartDate2
EndDateB
...

For you first [or second] question you would create a report [or query], and
in the Properties window, type something like the following into "Record
Source" (on the Data tab) [for the query just type it into the SQL editor]:
SELECT * FROM tblTable1 INNER JOIN tblTable2 ON tblTable1.JobID =
tblTable2.JobID;
Note this assumes that your JobID is the corresponding field. If you're
using a different field to determine which records from tblTable1 belong
with which records from tblTable2, use that criterion in the "ON" statement.
Then you'll be able to pull StartDate1 and EndDateB (like your first
question--just choose those fields as the source for a bound text box) or
StartDate1-EndDateA and StartDate2-EndDateB (like your second question).
[If you're just looking to query for those and don't care about the rest,
replace "*" with "tblTable1.StartDate1, tblTable1.EndDateA,
tblTable2.StartDate2, tblTable2.EndDateB"]

If your table structure is very different and you can't figure this out,
please post your structure for better help.

-Stu

Can one request the start date from a field in table one
and request the end date from a field in table two and get
results in a report?

OR is it possible to write a query that allows one to pull
a start and end date from 1 table and start and end date
from table 2 and display the results in a report?


Thank you
Vicki
Click to expand...


.
Click to expand...
 
I'm not quite clear on exactly what you are getting at, and please bear in
mind that I'm a novice myself. But it seems like you want a Parameter Query
for this next part.
The Parameters declaration will bring up a message box that prompts the user
for the two dates. If you want something prettier or with a choice of
selections, etc, I've heard that you can make a Form that will pop up, but I
don't know exactly how.

If it is indeed a parameter query you're after, then in the SQL view you can
just declare the parameters before the SELECT statement, viz:
PARAMETERS [Start Date] DateTime [End Date] DateTime
SELECT ... FROM ...(same as before if you say that worked)

Then you can base your report on the query, so that every time the user
wants to generate a report she is prompted for the start and end date. The
WHERE clause you typed below is a bit ambiguous: if you want both the Task
and the Mod Date Submitted between the same input dates, try this
WHERE ([Task Table].[Date Submitted] Between [Start Date] And [End Date])
AND ([Mod Table].[Date Submitted] Between [Start Date] And [End Date])
If you're trying to get something else, please explain.

Hope this helps,
Stu

Vicki said:
Thank you for your reply.

The first part worked wonderful!I just need to get the
[dateSubmitted] from table1 as a prompt for the start date
and [dateSubmitted] from table2 as a prompt for the end
date. I made the unbound text boxes on my report.But I
don't know how to prompt the user. I have never used the
SQL. Since the other worked can I add something similiar
in SQL?

WHERE ((([Task Table].[Date submitted]) Between [enter a
start date] And ([ Mod Table].[Date submitted ])AND
([Enter a end date]));
I am stumped. I don't know if I should be in the query
design or the report design.
Thank you!
-----Original Message-----
Yes, both can be done, but the "how" depends on the way you've set up your
tables. I'll hazard a guess that you have something like this:
tblTable0
JobID
JobName
...
tblTable1
JobID (referencing tblTable0)
StartDate1
EndDateA
...
tblTable2
JobID (again referencing tblTable0)
StartDate2
EndDateB
...

For you first [or second] question you would create a report [or query], and
in the Properties window, type something like the following into "Record
Source" (on the Data tab) [for the query just type it into the SQL editor]:
SELECT * FROM tblTable1 INNER JOIN tblTable2 ON tblTable1.JobID =
tblTable2.JobID;
Note this assumes that your JobID is the corresponding field. If you're
using a different field to determine which records from tblTable1 belong
with which records from tblTable2, use that criterion in the "ON" statement.
Then you'll be able to pull StartDate1 and EndDateB (like your first
question--just choose those fields as the source for a bound text box) or
StartDate1-EndDateA and StartDate2-EndDateB (like your second question).
[If you're just looking to query for those and don't care about the rest,
replace "*" with "tblTable1.StartDate1, tblTable1.EndDateA,
tblTable2.StartDate2, tblTable2.EndDateB"]

If your table structure is very different and you can't figure this out,
please post your structure for better help.

-Stu

Can one request the start date from a field in table one
and request the end date from a field in table two and get
results in a report?

OR is it possible to write a query that allows one to pull
a start and end date from 1 table and start and end date
from table 2 and display the results in a report?


Thank you
Vicki
Click to expand...


.
Click to expand...
Click to expand...
 
Thank you Stu!
I keep getting an error stating it is too complicated or
no results show.
The SQL works good with one Date column and prompts and
pull correct dates.

Thanks again.
-----Original Message-----
I'm not quite clear on exactly what you are getting at, and please bear in
mind that I'm a novice myself. But it seems like you want a Parameter Query
for this next part.
The Parameters declaration will bring up a message box that prompts the user
for the two dates. If you want something prettier or with a choice of
selections, etc, I've heard that you can make a Form that will pop up, but I
don't know exactly how.

If it is indeed a parameter query you're after, then in the SQL view you can
just declare the parameters before the SELECT statement, viz:
PARAMETERS [Start Date] DateTime [End Date] DateTime
SELECT ... FROM ...(same as before if you say that worked)

Then you can base your report on the query, so that every time the user
wants to generate a report she is prompted for the start and end date. The
WHERE clause you typed below is a bit ambiguous: if you want both the Task
and the Mod Date Submitted between the same input dates, try this
WHERE ([Task Table].[Date Submitted] Between [Start Date] And [End Date])
AND ([Mod Table].[Date Submitted] Between [Start Date] And [End Date])
If you're trying to get something else, please explain.

Hope this helps,
Stu

Thank you for your reply.

The first part worked wonderful!I just need to get the
[dateSubmitted] from table1 as a prompt for the start date
and [dateSubmitted] from table2 as a prompt for the end
date. I made the unbound text boxes on my report.But I
don't know how to prompt the user. I have never used the
SQL. Since the other worked can I add something similiar
in SQL?

WHERE ((([Task Table].[Date submitted]) Between [enter a
start date] And ([ Mod Table].[Date submitted ])AND
([Enter a end date]));
I am stumped. I don't know if I should be in the query
design or the report design.
Thank you!
-----Original Message-----
Yes, both can be done, but the "how" depends on the way you've set up your
tables. I'll hazard a guess that you have something
Click to expand...
like
this:
tblTable0
JobID
JobName
...
tblTable1
JobID (referencing tblTable0)
StartDate1
EndDateA
...
tblTable2
JobID (again referencing tblTable0)
StartDate2
EndDateB
...

For you first [or second] question you would create a report [or query], and
in the Properties window, type something like the following into "Record
Source" (on the Data tab) [for the query just type it into the SQL editor]:
SELECT * FROM tblTable1 INNER JOIN tblTable2 ON tblTable1.JobID =
tblTable2.JobID;
Note this assumes that your JobID is the corresponding field. If you're
using a different field to determine which records from tblTable1 belong
with which records from tblTable2, use that criterion
Click to expand...
in
the "ON" statement.
Then you'll be able to pull StartDate1 and EndDateB
Click to expand...
(like
your first
question--just choose those fields as the source for a bound text box) or
StartDate1-EndDateA and StartDate2-EndDateB (like your second question).
[If you're just looking to query for those and don't
Click to expand...
care
about the rest,
replace "*" with "tblTable1.StartDate1, tblTable1.EndDateA,
tblTable2.StartDate2, tblTable2.EndDateB"]

If your table structure is very different and you can't figure this out,
please post your structure for better help.

-Stu

Can one request the start date from a field in table one
and request the end date from a field in table two
Click to expand...
and
get
results in a report?

OR is it possible to write a query that allows one to pull
a start and end date from 1 table and start and end date
from table 2 and display the results in a report?


Thank you
Vicki


.
Click to expand...
Click to expand...


.
Click to expand...
 
Back
Top