Here's a tough one.....

[atadbitslow]

Using the data below, I need to generate a report--

Studio A
John Smith Residency dates 1/4/04 - 1/31/04
Jane Doe Residency dates 2/14/04 - 3/05/04

Studio B
Bill Jones Residency dates 1/13/04 - 2/1/04
Susy Brown Residency dates 2/24/04 - 3/31/04


Report needed:
Depart Arrive
John Smith 1/31/04 Studio A 2/14/04 Jane Doe (13 days)
Bill Jones 2/1/04 Studio B 2/24/04 Susy Brown (22 days)

figure in parentheses not absolutely necessary.......

Thanks for any help with this!

 

[dhookom]

Your sample data doesn't resemble most datasheet views that I have seen. For all we know, you may have 7 records with a single large text box with the first record of:
"Studio A"
and a second record of:
"John Smith.../04"
and a blank record in the middle.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[atadbitslow]

Not sure what you meant.....

I didn't list the data in datasheet view....but here is how the data is saved

Table A Table B
name studio
res begin date
res end date

A query could pull the data from the two tables
name studio res begin res end

My problem is getting it in the report format I listed above where it needs to be sorted by studio, and list the res end date for the first person and the res begin date for the next person....the need is to find out the date range (or how many days) between studio occupants......

 

[dhookom]

atadbitslow,
1) I don't see how the two tables are related
2) a field named name is only going to cause name issues since every object has a name project


Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[atadbitslow]

Table A and Table B are related by an ID number (one-to-many relationship)--I just didn't list it trying to keep it simple....my mistake--

 

[dhookom]

If you expect help (at least from me) you should type in a few actual records. This shouldn't be too difficult. Your withholding information and not making an effort makes this much more difficult.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[atadbitslow]

I don't see how I'm withholding valuable information, and I am making an effort....perhaps you should be a little nicer if you want to help.........

Table A
ID Name
100 John Smith
101 Jane Doe
102 Bill Jones
103 Susy Brown

Table B
ID Studio Beg date End date
100 A 1/4/04 1/31/04
101 A 2/14/04 3/05/04
102 B 1/13/04 2/1/04
103 B 2/24/04 3/31/04

Report needed:
Depart Arrive
John Smith 1/31/04 Studio A 2/14/04 Jane Doe (13 days)
Bill Jones 2/1/04 Studio B 2/24/04 Susy Brown (22 days)


Thank you-

 

[teddyatjorundotnet]

Will you ever have more then two entries for the same studio? If not I can see quick fix.

Something to the effect of:

SELECT [id], [name], MIN(end date), MAX(beg date), DATEDIFF(day, edate, sdate)
FROM tblA INNER JOIN tblB ON tblA.id = tblB.id

If you need it dynamic, then I'd have to put considerably more thought into this...

 

[dhookom]

You started out with "Here's a tough one..." and then it took you 4 posts to provide us (as least me) with the specific information required provide a solution.

I would first add a primary key autonumber to TableB. Name it RsvID. Then create a query with a subquery and save it as
"qselConsecutiveRsv":
[blue]
SELECT TableB.*, (Select Top 1 RsvID FROM TableB B WHERE B.Studio = TableB.Studio AND B.BegDate>=TableB.EndDate ORDER BY B.BegDate) AS NextRsvID
FROM TableB;
[/blue]
Then create another query based on TableB, qselConsecutiveRsv, and 2 instances of TableA:
[blue]
SELECT TableA_1.FullName AS FirstName, qselConsecutiveRsv.Studio, qselConsecutiveRsv.EndDate, TableB.BegDate, TableA.FullName AS NextName, DateDiff("d",[qselConsecutiveRsv]![EndDate],[TableB].[BegDate])-1 AS DaysBetween
FROM TableA AS TableA_1 INNER JOIN ((qselConsecutiveRsv INNER JOIN TableB ON qselConsecutiveRsv.NextRsvID = TableB.RsvID) INNER JOIN TableA ON TableB.ID = TableA.ID) ON TableA_1.ID = qselConsecutiveRsv.ID;
[/blue]

This will provide the recordset you need for your report.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[atadbitslow]

I'm stuck...query 2 asks for "tableA_1.fullname" and "tableA.fullname" and "qselconsecutiversv.rsv.id"...

