[X]My Assistant

[Sarah4uk]

Hi
I've spent all day trying to creat a report, and failed. I'm not Access savvy, and our database was made years ago by someone who i can't ask for help....

Basically i print labels for plants, using the data below
 1.jpg ( 584.9K )Number of downloads: 5


When the database was set up, it was made with an interface like
 3.jpg ( 218.04K )Number of downloads: 7

to select what plants/how many labels i need

making
 2.jpg ( 219.54K )Number of downloads: 1


Now; i would like to set up a semi-automated process for printing some labels.
CatGenusIDRef and CatSpeciesIDRef are number linked to words (which i need the words printer, not the numbers)
But i also need VarietyName and Description to be printed... so far so good...
 4.jpg ( 589.81K )Number of downloads: 4

BUT i would like it to print the number of labels for each (2020), and order them by Auricula Let - there are several different letters, and i want to print all the 'a's on one coloured lable, all the 'd's on another etc...

I have no idea how to write code, and everything i've tried has failed....

I'm sure its a super simple fix; but i just can't get my head around it!!!!

Many Thanks if you got this far..
Sarah

[GroverParkGeorge]

Welcome to UtterAccess.

Your labels need to be based on a query that joins the appropriate tables and the appropriate fields from those tables.

BTW: when you use the phrase "number linked" and when I see that screenshot, it gives me the impression whoever created this Access Relational Database Application used Lookup Fields in the table. This is generally considered a sub-optimal approach, but remedying that problem might be more trouble than it's worth.

To confirm this assumption, please open the table in design view and look at the property sheet for the field called CatGenusIDRef. I'll bet it looks like this, with a reference to a different table in your Access Relational Database Appplication:  Lookupfieldintable.png ( 27.53K )Number of downloads: 2


To make your labels work, therefore, you'll need to include that referenced table in this lookup field in the query that provides records to the label, and make sure the text value is included.

[Sarah4uk]

Yes; you're right with the lookUp - the database was set up in about 1998; and with imput from the website man, who uses the data to generate the website; very complicated - and Bloody Merlin is still lurking there too...

But the lookup can't be simple, can it...
 1.jpg ( 410K )Number of downloads: 4

[GroverParkGeorge]

Okay, a qualification first.

Normally I urge, implore and nag people NOT to use lookup fields in tables and to revert back to the standard approach instead.

In this case, I'm not so sure. You've been working with this data for a long time, and perhaps it's safer to make the smallest change needed to get the result. In other words, don't necessarily make the change.

Instead, include the table "Genus" in the query that provides records for the label. Open the query in design view and drag Genus into it from the navigation pane. The query designer should automatically find the join between Genus.GenusID and Catalogue.CatGenusIDRef, but if not, add it.

Now, drag the field Genus.GenusName down into the query design grid so that it is now available to the query and the needed field on the label. The same process can be used to add other related fields from other tables, if needed.