I want a report to tell me a percentage of records. i have a report that records "records that are past date". I want the report to tell me the percentage of total records that are past date.
I have a query that shows only past date records
and a query that shows all records
how do i do this?
I created the following expression on a report, under the details, in a text box:
=nz(pathjbo50,0)/nz(pathjbo50,0)+nz(pathcol50,0)+nz(pathpp50,0)+nz( pathlm50,0)
what I want is to find out what percentage pathjbo50 is of the sum of the other 4 fields.
but I keep getting an "Error" message. How do I get this percentage?
Where is the best place to find literature on writting expressions?
I have a table (tbl Team Info) which contains names and codes for teams within my business (>400 records) and another table (tbl Process) which contains a list of high level tasks (30 records).
I need to create something where for each team name 9in tbl Team Info) I can map them to the tasks that they undertake (in tbl Process) and assign a percentage of time then spend on each task. Each team could map to several different tasks.
Is it possible to only show a percentage, like 5% of records using a query? Ideally i would like my query to show a random 5% sample from a table, that would be great. Is this sort of query possible?
View 3 Replies View RelatedI'm trying to calculate the percentage change between records/rows. I've searched around the internet and found 2 possible examples, but they are very specific to the individual's database and I'm struggling to understand.
Here are two examples:
[URL] ....
[URL] .....
I believe I need to create a query based on another query (which I can do). I would need to create a column in the new query that calculates the percentage difference, but what's the formula/contents of that column?
I have a table with the following fields; Row; AnimalID; Weight: Date_Weighed (see attached screenshot example).
I would like to make a query that gives me the percentage weight loss/gain since the last time a particular animal was weighed. For example, animal AAA gained 100% between 2014/01/01 and 2014/02/01. Animal CCC gained 0% between 2014/02/01 and 2014/03/01.
I plan to use this to create a pop-up that informs me if an animal had lost over 15% of its bodyweight since the last time it was weighed, indicating either a loss of body condition, or that a female has given birth.
So I have a report with the following text box controls:
[Surname] & ", " & [Firstname]
=Sum([Quarter1_A]) - Named "Quarter_Total"
=Sum([Quarter1_T]) - Named "Quarter_Target"
=Val([Quarter_Total])/Val([Quarter_Target]) - Named "%Target" (Percent Format)
The report is grouped by the expression '[Surname] & ", " & [Firstname]'.I am trying to sort the records by the %Target text box. I tried entering the expression into the sort function but it still sorts by the grouped expression. I also tried sorting by the name of the text box but got the same results. How can I sort by the desired control?
Hi
I'm have created a video database with a genre field. Is there a way that when i click a button a list is displayed of all the records which are the same genre?
Thanks
Aden
How would one go about comparing 2 tables and finding those records that are not common to both tables? For instance, I have a table that I suspect some records have been accidently deleted by the user. I would like to compare that table to an older version of the table, and list only those records that exist only in the older version of the table.
Thanks.
Feeling stupid today...how can I query a table to find the last five unique records?
Table1:
PrimaryKey
Field1
Field1 may contain duplicates. I need the five latest entered unique records in Field1, and am struggling to write a query for this.
Many thanks to anyone who can help out.
[Still using Access 97]
Iv'e looked through several date functions but cant figure out which one to use or how. I want to produce all records for a period of 1 year, for 1 year ago. i.e if I run the query during any month of the 'current year' (obvously) if its after August it will show me the previous year of August to following July. My Start date is always August 1st of any year through to the following July 31st. It is relatively straight forward (I think)if it's from January to December because year part is the same but this becomes more complicated when you cover two different Year parts. I dont want to have to use a parameter Query of 'start date' and 'end date' I want to be able to run the query based on the system date now(). Any help would be appreciated thanks.
View 7 Replies View RelatedHi, I have a table contains 5 fields are shown as below, how I can write a query to find out the duplicate of the records? I have created a couple of queries by using Find Duplicates Query Wizard to do it. But the results are not accurate enough.
Field 1: ID
Field 2: Trading Name
Field 3: Location No
Field 4: Location Street
Field 5: Location Suburb
IDTrading Name Location NoLocation StreetLocation Suburb
41350Arabica Café Grey St
263Arabica Cafe` 1GREY STREET
41294Arashi Japanese Resturant41Courtenay PL
150Arashi Kushi Yaki Bar41COURTENARY PLACE
From the data sample, I could like to find out the duplicated records. One of the queries is shown as below. But they cannot pick up these records. Any better way to find the duplicated records? Please help.
SELECT DISTINCTROW [C&B-will].[trading name], [C&B-will].[location street], [C&B-will].Field1, [C&B-will].[location no]
FROM [C&B-will]
WHERE ((([C&B-will].[trading name]) In (SELECT [trading name] FROM [C&B-will] As Tmp GROUP BY [trading name],[location street] HAVING Count(*)>1 And [location street] = [C&B-will].[location street])))
ORDER BY [C&B-will].[trading name], [C&B-will].[location street];
I have 2 tables. I want to find all the records between the two that match and then replace the contents of another field with a value such as "*" to signal the matched records.
If I were using dBase I would set a relationship between the two and repl all "fieldname" with "*" for seal = seal.
I have no idea where to start with this in Access. I was thinking it may be a query but I am not sure. It seems that if there is an Unmatched Query there may be a matched query. The replace seems to be more what I am trying to figure out how to do. I have searched on Replace but all I come up with is the basic Find/Replace.
Any help would be greatly appreciated.
Heather
How to find the first 2 max numbers from set of records.
View 1 Replies View RelatedIs there a way, aside from using auto number, to find a records postion. I am using a list and want to be able to double click an entry and have it open a form and go to that record. However I can't use auto number because I allow for the deleting of records, and once deleted the auto number no longer corrilates to the records position.
If anyone knows of a different sytem that renumbers after deleting a record that would be great if you could share it with me. Or if there is a way to open a form and use goto record but instead of using record postion use where statement.
If anyone has any other ideas on this please share. Thanks.
Hi all,
I thought this would be relatively simple task to complete but its proving tricky.
I have two tables with the same data: E1 and E2
E1 has two columns and is as follows
A 1
B 2
C 3
D 4
E2 is similar except for one record '5'
A 1
B 2
C 5
D 4
Now the task is to show those records that do not match...in this instance
E1.Field1 E1.Field2 E2.Field2
C 3 5
Now I've used the find unmatched wizard and this is the SQL:
SELECT E1.Field1, E1.Field2, E2.Field2
FROM E1 LEFT JOIN E2
ON E1.Field2 = E2.Field2
WHERE (((E2.Field2) Is Null));
this is giving me partially the right answer as in
E1.Field1 E1.Field2 E2.Field2
C 3
Anyone else think of some other way?
Hi hope you can help me out,
I have a query which combines several linked tables, the query has about 10 columns, I need to show only the records which have one or more empty fields.
Some records may only have one missing field others may have several.
Any ideas?
Many thanks,
Ed
Hi.
i.e..............
Table A has records 1,2,3,4,5,6,7,8,9,10
Table B has records 1,2,3,4,5,6
How do I create a query that returns values 7,8,9,10 for Table B when I compare Table A and Table B?
What sort of query do I need? I tried the "Unmatched query" but this did not get the result I wanted.
Thanks for reading.....
Frank.
Hello,
I am trying to generate some query result on a form.
My DB consists of the following fields : tradeID and profit/loss.
How do i make a query to find the Maximum consecutive number of wins and losses in the database and display the results on a form?
Would really appreciate it if any of you have tried to sort and display results for finding consecutive number of events in any database!
Thanks a lot
I have a table 45 fields. There is a field ID. I would like to get the records where fields contains "No". I would not like to create 45 queries for each field check. how can i generate a table which shows ID, Field Name that contains value "No".
View 4 Replies View RelatedI have the two tables in my access DB:
Property Information
Lease Information
I need to write a query which shows where a property does not have an associated lease, or where there is no current lease associated with it.
Property Info:
PropertyID
Address
Lease Info:
LeaseID
LeaseStartDate
LeaseEndDate
PropertyID (linked field to property table)
How to show expired / not current leases, but can't seem to show where there has never been a lease on a property.
I've got 2 tables, same structure, one [T-temp-Target] holds number of training units split by module a trainee needs to finish the course, the other [T-temp-Actual] holds what they've completed so far.
Both tables have structure
TRAINEEID
MODCODE
CountOfUnits
I'm trying to find the modules that they've not done yet so I can add up the units for them, only modules that have been started are recorded in the table of what they've done [T-temp-Actual], modules they haven't started yet aren't included in it. Here's the SQL
Code:
SELECT
[T-temp-Target].TRAINEEID,
[T-temp-Target].MODCODE,
[T-temp-Actual].MODCODE
FROM [T-temp-Actual] INNER JOIN [T-temp-Target] ON
([T-temp-Actual].TRAINEEID = [T-temp-Target].TRAINEEID) AND
([T-temp-Actual].MODCODE = [T-temp-Target].MODCODE)
WHERE ((([T-temp-Actual].MODCODE) Is Null));
I have a database for work where I have a table of meters and a table of Faults which has a list of all faulty meters at one time. When a fault is repaired, I have a macro which updates the Meter Status to Working, adds a Fault Closed date, appends the record to the Closed table and then deletes it from the Faults table.
The user runs this from a form by clicking the Closed Fault button which activates the macro. I've added Echo on and off to hide that the form is temporarily closed while the Append and Delete queries are run and then it is re-opened again.
My problem is that the Form always opens at the first record in the Faults table. I would like it to open to the record which would have been next after the one that has been moved to the Closed Faults table.
Below is the code I have been using to test the Copying Meter Reference, closing and opening of the form and finding the correct record:-
Function CopyTest()
On Error GoTo CopyTest_Err
Dim strMeterRef As String
DoCmd.SetWarnings False
DoCmd.GoToRecord , "", acNext
strMeterRef = Meter_Reference
[code]....
As you can see I am trying to go to the next record, copy the Meter_Reference by setting it to strMeterRef and then Find strMeterRef when the Faults form is re-opened.
I have a Macro embedded in a button which calls the above Function by using RunCode but nothing happens.
I'm having a very strange problem with access. In vba I have an sql statement "select StudentID, CourseID from tblAttendance where AttendanceDate = #Whatever Date#".
Now this works for all dates (as far as I can see) before 01/07/2013 but after that it just doesn't find any records. I have manually added dates and checked and it didn't work.
How this could be possible? The date field is not in any relationship and it is Date/Time format. I don't even know how to go about troubleshooting this as it makes no sense to me.
It thinks these records have a date below 1/7/2013 and it can find dates after 1/12/2013.
Well, I'm trying to enforce referential integrity on one of my relationships. But it seems that there are 5 records in the Subtable that don't have a corresponding record in Table, so it won't work. I tried copy/pasting from Subtable to Table, and it says 'unable to make changes because this would make duplicates in the primary key' to all values except 5. Which it asks me if I want them pasted. If I click 'yes' or 'no' nothing happens either way.
Well, anyway, I need code for a query that selects all records from Subtable.Field1 that don't exist in Table.Field1.
Hi, I have a cross-ref table (called MFC_CIBC_XREF) which links a bank account to a fund number and a general ledger number. It should be a unique relationship, wherein (the "=" means corresponds/links to)
Bank Account # "=" Fund #
For each bank account, there are sometimes multiple currencies being used, so each currency then links to a general ledger account, such that
Bank Account #.Currency = Fund #.General Ledger #
Sorry, if that's kinda cryptic, but here's an example:
Bank Account - ABCD123
Currencies operated in - CAD, USD
Fund # - F30
Based on the above, ABCD123 "=" F30, and including the general ledger numbers, ABCD123.CAD "=" F30.100, ABCD123.USD "=" F30.8121.
Basically, it's a way to keep track of not just the transactions for a particular bank account, but for the transactions in a particular currency.
Anyway, as I said, it's supposed to be a 1-to-1 relationship: each BankAccount.Currency should correspond to 1 and only 1 Fund.GeneralLedger. However, there are some entries in the table which have each BankAccount.Currency corresponding to multiple General Ledger numbers. Using my example above, ABCD123.CAD has two records in the table, one corresponding to F30.100, and another corresponding to F30.8101. This was probably because F30.8101 used to be linked to a different bank account, that got merged (ie, it might have been linked to ABCD124.CAD that then got merged into ABCD123.CAD).
Now I want to run a query on MFC_CIBC_XREF and find all the records where for each BankAccount.Currency, there is more than one Fund.GeneralLedger. I don't really know that much SQL, and even in Design View, I'm not sure of the statements to use. Any help is much appreciated.