Allan,
Agreed - I was trying to be diplomatic. Since these report features take precedence, massaging queries may be fruitless.
Allan,
Agreed - I was trying to be diplomatic. Since these report features take precedence, massaging queries may be fruitless.
Sorry Jack. I should have known that. I just wanted to make the facts real obvious. The report does not mess with the Where or Having clauses so the OP should be able to modify the query to limit it to just what they are desire.
I'm not convinced that the OP has a clear picture of what is required. There still seems to be some confusion re requirements/outputs etc.
I always find it difficult, but helpful, to describe in detail what is being asked. Often posters jump over the details and try to "implement" a - not too well thought out concept - in physical Access. As you try to describe the details, the light usually goes on
and you realize there are pieces missing, there another concept/entity needed, the logic doesn't address XXX etc.
As someone said, if you can't describe the issue/opportunity in clear, simple terms, then you probably don't understand it as well as you think.
I've re-created the query but here is the issue:
If I remove TRANSTYPE as a field, I get all of my 82 project numbers non-redundant.
However, if I include TRANSTYPE as a field, which we want to include so we can see all 82 project numbers as well as the ones that are an " L", we get redundant records (More than 82). Attached are screen grabs.
Can you post a copy of the database -- do compact and repair, then zip it?
Remove anything confidential first.
Also, can you define in clear terms what you are trying to show in the Query or Report?
It is possible that your data structure may not be normalized and is causing issues(redundant records).
In one jpg I see multiple records for 91179 and 91526 for example, but each has a different Transtype.
In the one above I see multiples for 91402 but they each have different SumOfQTY. So I must ask, what makes your records unique, or what exactly were you expecting for this "NUMBER"?
You should also be aware that Name, Number and Code (and possibly others) are reserved words in Access.
Here's a link to a list of reserved words provided by Allen Browne.
@orange
https://app.box.com/s/wytlcjrgx2v6u1ra4mr5p8qr9opeexjs
projmast.mdb is the one we're working with
The goal is to see every single project record we have with a STATUS of A-H (Theres 82)
...in the order of field names that's it's currently in (NUMBER, TRANSTYPE, SALESMAN, PROJPODATE, PRODUCT, DATEIN, DATEDUE, STATUS, DATECOMPL, NAME, DESCRIPTIO, HOURSLABOR, QTY(Sum), TOTAL_PROJ, TOTALBILLD & also being able to see all the Transtype L's (Excluding P and X) without redundancy. So basically 82 records with the addition of Transtype L records in the same report.
We want to be able to generate a report using this query that we're having so much trouble with that updates automatically each time a new project is entered that gives us the total hours for labor (QTY is posted hours) down at the bottom of the report.
Last edited by orange; 07-21-2017 at 09:07 AM. Reason: added png
Should be able to download it
210 MB ??? Did you Compact and Repair before zipping?
Compacted it, didn't know what "Repair" meant
Allan,
Were you able to download the file? I see it is 210 mb, but get an error message when I click download.
newuser,
Can you decipher this
What exactly is the criteria? All Transtype L's (excluding P and X)???...in the order of field names that's it's currently in (NUMBER, TRANSTYPE, SALESMAN, PROJPODATE, PRODUCT, DATEIN, DATEDUE, STATUS, DATECOMPL, NAME, DESCRIPTIO, HOURSLABOR, QTY(Sum), TOTAL_PROJ, TOTALBILLD & also being able to see all the Transtype L's (Excluding P and X) without redundancy. So basically 82 records with the addition of Transtype L records in the same report.
We need to see the L's
We dont need to see P and X
Specifying a transtype criteria makes it not show all the project records which we need to be able to see
Allan et al.
I just downloaded the zip??
There are 4916 files in the zip??
It appears that most are older dbf (dbase3/4??) and indexes; pdfs, bmps... going back to 1998???
This is an ancient database
So the "BackEnd" is dBase or FoxBase? I did get the file downloaded. Which accdb are you trying to get to work?