Report coming up blank

[punter]

Hi,

I'm just starting to design somes reports and have run into a problem. The attached database has a report named BOL information. It pulls data from various tables. If the data in one of the fields is blank the whole report is coming up blank. Any thoughts on why this is happening? I have been trying to figure this out but haven't foudn the answer yet.

I know the report doesn't look very pretty. It is not even close to being finished. The first thing I have to do is get the data to come through then I can clean it up.

Thanks

Eddie.

 

[stopher]

The reason the report returns no data is that there is no data in the "Broker Information" table. The underlying query for your report is looking for matching records in all three tables. While there are matching records in two of your tables, there isn't any data, let alone matching data, in the third. Hence no data returned.

You can resolve this using the following query in place of the current one:

SELECT [BOL information].[Unique Number], [BOL information].DateRecordCreated, Container_Vendor_Information.VendorName, [BOL information].Destination, [BOL information].Vessel, Container_Vendor_Information.CartonCount, Container_Vendor_Information.ContainerNumber, Container_Vendor_Information.VendorInvoiceNumber, [BOL information].[BOL Number], [BOL information].[BOL Date], [BOL information].[ETA Date], [Broker Information].[Ref Number], [Broker Information].Duty, Container_Vendor_Information.InvoiceAmount
FROM ([Broker Information] RIGHT JOIN [BOL information] ON [Broker Information].[Unique Number] = [BOL information].[Unique Number]) INNER JOIN Container_Vendor_Information ON [BOL information].[Unique Number] = Container_Vendor_Information.UniqueNumber;

However, I have concerns about your database design. Tables "Container_Vendor_Information" and "BOL information" don't have Primary keys. As such, I'm at a loss to really understand the real relationships between your three tables. Furthermore, your relationship diagram joins all three tables to each other. I'm not saying this is wrong but it's highly unlikely/unusual. While there might be an implied relationship between two tables, it is usually only because of the relationships between an intermediary table.

In your d/b there appear to be several "container rentals/charges(?)" associated with each one BOL. How many brokers are associated with the BOL ? one-to-one ? In whcih case put them both in one table.

Is Broker really directly associated with container vendor information ? I suspect not (although I don't know enough about the data/subject to say)

hth
Stopher

hth
Stopher

 

[punter]

Thank you so much for your reply. I'll answer your questions one at a time:

1-However, I have concerns about your database design. Tables "Container_Vendor_Information" and "BOL information" don't have Primary keys.

There are three pieces to this puzzle (maybe they can be combinded into less than three tables, that remains to be seen) the BOL information, the Container information, and the Broker information. There is nothing that links all of them together (invoice number, vendor name, ect) so I had to create a unique number (generated by access). This unique number is how I link the information from the three tables together. Should I be doing something different? Can I make the unique # the primary key in all three fields? I'm thinking that maybe I can.

2-In your d/b there appear to be several "container rentals/charges(?)" associated with each one BOL. How many brokers are associated with the BOL ? one-to-one ? In whcih case put them both in one table.

There can very easily (and usually is) more than one container for each BOL.

There will only be one broker per bill of lading. Do you think I should put the Broker information on the BOL information table?

Thank you so much for all of your help.

Eddie.

 

[punter]

Hi,

This was something I was working on and got pulled off to do some other stuff last week. I can start playing with it again.

I'm new to access but trying all the time to get better. Stopher below kindly posted a query I should replace my current query with. The problem is I'm not sure where I should be doing that. Any thoughts?

This is an awesome site. I've learned a lot and I'm sure I'll learn more. Thank you all for your time and advice.

Eddie.

 

[Wiz47]

Hi,

This was something I was working on and got pulled off to do some other stuff last week. I can start playing with it again.

I'm new to access but trying all the time to get better. Stopher below kindly posted a query I should replace my current query with. The problem is I'm not sure where I should be doing that. Any thoughts?

This is an awesome site. I've learned a lot and I'm sure I'll learn more. Thank you all for your time and advice.

Eddie.

I presume that your current query is the record source for your report. If that is the case, then either edit the current query or create a new one and replace it as the record source.