Query qselconsecutiversv:
SELECT tableB.*, (select top 1 rsvid FROM tableB B where B.studio=tableB.studio and B.res_begin>=tableB.res_end order by B.res_begin) AS nextrsvid
FROM tableB;


Query 2:
SELECT tableA_1.fullname AS name_last, qselconsecutiversv.studio, qselconsecutiversv.RES_END, tableB.RES_BEGIN, tableA.fullname AS nextname, DateDiff("d",[qselconsecutiversv]![res_end],[TableB].[res_begin])-1 AS daysbetween
FROM tableA AS tableA_1 INNER JOIN ((qselconsecutiversv INNER JOIN tableB ON qselconsecutiversv.nextrsvid = tableB.rsvid) INNER JOIN tableA ON tableB.ID = tableA.ID) ON tableA_1.ID = qselconsecutiversv.rsv.id;

Thanks-

 

[dhookom]

I was expecting you to change my field names to your exact field names. If you don't have a field named "fullname" then you will need to substitute your field name. I don't have a copy of your table(s) so I am making some assumptions.

BTW: I have a real aversion to typing name as a field name since name is the name of a property of every object. Quite often forum members will use "name" when their field name is something else.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[atadbitslow]

Thanks for your help, but this doesn't work......

SELECT tableA_1.NAME_LAST AS name_last, qselconsecutiversv.studio, qselconsecutiversv.RES_END, tableA.NAME_LAST AS nextname, DateDiff("d",[qselconsecutiversv]![res_end],[TableB].[res_begin])-1 AS daysbetween, qselconsecutiversv.RES_BEGIN
FROM tableA AS tableA_1, (qselconsecutiversv INNER JOIN tableB ON qselconsecutiversv.nextrsvid = tableB.rsvid) INNER JOIN tableA ON tableB.ID = tableA.ID;

This is the output:

name_last studio RES_END nextname daysbetween RES_BEGIN
Jennings CHAPM 1/24/2004 Min 8 1/5/2004
Meyer CHAPM 1/24/2004 Min 8 1/5/2004
Min CHAPM 1/24/2004 Min 8 1/5/2004
Jennings CHAPM 1/24/2004 Min 8 1/5/2004
Jennings CHAPM 1/24/2004 Min 8 1/5/2004
Jennings BARNA 3/1/2004 Meyer 3 2/2/2004
Meyer BARNA 3/1/2004 Meyer 3 2/2/2004
Min BARNA 3/1/2004 Meyer 3 2/2/2004
Jennings BARNA 3/1/2004 Meyer 3 2/2/2004
Jennings BARNA 3/1/2004 Meyer 3 2/2/2004

Actual Data:
Name_last Studio Res_begin Res_end
Jennings BARNA 2/2/04 3/1/04
Meyer BARNA 3/5/04 3/26/04
Min CHAPM 2/2/04 3/29/04

It's close, but not quite......

Thanks-

 

[dhookom]

The last line in two SQLs ago contained:
"tableA_1.ID = qselconsecutiversv.rsv.id;"
which should have been
"tableA_1.ID = qselconsecutiversv.id;"


I'm not sure how you got your last SQL from my original two queries.
Can you see how you create some confusion when you claim your fields are NAME, ID, Studio, Beg date, and End date when they are apparently NAME_LAST, ID, Studio, Res_Begin, and Res_End?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[atadbitslow]

Thanks, yes I had fixed that-- tableA_1.ID = qselconsecutiversv.id

But this still doesn't work--

SELECT tableA_1.NAME_LAST AS name_last, qselconsecutiversv.studio, qselconsecutiversv.RES_END, tableA.NAME_LAST AS nextname, DateDiff("d",[qselconsecutiversv]![res_end],[TableB].[res_begin])-1 AS daysbetween, qselconsecutiversv.RES_BEGIN
FROM tableA AS tableA_1, (qselconsecutiversv INNER JOIN tableB ON qselconsecutiversv.nextrsvid = tableB.rsvid) INNER JOIN tableA ON tableB.ID = tableA.ID

???

 

[dhookom]

Have you opened qselConsecutiveRsv to see if the query is returning what you expect?

What do you mean by "still doesn't work"? That doesn't give us much to work with.

There are still differences in my suggested sql and yours. I have TableB.BegDate and you have qselconsecutiversv.RES_BEGIN.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[atadbitslow]

Thanks for all your help--it works perfectly now that I fixed my mistakes.....