Queries/Reports not showing records

[TracyM]

Hi, hope someone can help

I have created a database that logs projects coming into the dept, it all
works fine. I have also created a data access page so the staff can fill in a
project request on the intranet and thus create a record on the database.
This also works fine, when I close down the database and open up the data
access page and fill it in, I close this down and open the database and it
successfully created a new record.

The problem is I have reports & queries and none of them will bring up any
record created via the data access page? The records are there because I can
see them in the table, but I don't know why the queries & reports will not
show them, even if I ask it to show all records it will only show records
that have been entered directly onto the database.

I would really appreciate any help

Thanks
Tracy

 

[Guest]

Show us the SQL. Open the query in design view. Next go to View, SQL View
and copy and past it here. Information on primary keys and relationships
would be a nice touch too. Also any indexes.

 

[TracyM]

Hi Jerry

SQL as requested:
SELECT [Main Project Table].[Customer Name], [Main Project Table].[Project ID]
, [Main Project Table].[Project/Job description], [Main Project Table].[Date
Arrived], [Main Project Table].[Project Name], [Main Project Table].[Project
Manager], [Resource Scheduler].Status, [Resource Scheduler].[Status Notes],
[Resource Scheduler].[Colleague Name]
FROM [Main Project Table] INNER JOIN [Resource Scheduler] ON [Main Project
Table].[Project ID] = [Resource Scheduler].[Project ID]
WHERE ((([Main Project Table].[Customer Name]) Like [Enter customer name, or
the first part of it] & "*"));

Show us the SQL. Open the query in design view. Next go to View, SQL View
and copy and past it here. Information on primary keys and relationships
would be a nice touch too. Also any indexes.

Hi, hope someone can help

[quoted text clipped - 15 lines]

Thanks
Tracy

 

[TracyM]

Sorry, for got to add

This is based on two tables linked with a one-to-many relationship, One is
the main project table where customers project info goes and the 'many table'
is the people who have been allocated to each part of the project. The
Primary key is Project ID is the main project table, the linked table does
not have a primary key, its numbers are whatever the project id is in the
main table

Hope that helps

Hi Jerry

SQL as requested:
SELECT [Main Project Table].[Customer Name], [Main Project Table].[Project ID]
, [Main Project Table].[Project/Job description], [Main Project Table].[Date
Arrived], [Main Project Table].[Project Name], [Main Project Table].[Project
Manager], [Resource Scheduler].Status, [Resource Scheduler].[Status Notes],
[Resource Scheduler].[Colleague Name]
FROM [Main Project Table] INNER JOIN [Resource Scheduler] ON [Main Project
Table].[Project ID] = [Resource Scheduler].[Project ID]
WHERE ((([Main Project Table].[Customer Name]) Like [Enter customer name, or
the first part of it] & "*"));

Show us the SQL. Open the query in design view. Next go to View, SQL View
and copy and past it here. Information on primary keys and relationships

[quoted text clipped - 4 lines]

 

[Jason Lepack]

Remove your WHERE clause and see what that returns. You also might
want to put [] around status.

Try this:
SELECT [Main Project Table].[Customer Name], [Main Project Table].
[Project ID]
, [Main Project Table].[Project/Job description], [Main Project Table].
[Date
Arrived], [Main Project Table].[Project Name], [Main Project Table].
[Project
Manager], [Resource Scheduler].[Status], [Resource Scheduler].[Status
Notes],
[Resource Scheduler].[Colleague Name]
FROM [Main Project Table] INNER JOIN [Resource Scheduler] ON [Main
Project
Table].[Project ID] = [Resource Scheduler].[Project ID]

If that doesn't work then you have a problem with your join. You may
not have matching Project ID's.

Cheers,
Jason Lepack

Sorry, for got to add

This is based on two tables linked with a one-to-many relationship, One is
the main project table where customers project info goes and the 'many table'
is the people who have been allocated to each part of the project. The
Primary key is Project ID is the main project table, the linked table does
not have a primary key, its numbers are whatever the project id is in the
main table

Hope that helps

Hi Jerry

SQL as requested:
SELECT [Main Project Table].[Customer Name], [Main Project Table].[Project ID]
, [Main Project Table].[Project/Job description], [Main Project Table].[Date
Arrived], [Main Project Table].[Project Name], [Main Project Table].[Project
Manager], [Resource Scheduler].Status, [Resource Scheduler].[Status Notes],
[Resource Scheduler].[Colleague Name]
FROM [Main Project Table] INNER JOIN [Resource Scheduler] ON [Main Project
Table].[Project ID] = [Resource Scheduler].[Project ID]
WHERE ((([Main Project Table].[Customer Name]) Like [Enter customer name, or
the first part of it] & "*"));

Show us the SQL. Open the query in design view. Next go to View, SQL View
and copy and past it here. Information on primary keys and relationships

[quoted text clipped - 4 lines]

Thanks
Tracy- Hide quoted text -

- Show quoted text -

 

[TracyM]

Hi Jason

Thanks for the advice, you were right it was my join's I have changed this
and its now working great! Absolutely brillant!!!!!!!

Thanks Again
Tracy

Remove your WHERE clause and see what that returns. You also might
want to put [] around status.

