Merge a table and query into new table?

[Shyguy]

I have a table with a sequence of dates, some dates are duplicated.
I also have a query that lists "appointments" from another table.

I need to create a new table, or report, that shows all the dates from
the table, and the "appointments" from the query. If a date in the
table is a duplicated date (say it's in the table twice), and there is
an "appointment" for the same date, then the result should show the
date once with no "appointment" and once with the "appointment".

I don't think I'm articulating this correctly so here is an example.

Table Data

1/2/05
1/3/05
1/4/05
1/4/05
1/5/05

Query (appointment) Data

1/3/05 Name Time
1/4/05 Name Time
1/5/05 Name Time

Final Table or report

1/2/05
1/3/05 Name Time
1/4/05 Name Time
1/4/05
1/5/05 Name Time

Thank you for any help

 

[Jeff Boyce]

Hmmm? Are you saying that the ONLY connecting between the two tables is the
date field?

When you ask Access to link by the date field in your example, Access will
find a link from the first "1/4/05" in table 1 to the only "1/4/05" in table
2 (these are the same date, right?). Then Access will find a link from the
second "1/4/05" (table 1) to the only "1/4/05" in table 2 (these are the
same date, right?!).

So how would Access (or you) know that the first linkage was valid, but the
second (?3rd, 4th, ...?) was not?

I suppose one approach might be to first get single date linkages (one row
per date from table1). Then find all dates in table 1 that have more than
one row for a date. Finally, build a UNION query of the results of the
first query and the results of the second query (with suitable "blanks")
would seem to fit your requirement.

 

[Shyguy]

Hmmm? Are you saying that the ONLY connecting between the two tables is the
date field?

When you ask Access to link by the date field in your example, Access will
find a link from the first "1/4/05" in table 1 to the only "1/4/05" in table
2 (these are the same date, right?). Then Access will find a link from the
second "1/4/05" (table 1) to the only "1/4/05" in table 2 (these are the
same date, right?!).

So how would Access (or you) know that the first linkage was valid, but the
second (?3rd, 4th, ...?) was not?

I suppose one approach might be to first get single date linkages (one row
per date from table1). Then find all dates in table 1 that have more than
one row for a date. Finally, build a UNION query of the results of the
first query and the results of the second query (with suitable "blanks")
would seem to fit your requirement.

Thank you for the reply. I don't really understand what you mean up
the "build a Union query" I did try creating a union query taking all
records from the Dates Table and only those from the "appointments
Query and it almost worked. The only problem is that for the Dates
that are in the Dates Table twice and have only one "appointment" in
the "appointments" Query, the final resulting table only shows those
dates once. What I need to do is print a list of all available
appointments, and those already created in one chronological list.

 

[Gary Walter]

I have a table with a sequence of dates, some dates are duplicated.
I also have a query that lists "appointments" from another table.

I need to create a new table, or report, that shows all the dates from
the table, and the "appointments" from the query. If a date in the
table is a duplicated date (say it's in the table twice), and there is
an "appointment" for the same date, then the result should show the
date once with no "appointment" and once with the "appointment".

I don't think I'm articulating this correctly so here is an example.

Table Data

1/2/05
1/3/05
1/4/05
1/4/05
1/5/05

Query (appointment) Data

1/3/05 Name Time
1/4/05 Name Time
1/5/05 Name Time

Final Table or report

1/2/05
1/3/05 Name Time
1/4/05 Name Time
1/4/05
1/5/05 Name Time

Hi Shy,

PMFBI

I *believe* this is what Jeff meant

make a query that gets distinct dates from TableData

qryDistinctDates:

SELECT
DateField
FROM
TableData
GROUP BY DateField;

"LEFT JOIN"ing this query to apptData
would get first part for Union query

SELECT
q.DateField,
t1.[Name],
t1.[Time]
FROM
qryDistinctDates As q
LEFT JOIN
apptData As t1
ON
q.DateField = t1.DateField;

this will give you

1/2/05
1/3/05 Name Time
1/4/05 Name Time
1/5/05 Name Time


second part of Union query
would be where count of
DateField in TableData
was 2
(if apptData.[Time] is type Date/Time)


SELECT
t.DateField,
IIF(True,Null,"") As [Name],
IIF(True,Null,#00:00#) As [Time]
FROM
TableData As t
WHERE
COUNT(t.DateField)>1
GROUP BY
t.DateField,
IIF(True,Null,"") As [Name],
IIF(True,Null,#00:00#) As [Time];

this should give you

1/4/05 <null> <null>


So...your Union query would look like:
(if apptData.[Time] is type Date/Time)


SELECT
q.DateField,
t1.[Name],
t1.[Time]
FROM
qryDistinctDates As q
LEFT JOIN
apptData As t1
ON
q.DateField = t1.DateField
UNION ALL
SELECT
t.DateField,
IIF(True,Null,"") As [Name],
IIF(True,Null,#00:00#) As [Time]
FROM
TableData As t
WHERE
COUNT(t.DateField)>1
GROUP BY
t.DateField,
IIF(True,Null,"") As [Name],
IIF(True,Null,#00:00#) As [Time]
ORDER BY
[DateField],
[Name];

 

[Jeff Boyce]

As Gary points out, a "union" query has a specific meaning in Access --
check Access HELP for more information on how they are constructed (but
Gary's response points the way nicely).

I'll differ with Gary on only one point -- I took your example to be just
that. Unless you are absolutely positively certain that there will NEVER be
more than two rows with the same date in Table1, you need to compare using
">=2", rather than "=2".

Good luck

Jeff Boyce
<Access MVP>

 

[Shyguy]

I have a table with a sequence of dates, some dates are duplicated.
I also have a query that lists "appointments" from another table.

I need to create a new table, or report, that shows all the dates from
the table, and the "appointments" from the query. If a date in the
table is a duplicated date (say it's in the table twice), and there is
an "appointment" for the same date, then the result should show the
date once with no "appointment" and once with the "appointment".

I don't think I'm articulating this correctly so here is an example.

Table Data

1/2/05
1/3/05
1/4/05
1/4/05
1/5/05

Query (appointment) Data

1/3/05 Name Time
1/4/05 Name Time
1/5/05 Name Time

Final Table or report

1/2/05
1/3/05 Name Time
1/4/05 Name Time
1/4/05
1/5/05 Name Time

Hi Shy,

PMFBI

I *believe* this is what Jeff meant

make a query that gets distinct dates from TableData

qryDistinctDates:

SELECT
DateField
FROM
TableData
GROUP BY DateField;

"LEFT JOIN"ing this query to apptData
would get first part for Union query

SELECT
q.DateField,
t1.[Name],
t1.[Time]
FROM
qryDistinctDates As q
LEFT JOIN
apptData As t1
ON
q.DateField = t1.DateField;

this will give you

1/2/05
1/3/05 Name Time
1/4/05 Name Time
1/5/05 Name Time


second part of Union query
would be where count of
DateField in TableData
was 2
(if apptData.[Time] is type Date/Time)


SELECT
t.DateField,
IIF(True,Null,"") As [Name],
IIF(True,Null,#00:00#) As [Time]
FROM
TableData As t
WHERE
COUNT(t.DateField)>1
GROUP BY
t.DateField,
IIF(True,Null,"") As [Name],
IIF(True,Null,#00:00#) As [Time];

this should give you

1/4/05 <null> <null>


So...your Union query would look like:
(if apptData.[Time] is type Date/Time)


SELECT
q.DateField,
t1.[Name],
t1.[Time]
FROM
qryDistinctDates As q
LEFT JOIN
apptData As t1
ON
q.DateField = t1.DateField
UNION ALL
SELECT
t.DateField,
IIF(True,Null,"") As [Name],
IIF(True,Null,#00:00#) As [Time]
FROM
TableData As t
WHERE
COUNT(t.DateField)>1
GROUP BY
t.DateField,
IIF(True,Null,"") As [Name],
IIF(True,Null,#00:00#) As [Time]
ORDER BY
[DateField],
[Name];

Thank you very much for the response and code, but I'm afraid you left
me in the dust. I don't know any SQL and I don't know what to
substitute and what to leave alone. I got through the first part but
you lost me on "the second part of the union query". The t1.[Name]
etc. made my head hurt.

Thanks again for the effort and I'm sorry to have wasted your time.