Not Showing All Info only linked

[Firefly]

As you can see by the attach Not all the Insurances are showing up on the report. If you take a look at the frmProvider and Go To default Pin tab you will see one insurance with a specific set of numbers and you will see another one where the insurance field is blank which will apply these numbers too all insurances in the database that are not the Sub Form.

I want the report to look something like this


La Vida Medical Group TaxID 11111111111111111111
NPI#222222222222222222222

Medicare TaxID 123456789
NPI# 455555555555555


Medical TaxID 123456789
NPI# 455555555555555

Etc Etc but only The La Vida Medical Group Shows On Report Any Ideas.


Thanks

 

[neileg]

Needs work! I'll come to that in a minute.

Your report is based on the Insurance table and tblDefaultPins. The join is on Insurance Name and is an inner join. This means there has to be matching values in each table, and there's only La Vida Med in both. If you want all insurance names even if there's no matching value in tblDefaultPins, you need to change the join type to a Left Outer Join (type 2 in Access terms).

Now for the problems
1) Don't use symbols or punctuation in object names. So remove # and spaces from your field names
2) You shouldn't need NPI in both tblProviders and tblDefaultPins. Same with TaxID. Unless I haven't understood what these are.
3) You are holding InsuranceName in tblDefaultPins. It should be InsuranceID. Thats one of the main reasons for having a primary key.
4) You may well find it easier in the long run to create stored queries and base your reports and forms on those instead of building the dataset inside the report definition.

 

[Firefly]

Ok Got the Left outer join working and will be tinkering around a bit with my other reports.. Will fix to your recommendations. But just for hear sake lets say i Will name tblDeafultpin and change the fileds to TaxID1 and NPI1 so they be different and delete the record where the Insurance is null. is there a way to to put the info in frmProvider specifically TAXID and NPI for insurances not in the defaults pins tab or the same thing applies.

The reason I'm asking is certain insurances require there own numbers in those fields the frmprovider in the tab called provider info will be default and the default pins tab is when a insurance requires there own TAXID OR NPI.

 

[Firefly]

Never Mind Got it to work with your Ideas changed everything around like you said works perfectly just needed to create an append query now i need to review my homwork a little on queries lol.

 

[neileg]

Happy to help