Multiple Report Issues within single Database

[Shannon West]

I have recently acquired the job of updating our Access dB (2000 version) and I am a bit over my head here (my work with Access was limited to entering information into forms or running the reports requested - now they are asking me to update/fix/DESIGN these things!!). The database was designed by some other party many years ago, and has been copied to create new databases every year. This data base if for shipping of Christmas trees, so it hold load date, ship date, loading site, carrier, multiple customers (drops) and products. There are various reports that are run for the field crews so that they are always on top of what needs to be cut, what fields those will come from, etc. The problem I have is that this year they have added new product (which I know how to enter into the form that should be putting it in the table - which, in turn, should make it accessable by all reports, right?). The problem I am having are several:

In our Harvest/Product Summary Reports (I realize these names don't mean anything to you, but hopefully it will help in differentiating between the reports), I am able to see and link (in the properties window in design view) to the products that were existing, but I altered the name. However, the new products that I entered DO NOT show up. They show up in the form we use to enter the load information, but they do not show in the properties window - so I can't link these fields. When I look in the appropriate table, they products all exist as they should. However, in my xTab file ALL products that have ever been in any of the databases are listed. I can't change any of them because they are linked tables, so modifying them won't work.

In our Daily Carrier Load Sheet Report, Loads are pulled by Ship Date, show the load number, carrier, and is supposed to show what total product is supposed to be on that load. It is listed out by total product (i.e. if there are 8 drops and each has 2 pallets of D6P, it would show 16 D6P). This whole field is blank. All other information populates like it should. In design view, all that is shown is "Load Synopsis" in the field - but I can't seem to find WHERE the Load Sysnopsis is generated.

I am sure you will need examples, and I will work on getting those uploaded here ... but any thoughts or suggestions would be wonderful!

Thanks everyone!

[Delerna]

they have added new product (which I know how to enter into the form that should be putting it in the table - which, in turn, should make it accessable by all reports, right?).

If the database was designed correctly then YES it should!

I am getting my head around the rest of your post......more comments to come

[Delerna]

You have several issues here, lets take then one at a time and you may discover the solution to the rest as we go.

On your Harvest/Product Summary report there are several things that you mention that needs clatification.

I am able to see and link (in the properties window in design view) to the products that were existing

I guess you mean design view of the report but the properties window of what?

but I altered the name

Flesh this out with more detail, I am just guessing as to what you changed the name of???

However, the new products that I entered DO NOT show up

Show up in what??? I am not sure exactly what they are supposed to be showing up in, I know on the report, but what in the report?

When explaining your issues you do need to be as concise and to the point as possible but you must remember, we can't see your database, reports, forms etc.

So, be concice but also be complete.

[Delerna]

Just something that might help you.
If I was in your place this is how I might approach it.

Start at the report.
What is it bound to? a query, a table, what?
If the report is bound to a query
What tables is it querying?
On the report
What controls are on it?
What are those controls bound to?

Map all of these things, and anything else that might be usefull, out on a piece of paper, it will help you get an understanding of how the database has been designed and give you a reference for tracing through why something isn't working.

[Shannon West]

Hello Delerna!

I had a wonderful reply typed out to you explaining what I had found about the report (I figured taking one at a time would be easiest) and how I had made screenshots for clarification ... first my computer froze while trying to upload the screenshot file (too big I guess). Which irritated me, but! I retyped the response to you, hoping I was as eloquent as the first round. Only to be timed out and losing all the reply again ... I think I need a drink ...

I wanted to let you know that I got your reply and am working on a reply (again) and will post it as soon as I have it. In the interim, is there another way to get you the screenshot file to you? Its too big to attach to this post.

Thanks again so much for your help and in advance for your patience!

[beacon]

You should be able to put the screen shots in a .zip compressed folder and then upload that.

In case you've never worked with a .zip before, just right-click on your desktop or in a folder, hover over 'New', and then select 'Compressed Folder' from the choices. Then drag your screen shot onto the folder and post it here.

[Shannon West]

First Report to Work on

Hope everyone had a wonderful weekend!

Lets try one report at a time. I will refer to the Product Sheet this round. I opened last years database to start fresh and added the new products in the Products Form – which should add the new products to the Product Table. Which it did. And the new products show up in the Loads Form (this is where all load information is entered – Load Date, Carrier, Customers and Products) as product available for each load. The Loads Form is where the information is pulled for the Product Sheet, Summary Report and Harvest Summary.

On our Product Sheet Report, I added 3 fields to the report – one text box to label what product the column is referring to; one field to show the product that appears on the load(s) (each row is a load and the product totals show up in the various corresponding columns); and then a field to sum all the numbers in each column. The problem begins here. When I right-click on the second field so that I can link it to the appropriate product (see screenshot of Properties Window), the product doesn’t show up as an available option. In researching where the information is pulled from, it is pulled from the xTab Query.

I opened the xTab Query to add the new products, and they are not showing as options there either. So, I guess I need to figure out how to get the xTab to update with the new products that have been added, but how do I do that? Once I figure that out, I can fix the others, with the exception of the Load Synopsis on the Daily Carrier Load Sheet, but we can tackle that after this is fixed.

Thanks for all of your help!

[Delerna]

Hi Shannon

sorry for the delay, I have been away at a conferrence for the last couple of days.


There are no screen shots attached?

This statement

the product doesn’t show up as an available option

is a concern to me. I have the feeling that you might be a bit confused here.

In design mode, the product doesn't show up in the properties window, only the names of the product field will "show up".

For example
your product table might look like

ProdID,ProdDescription
1      Drill
2      Sander
3      Press
4      Grinder

If you now create a report bound to that table and then edit the report and open the properties of a textbox on the report.

I get the feeling that you are expecting to see Drill,Sander,Pr ess and Grinder as selectable options in its bindings property. This is not the case, you would only see ProdID and ProdDescription . The actual contents of those fields would ony be displayed when you run the report and never when you edit it.

Am I on the right track here? Is that what you were expecting?


PS
not that you should bind a report directly to a table

[Delerna]

You seem to be on the right track with the XTab query.
If you can post the code (or a screen shot) for it it might help me to understand your descriptions better.


My instinct is telling me that if the query is selecting from the products table then you should not need to add anything to the query to get the new products to come in.
It should have been designed so that if you add a new product it automatically comes in.

[Shannon West]

Screenshots

Good morning!

No worries at all - I appreciate your time!

Here are the screenshots - hopefully that will clarify some things. And I can answer any additional questions that pop up.

I think it shows up on the screenshots (if you don't see this reference, let me know and I will get a screenshot for you), the reports we are running break down by individual product, so the product names do show up in the drop down in the Properties window. We have other reports - such as the xTab - where the option is simply the Product ID and Product Description, but that is not the case on this particular report.

Again, thanks so much for this help!

[Delerna]

Looking at your screen shots
and comparing to your posts
will post back later

[Delerna]

My first guess is that the join conditions in your "XTabDailyCarri erLoadSheet" query are filtering your new records out.

Its difficult to read it as the tables aren't placed will in the snapshot but it appears

"Products" is joined to "DropDetail " on the ProductID field

and

"DropTable" is joined to the "DropDetail " on the LoadNumber and DropNumberField s

You need to check and ensure that matches exist for them.

Perhaps you should PM me and we can arrange for me to get a copy of your DB