Auggghhh!!

[Sergeant22]

I am trying to get a report out of Access 2000 and am having a difficult time. This report is for aquatic organisms. Here is a basic outline of what I want

Class: Gastropoda
Order: Lymnophilidae
Family: Physidae
Physa gyrina 6

Here are the problems
1) Order is stored in another table and is selected on the entry form using a combo box. The correct name shows in the table but the report draws from but the report only gives the Id number not the name

2) Everthing is double spaced making it difficult to read single spaced would be preferable.

The records for this report are selected using a query that chooses entered job numbers

Any assistance on this would greatly help I am lost.

Thanks in advance
Mike

 

[Chris RR]

First of all, make a copy of your report and the query underlying it to play with... then try this:

1) You need to add another field to your query so that the query contains the actual name of the order, not just the ID. I don't know your table setup, but you may need to do a join to get to this. Look at the code for the combo box & that should tell you where it picks up the order name. You'll need to have that field in your query.

Once you get the query right, you can add this field to the report.

2) Spacing can be affected by lots of things.

Does the spacing look "right" in the design view of your report? If not, drag & drop until it does.

Access has the ability to expand & shrink lines, and you may want to check out the can grow & can shrink properties. Sometimes data can be stored in a way that makes fields expand...when all you see on the report is spaces.

Now...very important "Things I've Learned in Access":

1 Remember, I told you to take a backup...bitter experience speaking here.

2. Get the query right before you try building a report. More than half the battle!

3. The wizards are only the beginning. It's not in the least bit unusual to have to do a fair amount of modification after you've built a report with the Access wizard.

[This message has been edited by Chris RR (edited 12-12-2001).]

 

[Sergeant22]

Ok I tried this but still I get the id number for the order. When I run the orginal query I get the correct name in the field, but when I run the same query in a report then I get the id number from the order table.

Let me explain the table set ups.

There is the main table, creatively called main, that stores all the data for the taxonomy of the organism and how many were found in the sample. The orders are drawn from a separate table named Orders. The field on the main table was set up with the lookup wizard, choosing the names from the orders table. Therefore, Orders are stored on a separate table and they are chosen on the entry form from a combo box. This was done to ensure the correct spelling of the orders, and I would like to convert all other fields to combo boxes for the same reason. Needless to say I am reluctant to do this given the current problems.

As far as the layout is concerned, I set up the report with the wizard and have done some modification on the layout, but because the each taxonomic level is within it's own header, I cannot get Access to go to single spacing.

 

[Chris RR]

Let's assume that the two fields in your Orders table are "ID" and "name". In your report, is the Control Source for the text box where you want to see the name, actually set to "name"? If you don't see the "name" field in your field list (go to "VIEW", click on "Field List"), then you need to modify and save the query, so that "name" is output from the query and is actually coming into the report.


Is it the header that's messing up the report spacing? Put your detail info into the detail section & shrink down the header & see if that helps.

Good luck!

As to design: I don't usually do my lookups in the table design, and I don't bother with "relationships" either. I'd rather do my lookups and joins as needed. Personal preference, but I think it can be faster (you don't always need all this baggage every time you work with a table...) I do believe that it's a great idea to make extensive use of lookups.

 

[Sergeant22]

I checked that the control source is "order". It is. Still get wrong data there.

Being a novice at this I am not sure what it is that you were saying at the end of your last posting. I'm not sure I know what you mean when you say "lookups and joins as needed". I though that setting them up in the tables were the only way to do this