473,322 Members | 1,755 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Multiple Report Issues within single Database

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!
Sep 2 '10 #1
11 1430
Delerna
1,134 Expert 1GB
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
Sep 2 '10 #2
Delerna
1,134 Expert 1GB
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.
Sep 2 '10 #3
Delerna
1,134 Expert 1GB
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.
Sep 2 '10 #4
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!
Sep 3 '10 #5
beacon
579 512MB
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.
Sep 3 '10 #6
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!
Sep 7 '10 #7
Delerna
1,134 Expert 1GB
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
Expand|Select|Wrap|Line Numbers
  1. ProdID,ProdDescription
  2. 1      Drill
  3. 2      Sander
  4. 3      Press
  5. 4      Grinder
  6.  

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,Press 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
Sep 10 '10 #8
Delerna
1,134 Expert 1GB
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.
Sep 10 '10 #9
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!
Attached Files
File Type: zip Access Issues Screenshots.zip (564.6 KB, 81 views)
Sep 10 '10 #10
Delerna
1,134 Expert 1GB
Looking at your screen shots
and comparing to your posts
will post back later
Sep 13 '10 #11
Delerna
1,134 Expert 1GB
My first guess is that the join conditions in your "XTabDailyCarrierLoadSheet" 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 DropNumberFields

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
Sep 13 '10 #12

Sign in to post your reply or Sign up for a free account.

Similar topics

16
by: noah | last post by:
Does PHP have a feature to associate Cookie sessions with a persistent database connection that will allow a single transaction across multiple HTTP requests? Here is how I imagine my process: I...
10
by: Eric S. Johansson | last post by:
I have an application where I need a very simple database, effectively a very large dictionary. The very large dictionary must be accessed from multiple processes simultaneously. I need to be...
0
by: halsa | last post by:
hi I need to append multiple reports to a single report.For example I have 2 Pending Invoice reports to print.The report viewer has to preview these two reports like two different pages and...
1
by: Ken | last post by:
Help! I have a MS Access database made up of a single table. The data updates for this database comes in the form of a single Excel spreadsheet, which is imported into the database table. I...
4
by: Jeff | last post by:
We have multiple ASP.Net web apps in development. As a standard we are looking to go with SQL Server to hold state information. Can we have the multiple apps all point to a single State DB? Or...
4
by: paul.chae | last post by:
I have a table in Access with about 3000 records. There are ~60 unique values in the ID field for the 3000 records. What I would like to do is automatically generate multiple Excel worksheets...
1
by: graham.dreyer | last post by:
HI all, I have a problem at the moment where i need to be able to create, edit and delete records in multiple tables in a single access database, not sure if it would be better to use SQL or...
7
by: Birky | last post by:
Is there a way to have Access enter multiple entries to a database based on a form where the user has selected a certain criteria? I am hoping to have my form enter multiple entries within a table...
3
by: Marcin Kalicinski | last post by:
How do I use multiple Python interpreters within the same process? I know there's a function Py_NewInterpreter. However, how do I use functions like Py_RunString etc. with it? They don't take any...
4
by: JoyceBabu | last post by:
Can anyone plz tell me the advantages and disadvantages of using multiple databases / single database for all tables. I have a site with more than a 100 tables. Current all the tables are in a...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.