Try this:
SELECT [Main Project Table].[Customer Name], [Main Project Table].
[Project ID]
, [Main Project Table].[Project/Job description], [Main Project Table].
[Date
Arrived], [Main Project Table].[Project Name], [Main Project Table].
[Project
Manager], [Resource Scheduler].[Status], [Resource Scheduler].[Status
Notes],
[Resource Scheduler].[Colleague Name]
FROM [Main Project Table] INNER JOIN [Resource Scheduler] ON [Main
Project
Table].[Project ID] = [Resource Scheduler].[Project ID]

If that doesn't work then you have a problem with your join. You may
not have matching Project ID's.

Cheers,
Jason Lepack

Sorry, for got to add

[quoted text clipped - 27 lines]

- Show quoted text -

 

[Guest]

Hi Jason,

I like your troubleshooting style. Make the query simpler and see what
happens.

In this case I bet that you are right about the join. Changing the INNER
JOIN to LEFT JOIN or maybe RIGHT JOIN should do the trick.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Remove your WHERE clause and see what that returns. You also might
want to put [] around status.

Try this:
SELECT [Main Project Table].[Customer Name], [Main Project Table].
[Project ID]
, [Main Project Table].[Project/Job description], [Main Project Table].
[Date
Arrived], [Main Project Table].[Project Name], [Main Project Table].
[Project
Manager], [Resource Scheduler].[Status], [Resource Scheduler].[Status
Notes],
[Resource Scheduler].[Colleague Name]
FROM [Main Project Table] INNER JOIN [Resource Scheduler] ON [Main
Project
Table].[Project ID] = [Resource Scheduler].[Project ID]

If that doesn't work then you have a problem with your join. You may
not have matching Project ID's.

Cheers,
Jason Lepack

Sorry, for got to add

This is based on two tables linked with a one-to-many relationship, One is
the main project table where customers project info goes and the 'many table'
is the people who have been allocated to each part of the project. The
Primary key is Project ID is the main project table, the linked table does
not have a primary key, its numbers are whatever the project id is in the
main table

Hope that helps

Hi Jerry

SQL as requested:
SELECT [Main Project Table].[Customer Name], [Main Project Table].[Project ID]
, [Main Project Table].[Project/Job description], [Main Project Table].[Date
Arrived], [Main Project Table].[Project Name], [Main Project Table].[Project
Manager], [Resource Scheduler].Status, [Resource Scheduler].[Status Notes],
[Resource Scheduler].[Colleague Name]
FROM [Main Project Table] INNER JOIN [Resource Scheduler] ON [Main Project
Table].[Project ID] = [Resource Scheduler].[Project ID]
WHERE ((([Main Project Table].[Customer Name]) Like [Enter customer name, or
the first part of it] & "*"));

Show us the SQL. Open the query in design view. Next go to View, SQL View
and copy and past it here. Information on primary keys and relationships
[quoted text clipped - 4 lines]
Thanks
Tracy- Hide quoted text -

- Show quoted text -

 

[Jason Lepack]

Thanks Jerry. It's nice to hear the feedback every now and again.

Hi Jason,

I like your troubleshooting style. Make the query simpler and see what
happens.

In this case I bet that you are right about the join. Changing the INNER
JOIN to LEFT JOIN or maybe RIGHT JOIN should do the trick.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Remove your WHERE clause and see what that returns. You also might
want to put [] around status.

Try this:
SELECT [Main Project Table].[Customer Name], [Main Project Table].
[Project ID]
, [Main Project Table].[Project/Job description], [Main Project Table].
[Date
Arrived], [Main Project Table].[Project Name], [Main Project Table].
[Project
Manager], [Resource Scheduler].[Status], [Resource Scheduler].[Status
Notes],
[Resource Scheduler].[Colleague Name]
FROM [Main Project Table] INNER JOIN [Resource Scheduler] ON [Main
Project
Table].[Project ID] = [Resource Scheduler].[Project ID]

If that doesn't work then you have a problem with your join. You may
not have matching Project ID's.

Cheers,
Jason Lepack

Sorry, for got to add
This is based on two tables linked with a one-to-many relationship, One is
the main project table where customers project info goes and the 'many table'
is the people who have been allocated to each part of the project. The
Primary key is Project ID is the main project table, the linked table does
not have a primary key, its numbers are whatever the project id is in the
main table
Hope that helps
TracyM wrote:
Hi Jerry
SQL as requested:
SELECT [Main Project Table].[Customer Name], [Main Project Table].[Project ID]
, [Main Project Table].[Project/Job description], [Main Project Table].[Date
Arrived], [Main Project Table].[Project Name], [Main Project Table].[Project
Manager], [Resource Scheduler].Status, [Resource Scheduler].[Status Notes],
[Resource Scheduler].[Colleague Name]
FROM [Main Project Table] INNER JOIN [Resource Scheduler] ON [Main Project
Table].[Project ID] = [Resource Scheduler].[Project ID]
WHERE ((([Main Project Table].[Customer Name]) Like [Enter customer name, or
the first part of it] & "*"));
Show us the SQL. Open the query in design view. Next go to View, SQL View
and copy and past it here. Information on primary keys and relationships
[quoted text clipped - 4 lines]
Thanks
Tracy- Hide quoted text -
- Show quoted text -- Hide quoted text -

- Show quoted text -