Access Report Question

[MakinBacon91]

Hello All,

I have been working on building a work in process (WIP) report for my company.
This report is based off of a database I have been working on for the past month or so.
I am wondering if I have something setup wrong in my database, or if I am just misunderstanding how reports work.

Basically my problem is:
I was adding information to my report, everything was fine. Until I added a piece of information that each record did not contain.
I added a table for Induction Date, and added a field for that to my report.
Now when I run my report, it only shows records that have an induction date assigned.




Is there a problem that I have some tables with 1-1 reference, and one of tables has 7 entries and the other has only 3?

Sincerely Thanks,
-Bacon-

 

[Joe4]

Is your Report based off a single table or a query?

I usually like to have a single query that contains all the data I need for my Report and base my Report off of that. Otherwise, if you want to involve data from multiple tables in your query, you would either need to use Subreports or DLOOKUPs (not recommended).

 

[MakinBacon91]

Is your Report based off a single table or a query?

I usually like to have a single query that contains all the data I need for my Report and base my Report off of that. Otherwise, if you want to involve data from multiple tables in your query, you would either need to use Subreports or DLOOKUPs (not recommended).

Well, to answer your question, I don't think I am doing either of those.
I started with a blank form, and I am adding information piece wise, by using the "Add Existing Fields" button.
Essentially I think it is the same thing as building off of a query, but I am making the 'query' as I go.

To your point, the same thing happens to me for a query, and I am not sure what to do to avoid it.
I.E. I want to query for all the job information for all jobs. However, if a job is not included in one of the tables I query across, then it will not show up in my query.
Does that make sense?

Thanks,
-Bacon-

 

[MakinBacon91]

JobID	UnitID	SetNo	PartTypeID	Status
1	DUK	1501	7FA	Open
2	LSP	1501	7FA	Open
3	DUK	1502	7FA	Open
4	DUK	1502	7FA	Open
5	DUK	1503	7FA	Open
6	DUK	1504	7FA	Open
7	DUK	1505	7FA	Open

<tbody>
</tbody>

JobID	SendS&I	CompletePre-Coat	ShipDate
DUK-1501	11/30/2015
DUK-1502		12/11/2015
DUK-1503			12/11/2015

<tbody>
</tbody>

Here are some tables I have.
So if I do a query for all information only DUK-1501 , -1502, and -1503 will show up.

For example: Here is my query

Job Number	PartTypeID	Status	UnitID	SendS&I	CompletePre-Coat	ShipDate
DUK-1501	7FA	Open	2	11/30/2015
DUK-1502	7FA	Open	2		12/11/2015
DUK-1503	7FA	Open	2			12/11/2015

<caption> Query1 </caption><thead>
</thead><tbody>
</tbody><tfoot></tfoot>

But if I remove the Date table from my query here are the results

Job Number	PartTypeID	Status	UnitID
DUK-1501	7FA	Open	2
DUK-1502	7FA	Open	2
DUK-1503	7FA	Open	2
DUK-1504	7FA	Open	2
DUK-1505	7FA	Open	2
DUK-1506	7FA	Open	2
LSP-1502	7FA	Open	4

<caption> Query1 </caption><thead>
</thead><tbody>
</tbody><tfoot></tfoot>

 

[Joe4]

However, if a job is not included in one of the tables I query across, then it will not show up in my query.

In order to have unmatched records show, you will need to perform a Left Join in your Query (which says "Show ALL records from TableA, and then show any matching records from TableB). See here: MS Access 2003: Create a query using a Left Join

I am adding information piece wise, by using the "Add Existing Fields" button.

If you are doing this, you already have a defined data source for your report (that is what the Available Field Box shows, fields available in the data source you have chosen for this report). If you bring up the Proprties of the Report, and go to the Data tab, take a look at the first property, "Record Source". This is the table or query that your data for this report is coming from. If it is a query, then this is where you need to go to make the Join changes to see the data that you want. If it is a table, then you need to create the query that returns the data that you want, then update this property to select this new query.

 

[MakinBacon91]

Haha, Well I think I just figured out my solution.
Under Relationships View I went into the relationship between JobInfo and Date, and I changed the Join Type to the second option, "Include ALL records from 'JobInfo' and only those from 'Date' where the joined fields are equal"

Thanks Joe!
-Bacon-

 

[Joe4]

Under Relationships View I went into the relationship between JobInfo and Date, and I changed the Join Type to the second option, "Include ALL records from 'JobInfo' and only those from 'Date' where the joined fields are equal"

Yes, that is exactly what I was telling you to do in my previous post. The link I provided demonstrates how to do that.