Difficult problem

  • Thread starter Thread starter Igor
  • Start date Start date
I

Igor

Hello,

I need to generate a report with the following fields:

Document Name, Document Type, Document Number, Modification Number, Date
Sent, Reply Received, Reply Received Date.

For this I have two tables:

The Correspondence Table has all the information for all the documents that
have been already sent. This table contains the fields Document Number,
Modification Number, Date Sent, Reply Received, Reply Received Date.

(Note: A document with the same Document Number will be sent several times,
depending on the number of corrections made to it. This means that for the
same Document Number there will be a different Modification Number, Sent
Date, Reply Received and Reply Received Date. And each of these deliveries
has to have it's own row.)

The Index Table has the complete list of all the documents that will be sent
to a client over a time period. These documents will not be sent at the same
time or in a specific order. This table just has three fields, Document Name,
Document Type and Document Number.

(!)Problem: I need that the report shows all the documents, including the
ones that have not been sent.

Example: Document 1 has been sent two times, Document 5 has been sent 3
times and Document 7 has been sent 1 time. Documents 2, 3, 4 and 6 have not
been sent. The report has to get the information for Documents 1, 5 and 7
from the Correspondence Table and the information for Documents 2, 3, 4 and 6
from the Index Table.

(!!)Desired result: The Report should generate 10 rows. 6 rows (for
Documents 1, 5 and 7) should have values in all the report fields. And 4 rows
(for Documents 2, 3, 4 and 6) should have values only in the fields Document
Name, Document Type, Document Number (the fields that are in the Table Index).

I would greatly appreciate any help!
 
Try this ---
SELECT [Index Table].[Document Name], [Index Table].[Document Type], [Index
Table].[Document Number], [Correspondence Table].[Modification Number],
[Correspondence Table].[Date Sent], [Correspondence Table].[Reply Received],
[Correspondence Table].[Reply Received Date]
FROM [Index Table] LEFT JOIN [Correspondence Table] ON [Index
Table].[Document Number] = [Correspondence Table].[Document Number];
 
Thank you for the help, Karl.

One question: Where should I write this (what you told me)? Maybe in the
Field in the Query with the expression builder?

(Sorry, I'm just starting with Access)

Thanks again!

--

igor


KARL DEWEY said:
Try this ---
SELECT [Index Table].[Document Name], [Index Table].[Document Type], [Index
Table].[Document Number], [Correspondence Table].[Modification Number],
[Correspondence Table].[Date Sent], [Correspondence Table].[Reply Received],
[Correspondence Table].[Reply Received Date]
FROM [Index Table] LEFT JOIN [Correspondence Table] ON [Index
Table].[Document Number] = [Correspondence Table].[Document Number];


--
KARL DEWEY
Build a little - Test a little


Igor said:
Hello,

I need to generate a report with the following fields:

Document Name, Document Type, Document Number, Modification Number, Date
Sent, Reply Received, Reply Received Date.

For this I have two tables:

The Correspondence Table has all the information for all the documents that
have been already sent. This table contains the fields Document Number,
Modification Number, Date Sent, Reply Received, Reply Received Date.

(Note: A document with the same Document Number will be sent several times,
depending on the number of corrections made to it. This means that for the
same Document Number there will be a different Modification Number, Sent
Date, Reply Received and Reply Received Date. And each of these deliveries
has to have it's own row.)

The Index Table has the complete list of all the documents that will be sent
to a client over a time period. These documents will not be sent at the same
time or in a specific order. This table just has three fields, Document Name,
Document Type and Document Number.

(!)Problem: I need that the report shows all the documents, including the
ones that have not been sent.

Example: Document 1 has been sent two times, Document 5 has been sent 3
times and Document 7 has been sent 1 time. Documents 2, 3, 4 and 6 have not
been sent. The report has to get the information for Documents 1, 5 and 7
from the Correspondence Table and the information for Documents 2, 3, 4 and 6
from the Index Table.

(!!)Desired result: The Report should generate 10 rows. 6 rows (for
Documents 1, 5 and 7) should have values in all the report fields. And 4 rows
(for Documents 2, 3, 4 and 6) should have values only in the fields Document
Name, Document Type, Document Number (the fields that are in the Table Index).

I would greatly appreciate any help!
Click to expand...
Click to expand...
 
Correct any table and field names. Create a new query in design view, do not
select a table, click on VIEW - SQL View, paste in the new window.

--
KARL DEWEY
Build a little - Test a little


Igor said:
Thank you for the help, Karl.

One question: Where should I write this (what you told me)? Maybe in the
Field in the Query with the expression builder?

(Sorry, I'm just starting with Access)

Thanks again!

--

igor


KARL DEWEY said:
Try this ---
SELECT [Index Table].[Document Name], [Index Table].[Document Type], [Index
Table].[Document Number], [Correspondence Table].[Modification Number],
[Correspondence Table].[Date Sent], [Correspondence Table].[Reply Received],
[Correspondence Table].[Reply Received Date]
FROM [Index Table] LEFT JOIN [Correspondence Table] ON [Index
Table].[Document Number] = [Correspondence Table].[Document Number];


--
KARL DEWEY
Build a little - Test a little


Igor said:
Hello,

I need to generate a report with the following fields:

Document Name, Document Type, Document Number, Modification Number, Date
Sent, Reply Received, Reply Received Date.

For this I have two tables:

The Correspondence Table has all the information for all the documents that
have been already sent. This table contains the fields Document Number,
Modification Number, Date Sent, Reply Received, Reply Received Date.

(Note: A document with the same Document Number will be sent several times,
depending on the number of corrections made to it. This means that for the
same Document Number there will be a different Modification Number, Sent
Date, Reply Received and Reply Received Date. And each of these deliveries
has to have it's own row.)

The Index Table has the complete list of all the documents that will be sent
to a client over a time period. These documents will not be sent at the same
time or in a specific order. This table just has three fields, Document Name,
Document Type and Document Number.

(!)Problem: I need that the report shows all the documents, including the
ones that have not been sent.

Example: Document 1 has been sent two times, Document 5 has been sent 3
times and Document 7 has been sent 1 time. Documents 2, 3, 4 and 6 have not
been sent. The report has to get the information for Documents 1, 5 and 7
from the Correspondence Table and the information for Documents 2, 3, 4 and 6
from the Index Table.

(!!)Desired result: The Report should generate 10 rows. 6 rows (for
Documents 1, 5 and 7) should have values in all the report fields. And 4 rows
(for Documents 2, 3, 4 and 6) should have values only in the fields Document
Name, Document Type, Document Number (the fields that are in the Table Index).

I would greatly appreciate any help!
Click to expand...
Click to expand...
Click to expand...
 
It works!

Karl, two last questions:

1) In this report I need to include a field (Date Received) from a third
table (From 3P Table) into this report. The common field is Correspondence
Reference Number and the table already in the report is the Correspondence
Table.

2) Another field is a calculated field and the Expression I need to use is
this one: Days: DateDiff("d",[Date Sent],NZ([Date Received],Date())). How do
I include this in the SQL window?

I know these must be dumb questions but I can't write SQL.

Thank you very much for taking the time to help me!


--

igor


KARL DEWEY said:
Correct any table and field names. Create a new query in design view, do not
select a table, click on VIEW - SQL View, paste in the new window.

--
KARL DEWEY
Build a little - Test a little


Igor said:
Thank you for the help, Karl.

One question: Where should I write this (what you told me)? Maybe in the
Field in the Query with the expression builder?

(Sorry, I'm just starting with Access)

Thanks again!

--

igor


KARL DEWEY said:
Try this ---
SELECT [Index Table].[Document Name], [Index Table].[Document Type], [Index
Table].[Document Number], [Correspondence Table].[Modification Number],
[Correspondence Table].[Date Sent], [Correspondence Table].[Reply Received],
[Correspondence Table].[Reply Received Date]
FROM [Index Table] LEFT JOIN [Correspondence Table] ON [Index
Table].[Document Number] = [Correspondence Table].[Document Number];


--
KARL DEWEY
Build a little - Test a little


:

Hello,

I need to generate a report with the following fields:

Document Name, Document Type, Document Number, Modification Number, Date
Sent, Reply Received, Reply Received Date.

For this I have two tables:

The Correspondence Table has all the information for all the documents that
have been already sent. This table contains the fields Document Number,
Modification Number, Date Sent, Reply Received, Reply Received Date.

(Note: A document with the same Document Number will be sent several times,
depending on the number of corrections made to it. This means that for the
same Document Number there will be a different Modification Number, Sent
Date, Reply Received and Reply Received Date. And each of these deliveries
has to have it's own row.)

The Index Table has the complete list of all the documents that will be sent
to a client over a time period. These documents will not be sent at the same
time or in a specific order. This table just has three fields, Document Name,
Document Type and Document Number.

(!)Problem: I need that the report shows all the documents, including the
ones that have not been sent.

Example: Document 1 has been sent two times, Document 5 has been sent 3
times and Document 7 has been sent 1 time. Documents 2, 3, 4 and 6 have not
been sent. The report has to get the information for Documents 1, 5 and 7
from the Correspondence Table and the information for Documents 2, 3, 4 and 6
from the Index Table.

(!!)Desired result: The Report should generate 10 rows. 6 rows (for
Documents 1, 5 and 7) should have values in all the report fields. And 4 rows
(for Documents 2, 3, 4 and 6) should have values only in the fields Document
Name, Document Type, Document Number (the fields that are in the Table Index).

I would greatly appreciate any help!
Click to expand...
Click to expand...
Click to expand...
 
Try this and again check table and field names --
SELECT [Index Table].[Document Name], [Index Table].[Document Type], [Index
Table].[Document Number], [Correspondence Table].[Modification Number],
[Correspondence Table].[Date Sent], [Correspondence Table].[Reply Received],
[Correspondence Table].[Reply Received Date], DateDiff("d",[Correspondence
Table].[Date Sent],NZ([Correspondence Table].[Date Received],Date())) AS
[Days], [3P Table].[Date Received]
FROM ([Index Table] LEFT JOIN [Correspondence Table] ON [Index
Table].[Document Number] = [Correspondence Table].[Document Number])
[Correspondence Table] LEFT JOIN [3P Table] ON [Correspondence
Table].[Correspondence Reference Number] = [3P Table].[Correspondence
Reference Number];



--
KARL DEWEY
Build a little - Test a little


Igor said:
It works!

Karl, two last questions:

1) In this report I need to include a field (Date Received) from a third
table (From 3P Table) into this report. The common field is Correspondence
Reference Number and the table already in the report is the Correspondence
Table.

2) Another field is a calculated field and the Expression I need to use is
this one: Days: DateDiff("d",[Date Sent],NZ([Date Received],Date())). How do
I include this in the SQL window?

I know these must be dumb questions but I can't write SQL.

Thank you very much for taking the time to help me!


--

igor


KARL DEWEY said:
Correct any table and field names. Create a new query in design view, do not
select a table, click on VIEW - SQL View, paste in the new window.

--
KARL DEWEY
Build a little - Test a little


Igor said:
Thank you for the help, Karl.

One question: Where should I write this (what you told me)? Maybe in the
Field in the Query with the expression builder?

(Sorry, I'm just starting with Access)

Thanks again!

--

igor


:

Try this ---
SELECT [Index Table].[Document Name], [Index Table].[Document Type], [Index
Table].[Document Number], [Correspondence Table].[Modification Number],
[Correspondence Table].[Date Sent], [Correspondence Table].[Reply Received],
[Correspondence Table].[Reply Received Date]
FROM [Index Table] LEFT JOIN [Correspondence Table] ON [Index
Table].[Document Number] = [Correspondence Table].[Document Number];


--
KARL DEWEY
Build a little - Test a little


:

Hello,

I need to generate a report with the following fields:

Document Name, Document Type, Document Number, Modification Number, Date
Sent, Reply Received, Reply Received Date.

For this I have two tables:

The Correspondence Table has all the information for all the documents that
have been already sent. This table contains the fields Document Number,
Modification Number, Date Sent, Reply Received, Reply Received Date.

(Note: A document with the same Document Number will be sent several times,
depending on the number of corrections made to it. This means that for the
same Document Number there will be a different Modification Number, Sent
Date, Reply Received and Reply Received Date. And each of these deliveries
has to have it's own row.)

The Index Table has the complete list of all the documents that will be sent
to a client over a time period. These documents will not be sent at the same
time or in a specific order. This table just has three fields, Document Name,
Document Type and Document Number.

(!)Problem: I need that the report shows all the documents, including the
ones that have not been sent.

Example: Document 1 has been sent two times, Document 5 has been sent 3
times and Document 7 has been sent 1 time. Documents 2, 3, 4 and 6 have not
been sent. The report has to get the information for Documents 1, 5 and 7
from the Correspondence Table and the information for Documents 2, 3, 4 and 6
from the Index Table.

(!!)Desired result: The Report should generate 10 rows. 6 rows (for
Documents 1, 5 and 7) should have values in all the report fields. And 4 rows
(for Documents 2, 3, 4 and 6) should have values only in the fields Document
Name, Document Type, Document Number (the fields that are in the Table Index).

I would greatly appreciate any help!
Click to expand...
Click to expand...
Click to expand...
 
Hello, Karl,

I'm getting an error message:

"Syntax error in FROM clause."

Why could this be? I wrote it exactly as you have it.

--

igor


KARL DEWEY said:
Try this and again check table and field names --
SELECT [Index Table].[Document Name], [Index Table].[Document Type], [Index
Table].[Document Number], [Correspondence Table].[Modification Number],
[Correspondence Table].[Date Sent], [Correspondence Table].[Reply Received],
[Correspondence Table].[Reply Received Date], DateDiff("d",[Correspondence
Table].[Date Sent],NZ([Correspondence Table].[Date Received],Date())) AS
[Days], [3P Table].[Date Received]
FROM ([Index Table] LEFT JOIN [Correspondence Table] ON [Index
Table].[Document Number] = [Correspondence Table].[Document Number])
[Correspondence Table] LEFT JOIN [3P Table] ON [Correspondence
Table].[Correspondence Reference Number] = [3P Table].[Correspondence
Reference Number];



--
KARL DEWEY
Build a little - Test a little


Igor said:
It works!

Karl, two last questions:

1) In this report I need to include a field (Date Received) from a third
table (From 3P Table) into this report. The common field is Correspondence
Reference Number and the table already in the report is the Correspondence
Table.

2) Another field is a calculated field and the Expression I need to use is
this one: Days: DateDiff("d",[Date Sent],NZ([Date Received],Date())). How do
I include this in the SQL window?

I know these must be dumb questions but I can't write SQL.

Thank you very much for taking the time to help me!


--

igor


KARL DEWEY said:
Correct any table and field names. Create a new query in design view, do not
select a table, click on VIEW - SQL View, paste in the new window.

--
KARL DEWEY
Build a little - Test a little


:

Thank you for the help, Karl.

One question: Where should I write this (what you told me)? Maybe in the
Field in the Query with the expression builder?

(Sorry, I'm just starting with Access)

Thanks again!

--

igor


:

Try this ---
SELECT [Index Table].[Document Name], [Index Table].[Document Type], [Index
Table].[Document Number], [Correspondence Table].[Modification Number],
[Correspondence Table].[Date Sent], [Correspondence Table].[Reply Received],
[Correspondence Table].[Reply Received Date]
FROM [Index Table] LEFT JOIN [Correspondence Table] ON [Index
Table].[Document Number] = [Correspondence Table].[Document Number];


--
KARL DEWEY
Build a little - Test a little


:

Hello,

I need to generate a report with the following fields:

Document Name, Document Type, Document Number, Modification Number, Date
Sent, Reply Received, Reply Received Date.

For this I have two tables:

The Correspondence Table has all the information for all the documents that
have been already sent. This table contains the fields Document Number,
Modification Number, Date Sent, Reply Received, Reply Received Date.

(Note: A document with the same Document Number will be sent several times,
depending on the number of corrections made to it. This means that for the
same Document Number there will be a different Modification Number, Sent
Date, Reply Received and Reply Received Date. And each of these deliveries
has to have it's own row.)

The Index Table has the complete list of all the documents that will be sent
to a client over a time period. These documents will not be sent at the same
time or in a specific order. This table just has three fields, Document Name,
Document Type and Document Number.

(!)Problem: I need that the report shows all the documents, including the
ones that have not been sent.

Example: Document 1 has been sent two times, Document 5 has been sent 3
times and Document 7 has been sent 1 time. Documents 2, 3, 4 and 6 have not
been sent. The report has to get the information for Documents 1, 5 and 7
from the Correspondence Table and the information for Documents 2, 3, 4 and 6
from the Index Table.

(!!)Desired result: The Report should generate 10 rows. 6 rows (for
Documents 1, 5 and 7) should have values in all the report fields. And 4 rows
(for Documents 2, 3, 4 and 6) should have values only in the fields Document
Name, Document Type, Document Number (the fields that are in the Table Index).

I would greatly appreciate any help!
Click to expand...
Click to expand...
Click to expand...
 
Thank you very much for your help, Karl!

What I did to solve the problem was this:

FROM [Deliverables Index] LEFT JOIN ([To ACP] LEFT JOIN [From ACP] ON [To
ACP].[ACP Reference Number]=[From ACP].[ACP Reference Number]) ON
[Deliverables Index].[Document Number]=[To ACP].[Document Number];

And problem solved!

I appreciate very much that you took the time to help me out.

--

igor


Igor said:
Hello, Karl,

I'm getting an error message:

"Syntax error in FROM clause."

Why could this be? I wrote it exactly as you have it.

--

igor


KARL DEWEY said:
Try this and again check table and field names --
SELECT [Index Table].[Document Name], [Index Table].[Document Type], [Index
Table].[Document Number], [Correspondence Table].[Modification Number],
[Correspondence Table].[Date Sent], [Correspondence Table].[Reply Received],
[Correspondence Table].[Reply Received Date], DateDiff("d",[Correspondence
Table].[Date Sent],NZ([Correspondence Table].[Date Received],Date())) AS
[Days], [3P Table].[Date Received]
FROM ([Index Table] LEFT JOIN [Correspondence Table] ON [Index
Table].[Document Number] = [Correspondence Table].[Document Number])
[Correspondence Table] LEFT JOIN [3P Table] ON [Correspondence
Table].[Correspondence Reference Number] = [3P Table].[Correspondence
Reference Number];



--
KARL DEWEY
Build a little - Test a little


Igor said:
It works!

Karl, two last questions:

1) In this report I need to include a field (Date Received) from a third
table (From 3P Table) into this report. The common field is Correspondence
Reference Number and the table already in the report is the Correspondence
Table.

2) Another field is a calculated field and the Expression I need to use is
this one: Days: DateDiff("d",[Date Sent],NZ([Date Received],Date())). How do
I include this in the SQL window?

I know these must be dumb questions but I can't write SQL.

Thank you very much for taking the time to help me!


--

igor


:

Correct any table and field names. Create a new query in design view, do not
select a table, click on VIEW - SQL View, paste in the new window.

--
KARL DEWEY
Build a little - Test a little


:

Thank you for the help, Karl.

One question: Where should I write this (what you told me)? Maybe in the
Field in the Query with the expression builder?

(Sorry, I'm just starting with Access)

Thanks again!

--

igor


:

Try this ---
SELECT [Index Table].[Document Name], [Index Table].[Document Type], [Index
Table].[Document Number], [Correspondence Table].[Modification Number],
[Correspondence Table].[Date Sent], [Correspondence Table].[Reply Received],
[Correspondence Table].[Reply Received Date]
FROM [Index Table] LEFT JOIN [Correspondence Table] ON [Index
Table].[Document Number] = [Correspondence Table].[Document Number];


--
KARL DEWEY
Build a little - Test a little


:

Hello,

I need to generate a report with the following fields:

Document Name, Document Type, Document Number, Modification Number, Date
Sent, Reply Received, Reply Received Date.

For this I have two tables:

The Correspondence Table has all the information for all the documents that
have been already sent. This table contains the fields Document Number,
Modification Number, Date Sent, Reply Received, Reply Received Date.

(Note: A document with the same Document Number will be sent several times,
depending on the number of corrections made to it. This means that for the
same Document Number there will be a different Modification Number, Sent
Date, Reply Received and Reply Received Date. And each of these deliveries
has to have it's own row.)

The Index Table has the complete list of all the documents that will be sent
to a client over a time period. These documents will not be sent at the same
time or in a specific order. This table just has three fields, Document Name,
Document Type and Document Number.

(!)Problem: I need that the report shows all the documents, including the
ones that have not been sent.

Example: Document 1 has been sent two times, Document 5 has been sent 3
times and Document 7 has been sent 1 time. Documents 2, 3, 4 and 6 have not
been sent. The report has to get the information for Documents 1, 5 and 7
from the Correspondence Table and the information for Documents 2, 3, 4 and 6
from the Index Table.

(!!)Desired result: The Report should generate 10 rows. 6 rows (for
Documents 1, 5 and 7) should have values in all the report fields. And 4 rows
(for Documents 2, 3, 4 and 6) should have values only in the fields Document
Name, Document Type, Document Number (the fields that are in the Table Index).

I would greatly appreciate any help!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top