I have a report that takes money values form several records and sums them up in various category's making a list of departments with the total amount of balance for each (positive and negative numbers). I need to categorize the positive values form the negative and calculate the totals of each and the grand final total (invoice style)
View RepliesTried to export a report to Excel using Tools>Office Links>Analyze It With Microsoft Excel menu. The order of the fields appearing in Excel doesn't seem to match the order on the Access report layout. What is the logic on how the fields exported? Thanks.
View 1 Replies View RelatedHave this table
ID | NAME | Progresscomplete | Release
1 | a | 20% | July
2 | b | 65% | July
3 | c | 33% | July
4 | h | 15% | Sept
5 | i | 30% | Sept
6 | r | 5% | Dec
7 | s | 50% | Dec
8 | t | 15% | Dec
9 | u | 20% | Dec
now I want to create a query that shows the Progresscomplete for each task as a percentage for a Release
so the total progress for each release is
July 300%
Sept 200%
Dec 400%
and the current level of progress for each release is
July 39.33% (118/300*100)
Sept 22.5 % (45/200*100)
Dec 22.5 % (90/400*100)
thus the current level of progress for each task within a release is
1 | a | 16.9% (20/118*100) | July
2 | b | 55% (65/118*100) | July
3 | c | 28% (33/118*100) | July
4 | h | 33% (15/45*100) | Sept
5 | i | 66% (30/45*100) | Sept
6 | r | 5% (5/90*100) | Dec
7 | s | 55% (50/90*100) | Dec
8 | t | 16.6% (15/90*100) | Dec
9 | u | 22% (20/90*100) | Dec
I have queries for the first two not sure about the last also
not sure if i should have the base of the last output be the current level of progress for each release or the total progress for each release
I have tried this for the last output
SELECT ID, NAME, ((Progresscomplete/Count(Progresscomplete))*100) AS currentprogresslevel, Release
FROM TEST_RawData
GROUP BY Release;
but get an error "tried to execute a query that does not include specified expression... as part of an aggregate function.
I created a db, tested it with dummy info and all was fine. I then imported live data from another db. When I came to add new records, discovered that, in the table design, a default value of 0 (zero) has been entered for fields where a look up table is used (where I had used Autonumber for the ID). Solved that easily enough, but later I amended a text box to a combo box and the same thing happened. Why does it do this?
View 2 Replies View RelatedAccess is making my brain hurt - can anybody please help?
Here's what I have and what I am trying to get it to do...
I have a table, maba_registrations, into which students are entered according to which course they are doing in which semester - eg -
autonumber student_id course_code semester year
1 1 A123 1 2006
2 2 A124 1 2006
3 2 A124 2 2006
4 2 A126 1 2006
5 3 A132 1 2006
What I need to do is to pull four subsets of data out of this table -
1) A unique list of courses that student x is taking in semester 1 (only) of a given year
2) A unique list of courses that student x is taking in semester 2 (only) of a given year
3) A unique list of courses that student x is taking over 2 semesters (both semester 1 and semester 2 of a given year)
4) A unique list of courses that student x is taking over 2 semesters (semester 2 of a given year and semester 1 of the following year)
Obviously there should be no overlap... and that's where I get a bit stuck
I can craft a simple query that will show me the courses a student is taking in semester 1 of a given year, but I cannot figure out how to get it to exclude those courses which are also still being taken in semester 2. In the above example, the query pulls out records 2 and 4 for student 2; I only want record 4.
I think what I need is to have 2 queries -
a) pulls out all courses for student x in semester 1 for the current year
b) pulls out all courses for student x in semester 2 for the current year
- and then subtract the results of query (b) from query (a) (ie remove the rows that are matched). Is there any way to do something like that?
Or is there a simple part of query syntax that I can use to make a query that just says "pull out all the courses for student x where semester=1 and there is no row for this course and this student where semester=2"?
Any ideas would be hugely appreciated!
Maybe someone knows the logic behind MS Access for the display of percents in the formating..... ie.... If you input 3 you get 300.00%. I really dont think ANY user would look at a form... with a field called...say "Commission percent" and expect an input of a 5 to return 500.00% Does this need to be corrected with coding?
View 2 Replies View RelatedHey guys, I'm a little lost in my logic here for some reason.
I have a table that has 6 dates. I'll label them as 1 to 6.
Date 1 > Date 2 > Date 3 > Date 4 > Date 5 > Date 6.
I would like to run a query to ensure that this holds true. Can anyone give me a little help on this?
Do I have to do an individual compare from 1 field to the 5 other fields 6 times? for example
Select if
Date 1 < Date 2
OR
Date 1 < Date 3
OR
Date 1 < Date 4
etc etc.?
I realize my newbness shows here...
Im trying to run a query that looks in a table to see if product a and product b were purchased and then return the id but i never get any data, and I know there are some results that should be showing up. If I remove either one of the products it returns data but not when both are requested. here's the query:
SELECT [Copy Of data].id, data.purchase_product, data.purchase_product
FROM data INNER JOIN [Copy Of data] ON data.email = [Copy Of data].email
WHERE (((data.purchase_product)="ProdA") AND ((data.purchase_product)="ProdB"));
what am i not getting?
I am looking for some direction on how to approach this.
I have a table that has a field call DocumentID and I don't like it. I would like to create my own.
In the same table I also have 10 fields of data called L1, L2, to L10.
I would like my new DocumentID to be the 10 fields concatenated together, with a period between them unless the value of the field is null.
If this is a query I need to then move the data back to a table in the same database.
Any suggestions on a good strategy/approach?
Thanks
I have been working on customizing MS ACCESS to
produce a report that involves one mathematical
computation from my input data. However, I've run
into a problem because I can't get the program to
carry out a logic command that would work fine in
MS Excel: the "IF" command. Specifically, what I'm
doing involves a calculation, where one data input is
subtracted from another data input, but if the difference
is a negative number, I would like to substitute the value
of ZERO. The closest I've come to achieving this is to
enter the condition ">0" while in Query Design mode, but
the problem with this approach is that if the difference
is a negative number, the report does not display the
data that resulted in the calculation of the negative number.
In other words, I want the data to be included in the report,
but I want a "Zero" to be substituted for all calculations that
produce a negative number. For example, if I enter a set of
data, and the two numbers that get subtracted are 40 minus
36, then the difference is positive 4, and so I want a "4" to
be displayed; But if the two numbers to be subtracted are
36 minus 40, the result is negative, and so I'd want the
report to display a "zero" in the calculation.
Please advise me on how to set this up, as the only approach
I've found resulted in all negative calculations being omitted
from the report.
Thanks for your help.
I am not sure if I understand this...
I have MainTable, on which I base MainForm. I would like to have MainForm show only the records that have a null value in CertainField. If I write NullQuery to select only those records, can I redirect MainForm to NullQuery? Well, I know I can do that... but how does MainTable get updated with new records if MainForm is based on NullQuery????
Any help is greatly appreciated.
Tom
I need some help with code. I can do this in Excel, but I am not sure how to do it in Access.
Here are the fields:
PoundsPerBox
PoundsPerOrder
BoxesToOrder
Here is the scenario: If a customer orders custom paint for their equipment, we need to total how many pounds of Powder Coat paint it takes to paint all the units (PoundsPerOrder). When we order this from the vendor we have to order it by the box which is measured in pounds. In this example, the vendor will sell us a box of white Poweder Coat paint in increments of 55 pounds. (55, 110, 165,...)
Verbally, the logic reads like this: If PoundsPerOrder is less than or equal to PoundsPerBox, then BoxesToOrder equals 1. This logic needs to be tested for each increment value (indefinately) of PoundsPerBox.
Am I making sense? How do I accomplish this in Access? And, as a side note, I need to capture and store this value in a table for future reference.
I would like to create a report based on a query. The first part is simple enough. However within this query I have a 'Count field' (a total of the number of duplicated this record has appeared in the table). And would like to create a subreport (if possible) based on that value for example. if count > 1 then show subreport.
My question therefore; is the above possible? How would I go about linking this logic with the subreport
Ok, here it goes:
Got these tables:
CompanyTbl:
CompanyID (PK)
Name
Street
etc.
NAICS1(this is a standard industrial code, a 6 digit number telling about their product/service)
NAICS2
NAICS...6 (company can have 1-6 NAICS codes)
A layman has no clue what these NAICS code are, so each and one of them has a descriptive title, like NAICS 32330 = Canned Vegetables Manufacturing
For this puropse, I designed a second table:
ProductTbl
ProductTitle (PK)
NAICScode
Purpose:1. Be able to query by ProductTitle and get all companies with that profile.
2. Be able to retrieve Company ID with all its NAICS code/s and corresponding ProductTitle/s.
Now one company can have up to 6 NAICS codes and one NAICS code can appear under the descirption of multiple companies. So a many-to-many relationship. But how? A third table, probably....But how?
In the highlighted line of code attached I am comparing two strings one from a snapshot recordset and one from an array.
The logic fails, you can see in the watches window both values equal "DESIGN and the elseif statement should execute but it does not.
The only reason I can see for this is that the values are equal to "DESIGN and not DESIGN. Where this single quotation mark comes from I do not know. It is not visible in the tables and it does not appear when the values are printed into excel.
Hi Everyone:
I am new to this forum as I recently started working in MS Access. I’m hoping someone can please help me with this query I’m trying to do using MS Access 2007.
This task is somewhat similar to a typical points/miles rewards credit card program.
The problem is as follow:
For every $2,000 of accrued purchases in a credit card, an individual will get a gift card.
So, any remaining balances over $2,000 will rollover and be added to next set of transactions, until it reaches $2,000 again. For instance, if on the first week I spent $2,500, I would qualify for my first gift card. The excess $500 would rollover and be added to the next set of transactions until they reach $2,000 again. Therefore, this would qualify me for another gift card.
So far, I’ve created two tables, one that stores accrued purchases (Promo History) and another table based on an Append Query which takes all qualified transactions from the first table and appends those records to the second table (Promo History Qualified) in order to track those qualified transactions over $2,000.
However, I’m stuck on how I would accommodate the remaining balances to be carried over. Any suggestions would be greatly appreciated!!
Thank you in advance,
marel
Record Primary Key: ID_Wells..The TxtFedStCo has Fed, St, Co, ... in one field - and a Dt_Apd_Sub (date submitted) .There can be zero or many dates submitted for each Fed or State.
Objective:
If
A Fed has (1 or many) date submitted
AND
A St has (1 or many) date submitted
Condition is TRUE
How do I get to the next step? The reason for doing this in SQL is to prototype in Access, then move this over to TSQL later.
I'm trying to create a new field based on two existing fields as part of a select query. Two tables in the query each have a "HOSPITAL" field which is an indicator for "Y" or "N" for each department (in the DEPTLOOKUP table) and for each facility (in the Facility Lookup table). I want to create a field whereby a new indicator is created so that a Y is given for each record only where the facility and department HOSPITAL indicator field is both "Y". I have attached a word document that has a screenshot of the query in design view as well as the access sql code.
View 3 Replies View RelatedDoes the MS Access Report support Hide/Show specific fields according to parameters or even by click?
View 3 Replies View RelatedI'v looking for since a couple months a go to make a report direct from access form using crystal report but i havent found it yet. I'v tried this code and its giving me errors. " run time error 1004 method range of object _global failed "
how to make a report using crystal report direct from ms access as front end application ? is it possible to use crystal report ?btw i use database sql server 2008 and MS Access 2007 as my frontend application.here's the code that i'v found and gives me an error
Dim CR As New CRAXDRT.Application
Dim rep As CRAXDRT.Report
Set rep = CR.OpenReport(Range(" ??? ")) * i getting error in this line, what should i do to fill it ??
rep.ParameterFields(1).AddCurrentValue "Boston"
rep.ParameterFields(2).AddCurrentValue "Cars"
rep.Database.Tables(1).SetLogOnInfo "tool", "db_tsel"
rep.ReadRecords
rep.PrintOut promptUser:=False, numberOfCopy:=1 ' promptUser:=True doesn't work
I have a form with 7 List boxes linked to 7 Query's which in turn are linked to a table. Each list box if for a particular trade.
I am trying to select a person or persons from each List box and then have them sent to a report. I have Code to do one list box, but do not know how to link all boxes with code to a 'Open report' button.
The code I am using is as follows:-
Private Sub cmdOpenReport_Click()
On Error GoTo Err_cmdOpenReport_Click
Dim strWhere As String
Dim ctl As Control
Dim varItem As Variant
'make sure a selection has been made
[code]....
The aim of what I am doing is to create a monthly statement to give to our intermediaries that shows the commission they will receive each month for the deals they have referred. I have managed to create this report, HOWEVER I can't figure out how to filter out which month I need, so I a report for Jan, Feb Mar etc... The idea is that at the end of each month I need to run the report so only the latest month shows...
View 3 Replies View RelatedI have a report which programmatically sets the value of some labels based on its own internal logic for each line of the detail section of a report. This all works fine and dandy, using the Detail_format event, and accessing detail.controls.item(x).caption.
HOWEVER, when I then embed the report as a subreport (which I need to do), I goes wrong. Here, I get the values of the last row of the detail repeated in every previous one. I'm suspecting because the parent report has its own 'detail' (I've tried giving the subreport its own distinct detail name).
How do I hide the report footer based on the report's data ?
I'm trying to hide if number of users = 1
The report's data is a query built inside the report's RecordSource, not a self standing query.
I've done this once entirely by accident and can't seem to duplicate it...
I have a report. It has the following:
Report Header: Logo and title
Department Header
Supervisor Header
Group Header
Detail
Department Footer: Totals
Report Footer: Overall Totals for all departments
Here's my question.
I have combo boxes on my main form that filter this report. The combo boxes are referred to by the query that runs the report. How do I get proper unfiltered overall totals in my report footer?
I have a report that displays incidents, their details, consequences and a photo. Among the details is a severity rating high medium or low, I have been asked to make the report shorten the records which have been given a low severity (because it takes up as much space as the more important/severe ones).
The only method I can think of is to use the onformat event, to shrink and make invisible all the fields that I don't want to see if the severity field shows 'low'.