Can't figure out how to display table field in report

[Lateral]

Hi guys

I'm a newbie and using Access 2007.

I have a Customer table that has a field "PostalCode" that is numerical an is connected to a field called PostalID in a table called PostalCode and has the actual postal code stored in a field call Pcode.

I have everything working ok as far as displaying the correct Pcode on the various forms. eg: is the PostalID is 10, I want the form to display the Pcode of 2000.

I can't figure out how to display the Pcode in a report.....help!

Thanks

Best Regards
Greg

 

[CazB]

Your report needs to be based upon a query that contains both tables, with the relationship between the two tables in place. You then just add your PCode from the PostalCode table into the query, instead of (or as well as, if you prefer!) the PostalCode field from your Customers table...


Alternatively, you could change the PostalCode text box in your report into a Combo Box... with the PostalCode table as it's record source.... but that doesn't look as good as it means you have a combo box on your report!

HTH

 

[RainLover]

Your Postal Code should not be the Primary Key as it is not unique. That is if I read you correctly.

An Auto number would make a better PK. Then a foreign Key in the other table should be a Integer.

Do you need it explained in more detail. Happy to do so it you want. Simply post the structure of your current two tables and I will adjust.

 

[Lateral]

Thanks guys.

I'll have a play and and get back to you to let you know how I go.

Rain, can you add a bit more information for me?

Thanks

Regards
Greg

 

[RainLover]

Thanks guys.

I'll have a play and and get back to you to let you know how I go.

Rain, can you add a bit more information for me?

Thanks

Regards
Greg

Can you place the two tables in a new Database and post that Database here.

PLEASE convert to Access 2003 before posting.

 

[Lateral]

Hi Rain,

I thought it would be easier to upload the whole database

Regards
Greg

 

[RainLover]

I think if you fix the following you should be able to do what you need.

This link explains why you should not use Look ups in Tables. Forms are just fine but not Tables mainly because they hide what is really stored in the Table.
You should get rid of them all together. This alone may solve your problem.
http://access.mvps.org/access/lookupfields.htm

Additional Information.

The Table Work Order Labor has the wrong Joins. It should be linked to either one of the two tables you have not both. You need to think this through and decide which one.

Your Naming Conventions could be better. For Example never use spaces. Spaces require special attention when it comes to Code. You can have them but better off without them. If you click on the link in my signature that will take you to a Sky Drive where you can download an article on the subject.

I don't know why you are having problems with your report. I have created one for you and is attached.

 

[Lateral]

Thanks Rain!

I'll have a look at all of the issues you have raised.

Regarding the naming conventions, this database is one of the templates that I downloaded from Microsoft, so I have inherited the naming.....

Regards
Greg

PS: Thanks again

 

[RainLover]

Regarding the naming conventions, this database is one of the templates that I downloaded from Microsoft, so I have inherited the naming.....

Microsoft has its reasons. Mainly to show that you can have spaces.

However most people would tell you that you are better off without them.

Good Luck with the project.

 

[Lateral]

Hi Rain,

Just a quick few questions:

1. In the Rain database you have created a report called RptRain that has the QryRain as the source of the form. I assume that the query is what is actually determining what data/columns are being made available to the report?

2. I looked a the Customer table and the Lookup was still in place for the PostalCode. I removed the Lookup and your form still worked.

3. I read the link regarding the use or rather reasons for not using Lookups. So, in summary, don't use Lookups but use Queries instead?

Thanks again for your help.....

Regards
Greg

 

[RainLover]

Hi Rain,

Just a quick few questions:

1. In the Rain database you have created a report called RptRain that has the QryRain as the source of the form. I assume that the query is what is actually determining what data/columns are being made available to the report?/

Yes it is. You could have confirmed this by looking at the the Record Source which is found in the properties box.

2. I looked a the Customer table and the Lookup was still in place for the PostalCode. I removed the Lookup and your form still worked.

Is there a question in this because that is what should be expected.

3. I read the link regarding the use or rather reasons for not using Lookups. So, in summary, don't use Lookups but use Queries instead?

Not quite. Don't use Lookups at the table level. But they are an essential part of Forms.

 

[Lateral]

Thanks again Rain!

 

[RainLover]

Thanks again Rain!

Good luck with the project.

There are heaps of people here willing to help.