Search results

Ok. I'll try your suggestions and let you know how I get through

Actually DwgNo C330 has 2 revisions - C1 & C2

The primary key is just an autonumber. I didn't want to set the drawing number as the primary key as different design consultants tend to use similar numbering systems

Revision C6 is for a different drawing. Right now Drawing Number C330 is the one with a revision C1 & C2. I need to see all drawings but only the last revision for each. So for drawing C330 i want to only see revision C2 listed

The query I'm having trouble with is qryDrwgRegisterSummary

At present there is no sensitive data in my DB - just entering test info to test the queries. Should I upload the database?

I tried both suggestions but no luck. Below is the SQL SELECT qryDrwgRegister.tblDrwgRegister.DwgID, qryDrwgRegister.ContractName, qryDrwgRegister.IssuedBy, qryDrwgRegister.DwgNo, qryDrwgRegister.DwgTitle, qryDrwgRegister.tblDrwgRegisterDtls.DwgID, Max(qryDrwgRegister.Rev) AS MaxOfRev...

Hi. That didn't work either. Both drawing revisions still showing.

Hi, I am creating a drawing register database for a construction project and would like to see the latest revisions for each drawing. Right now I have a drawing with two revisions - revision C1 was issued March 3rd and revision C2 issued March 28th. In my query I changed the group by to Max...

This method worked perfectly...thank you so much for your assistance. I really appreciate it! Definitely saving this article.

Thank you :)

A different issue has arisen. There are 4 sizes of water tanks but only 2 sizes were delivered in January. When I try running the report I am getting an error message stating that the access database does not recognize the [tank size] as a valid field name or expression. Is there a way for a...

Thank you! I made the changes but was still getting the #Name? error message. However, I ran a basic query (not a cross tab query) and used that as the domain and the forumla with your recommended changes worked. thanks again!

Hello, I have a report that is based on a crosstab query which shows the deliveries of water tanks by year and month. The row headings are year and month, column headings are the different size water tanks and the value is the qty received. For this new year, I would like the 2022 deliveries...

Yes...you are correct. For every 1000-gal system, two 1000-gal tanks are installed. I too queried why it wasn't referred to as a 2000-gal system but that is how the contract was issued so I have to use those references in my DB. So when I was asked this week to confirm the number of tanks...

Thank you for your input but we have 182# 1000-gal SYSTEMS to install. Each 1000-gal system will consist of 2x1000-gal tanks. There are no 500-gal tanks. The tally is per system as each system will include tanks as well as pumps, plumbing and electrical fittings, etc. However, with regards...

Thank you again for your help! This is counting the tanks per system as I needed. I can now use this for the next week's reports.

Thank you. I will try and let you know if it works. I was able to send off the other reports this morning thanks to your great assistance. I used excel to create the report with the actual sum of the tanks but I will try your suggestion so next time I can run it in Access

Hi again. I need help with one more thing. Where ever we have to install a 1000-gal system, the candidate is receiving 2# tanks per 1000-gal system. I'd like the DCount formula to include a criteria that would look for "*1000-gal*" and multiply by 2 so I can get the actual number of tanks...

I can't thank you enough for your help on this. I changed the source query as you outlined above, removing all the Dlookup formulas. Then in the group footer I added the formulas =Sum(IIf([BaseInstallationDate] Is Null,Null,[CountOfSystems])) and =Sum(IIf([TankInstallationDate] Is...