The old inverted comma problem (1 Viewer)

[John Sh]

What I'm trying to do:
I have some 700 boxes with various numbers of botanical specimens in each box. I need to know the exact number of specimens per box.
The "Main" table is a list of 11,000 specimens by "Family" and each specimen has a "BoxNo" associated. I have included a subset of this table.
The "Short" table is a grouped list of "Families" and "BoxNo" taken from the "Main" table.:
The "Counted" table is a repository for the count results by "Family"

My problem:
The "Dcount" line refuses to behave itself. I have tried countless variations of comma combinations and seem to end up with 'Syntax" , "Type" and various other errors.
Can you please correct the punctuation in that line.
I am wide open to suggestions of a better way to achieve the desired outcome.

 

[GPGeorge]

Please explain more clearly. You want the total number of specimens per box in the "Main" table PLUS the total number of specimens per box in the "Short" table, correct? And the box numbers in the Main table refer to the same physical boxes as the box numbers in the Short table, correct? So if you count specimens per box in each table, you can add those totals together to determine the total per box, correct?

 

[GPGeorge]

Please explain more clearly. You want the total number of specimens per box in the "Main" table PLUS the total number of specimens per box in the "Short" table, correct? And the box numbers in the Main table refer to the same physical boxes as the box numbers in the Short table, correct? So if you count specimens per box in each table, you can add those totals together to determine the total per box, correct?

As alternative explanation, I might guess you want the total number of specimens of each type in each box, not just the total number of specimens. Would that be interpretation to use?

 

[arnelgp]

you can just use Query for your purpose without maintaining a "repository" table (Counted table) and without using VBA.
see FinalQuery.
see also qryFamilyBoxShort query.

Note that all Boxes for family, Myrtaceae has 0 count since it is not in Main table (your VBA is counting on this table, DCount).

 

[John Sh]

you can just use Query for your purpose without maintaining a "repository" table (Counted table) and without using VBA.
see FinalQuery.
see also qryFamilyBoxShort query.

Note that all Boxes for family, Myrtaceae has 0 count since it is not in Main table (your VBA is counting on this table, DCount).

Once again you save my skin.
Given that table "Short" was already a grouped subset of table "Main" is "qryFamilyBoxShort" required as it seems to be recreating what was already there? In that case the 'FinalQuery" could use "Short" as the table instead of the other one. Partially answered myself here, it's not a table but a query> DUHH.
I've had a little play with it, adding the Infrafamily and it is exactly what I was looking for.
Hopefully, as my limited knowledge of Access VBA grows, I will call on your expertise less frequently.
In the meantime however, if you're ever in Toronto, Australia, I owe you a dinner.
Many thanks.
John

Just as a matter of interest, what was wrong with the dcount line?

 

[John Sh]

you can just use Query for your purpose without maintaining a "repository" table (Counted table) and without using VBA.
see FinalQuery.
see also qryFamilyBoxShort query.

Note that all Boxes for family, Myrtaceae has 0 count since it is not in Main table (your VBA is counting on this table, DCount).

I have removed "qryFamilyBoxShort" and replaced it with "Short" and appended the result to "Counted" as we need an ongoing record so we can incorporated new specimens into existing boxes without lots of looking and not overloading already full containers.
Your assistance has been most helpful.
John

 

[arnelgp]

and appended the result to "Counted"

you also need to add index (no duplicate) to counted table (index on family, infra and box).
so that you won't have duplicate when you append new records to it.

 

[John Sh]

you also need to add index (no duplicate) to counted table (index on family, infra and box).
so that you won't have duplicate when you append new records to it.

No duplicate indexes are not really appropriate since the Family, infrafamily and box numbers do repeat often
I'll finish up with a form that will access families via a combo box. I will also modify "qryFamilyBoxShort" to run direly off the "Main" table.
The "Counted" table will remain empty until required or I'll do away with it altogether.
You've given me lot's of food for thought.

 

[arnelgp]

No duplicate indexes are not really appropriate since the Family, infrafamily and box numbers do repeat often

you include the BoxNo. will the 3 fields duplicate also?

 

[John Sh]

you include the BoxNo. will the 3 fields duplicate also?

With the box number there should be no duplicates but is multiple indexes worth it for a 600 line table. The response is almost instant as it is.
Also it is my intention to remove all data from the counted table when not in use and reload it as necessary. This way it will always reflect any changes that may occur.
A question. How do you get qryFamilyboxshort to show up as a table in the qryfinal design?