Query To Display None Payers!
Sep 6, 2006
Hi,
Ive come up against a brick wall once again in my database so once again here we go.
I have my main from Members which contains a field traffstatus, this field will be filled in with either Green, Amber or Red. Green is some one who is up to date with there payments, Amber is someone who has not paid for 4-6 weeks and Red is someone who has not paid for 6 weeks or more. This will be calculated from the current date so some one who has not made a payment in 5 weeks from today will be amber. The payment information is stored in a table named download20060602. This contains 5 fields, ID / datepaid / description / type / amount. The description field is linked to the description field in the members table so that one member can have many payments.
So I need to know how I can make a query to fill in the traffstatus field on the members form using the criteria above.
Im assuming this would be done by a query but Im not sure.
Any help would be fantastic!
Cheers Phill
View Replies
ADVERTISEMENT
Apr 6, 2006
Hi I was just wondering whether there was a way to display results of a query in a listbox form or similar.
Thanks
View 1 Replies
View Related
Jan 17, 2008
i accidentally change a property of a query to hidden, i don't know how to make the query displayed again.
what should do?
View 3 Replies
View Related
Jul 28, 2006
I wish to display a message box, only if a query returns results. I would have it on the OlLoad portion of my form. The problem I am having is doing the sql in the vba portion of the code...it would look something like this:
sql = "select * from table"
if results of sql are not blank, then
msgbox("hello")
end if
Can someone help me please?
View 14 Replies
View Related
Aug 8, 2006
Is it possible to run more than 1 query and display results on the same form?
I have 1 form with a Record source of Query 1.
The control boxes can therefore just reference the query fields.
So, Control box 1 has =[selectedfieldfromQuery1] as its control source.
I need to have a second control box displaying the value of a different query from a different table.
So, at the moment Control box 2 has =[Query2]![selectedfieldfromQuery2] as its control source but Access doesn't recognise that.
ANy ideas on how to progress?
View 4 Replies
View Related
Dec 29, 2004
The query below return this data in it's rows:
Rank # Name Div Sex Score
1 8 Bird Twitty 52Kg Female 684.69
2 12 du Toit Carin 52Kg Female 608.77
3 13 Colbert Jackie 52Kg Female 607.62
4 11 Wade Jenna 52Kg Female 595.41
1 7 DeDee Jenna 67.5Kg Female 702.27
2 10 Diva Meisie 67.5Kg Female 550.46
1 5 Man Super 70Kg Male 655.09
1 9 Doe John 82.5Kg Male 480.3
1 3 Man Spider 90Kg Male 537.63
1 6 America Mr 100Kg Male 406.42
1 4 Hulk Incred 110Kg Male 645.8
SELECT (SELECT COUNT(*) + 1
FROM History H INNER JOIN
Lifter L ON L.uidLifter = H.uidLifterRef
WHERE L.uidClassRef = Lifter.uidClassRef AND
H.ScoreTotal > History.ScoreTotal) AS Rank,
Lifter.LifterNumber AS LifterNumber,
Lifter.Lastname AS Lastname, Lifter.Firstname AS Firstname,
Class.Description AS CDescription,
Activity.Description AS GDescription,
History.ScoreTotal AS ScoreTotal
FROM Class, Lifter, Team, History, Meet, Activity
WHERE (Meet.MeetDate = DATE ()) AND Team.TeamStatus = 0 AND
History.ScoreTotal > 0 AND
Class.uidClass = Lifter.uidClassRef AND
Lifter.uidTeamRef = Team.uidTeam AND
Lifter.uidLifter = History.uidLifterRef AND
Team.uidMeetRef = Meet.uidMeet AND
Lifter.Gender = Activity.Code
GROUP BY uidClassRef, Lifter.LifterNumber, Lifter.Lastname,
Lifter.Firstname, Class.Description, History.WeightScoreC,
Lifter.Gender, Activity.Description, Activity.Activity,
Lifter.LifterStatus, History.ScoreTotal
HAVING (Activity.Activity = 'GenderStatus') AND
(Lifter.LifterStatus = 0)
ORDER BY VAL(Class.Description) ASC, Lifter.Gender DESC,
History.ScoreTotal DESC
I would like to see the query only returns the top three of each group to help me determine the GOLD,SILVER and BRONCE position in each division. I know that I need a select in the WHERE part but how?
View 7 Replies
View Related
Mar 14, 2005
My mini database has 11 records stored in a table called “Products”. The table has two columns called “P_name” and “P_price. The records are stored in the following way and order in the database:
Counter P_name P_price
1 Shoes 15
2 Coat 16
3 Cardigan 4
4 Shoes 9
5 Cardigan 7
6 Trousers 15
7 Coat 8
8 Cardigan 14
9 Shoes 2
10 Trousers 12
11 Trousers 8
My question now is whether there exists any SQL query that will select all products and then be able to display the recordset in the exact order below (P_name should not be displayed in alphabetical order, but grouped together with other in same product category and therefore displayed after each other. P_price should be in ascending order). I have managed this far: SQL = SELECT * FROM Products where ?????? ORDER BY ?????, price ASC.
Coat 8
Coat 16
Trousers 8
Trousers 12
Trousers 15
Shoes 2
Shoes 9
Shoes 15
Cardigan 4
Cardigan 7
Cardigan 14
If it's not possible to achieve this using SQL, maybe you can suggest something else?
View 1 Replies
View Related
Nov 18, 2005
When I query my database I want to return groups of records. The groups differ from one another based on one field (Employee ID) -- So one group with Employee ID# 1 may have 2 records and another group with Employee ID# 2 may have 5 records, etc. My question is: What is the best way to display these groupings and allow the user to scroll or navigate thru the different groups easily?
Any help is appreciated.
Thanks
View 1 Replies
View Related
Nov 30, 2005
Ok,
I have a table with 3 fields Line address, Availability, Relevance Date
Every month i put in the data given and i have back from june (so i dont actually have 12 months.
At the moment i am grouping by line address(Row header) and datepart("m",[relevance date]) - (column header) and have the availability as a value and sum. Because i only have data from june it only gives me 6,7,8,9,10. How do i get it to always display 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12 and just put the availability into the relevant months and put nothin in the others.
Current SQL: -
TRANSFORM Sum(Monthly_cleansed_data.Availability) AS SumOfAvailability
SELECT Monthly_cleansed_data.[Line Address] AS LineID
FROM Monthly_cleansed_data
GROUP BY Monthly_cleansed_data.[Line Address]
ORDER BY Monthly_cleansed_data.[Line Address]
PIVOT DatePart("m",[Relevance Date]);
Thanks
k0r54
View 2 Replies
View Related
Jan 19, 2006
I have a query that querys another query for check boxes that are checked. I would like to display in a text box on a form the # of records that the query found to be checked. I have looked all day and have found nothing that I have enough knowledge to use. I need to know how and how to apply this. If you have other suggestions to do this, I am game! :)
Thanks!!!
View 11 Replies
View Related
Jun 12, 2006
Hi,
I have two tables of data, one is a customer information (membersdata) table and the other is information recived from a bank (bankdata). Each customer has a 'bank description' field in its membersdata table and the bankdata table also has field 'bank description'.
The query I have at the moment gives me back the data that both tables have a matching 'bank description'. The query I want is one that will give me the data from the bankdata table that does not exist in the membersdata table.
So simply put the query I want is the opposite of the one I made with the wizard.
I hope that makes sense?
Any help would be fantastic!
Cheers
Phill
View 9 Replies
View Related
Dec 19, 2006
I have a database which contains details of groups of students. Groups are assigned tutor(s) using a link table between the Groups table and a Tutors Table.
I want to create a query which shows each group with their tutor. If I add the three tables to the query I can correctly generate a list of groups with their tutors, but any group which hasn't got a tutor (i.e. where the Tutor column would be blank) doesn't feature in the results.
I want all groups to be listed, even if they haven't got a tutor. Is this possible? How?!
Thanks in advance,
Gary
View 2 Replies
View Related
Jan 7, 2007
Hi,
I'm trying to work out a formula in a query.
At the moment it looks like this:
Days on Hold: calcworkdays([on hold date],[off hold date])-1
Now i have a module thingy set up (calcworkdays) which works out working days. What this expression does, when theres an on hold date and an off hold date is work out how many working days something is on hold.
Now, problem is, not always is something on hold, therefore fields are often blank and then i get a result in query that says "#Error", but i want to use the answer to this expression in another formula, but when error is displayed it makes the other query show error too.
What I want is some sort of If statement or similar so that if no results exist to display "0". Can anyone tell me how to add this in?
I'm a total Access Noob, it took me forever to work out this working days thing (damn access for not being as simple as excel!)
I think my problem might lie in the way the function has been written. I think i might have to modify this to show "0", rather than "error"
This is what ive got in the function.
Public Function calcWorkDays(dteStart As Date, dteEnd As Date) As Long
Dim i As Long 'day counter
Dim dteCurDay As Date
'set i = 1 if you want the first date to count as a full day
'or i = 0 if you do not want the first day to count as a full day
i = 0
dteCurDay = dteStart
Do Until dteCurDay >= dteEnd
'check date against holiday table
If 0 = DCount("[HolidayDate]", "tblHolidays", "[HolidayDate] = #" & dteCurDay & "#") Then
'continue checking for weekdays
If Weekday(dteCurDay, vbSunday) <> vbSunday And _
Weekday(dteCurDay, vbSunday) <> vbSaturday Then
i = i + 1
End If
End If
dteCurDay = DateAdd("d", 1, dteCurDay)
Loop
calcWorkDays = i
End Function
Any help would be much appreciated!
Thanks
View 1 Replies
View Related
Jan 30, 2007
Hi.. I am a beginner using access and there is probably an easy solution for this which i dont know about!
I have made a query where i calculate the tax deductions for a payroll system. my problem is that different workers are to be taxed differently given maritial status and number of kids as well as net income.
I have managed to calculate everything with the kids and wifes but now i need to sort the table after how much they earn to deduct taxes. I have a "first stage NetPay field" now, where i can see the Gross pay minus allowances for wifes and kids.
Now, if that value(first stage netpay) is under 8840 no more deductions are to occur. If the value is between 8840 and 10000 i have to deduct 15% of the excess above 8840
If the value is between 10000 and 20000 i have to deduct a further 174+ 25% of excess over 10000
If the value is above 20000 i have to deduct 2674+ 31% of excess over 20000
So far what i have done is make a new coloumn, where i take "first stage netpay field" and subtract 8840. If the value is negative i thought i could show only the positive values by enterin >0 in the criteria field for the query, this however will make no posts show up..
(I tried entering Abs() in the Field coloumn where i made the expression, and this worked. )
So my question is: is there a function like Abs() i could use that would convert negative values into 0?
Or do any of you guys have a better idea of how to solve this?
Hope someone can help me...
Takstein
View 8 Replies
View Related
Aug 7, 2007
I have been doing a lot of searching on the forums, and I can't seem to find an answer to my problem.
I have a form that uses a query to create an e-mail. This e-mail lists all of the different tests to perform on a sample of a product. My table set up is as follows:
tblProducts
ProductID
ProductName
tblTests
TestID
TestName
tblSamples
SampleID
ProductID
TestID
The purpose of this is to send out a sample of a specified product and perform 1 to XX tests on it.
Currently, my query is giving these results:
Product Test
Product1 Test1
Product1 Test2
Product2 Test1
Product2 Test2
Product3 Test1
I would like for it to format the data like this:
Product Tests
Product1 Test1, Test2
Product2 Test1, Test2
Product3 Test1
Any suggestions on what I should do?
Thanks in advance!
View 2 Replies
View Related
Sep 28, 2007
I have a small stock control database built around a Northwind example. I am trying to build a query that displays items low on stock.
On my products form I have:
Min Order Level field (stored value)
Units on Hand (calculated value from products subform (units received-units sold)).
Q1: Do I use a query to calculate/display this?
Q2: How do I construct an expression to acheive this?
Any help much appreciated
View 4 Replies
View Related
Mar 14, 2008
Hello I have Supply order Database
I want to display combo box
after selecting the month the query will run
plz tell me how to do it.......
kindly reply
if any 1
thanks
View 3 Replies
View Related
Jun 22, 2005
I wish to print the result from query in a subform. In my main form, I had a textbox call 'year' which asking user to input a valid year. The query will have to find out all the records that are in that particular year, one year before and one year after. After that, it should display the result in datasheet form. Can anyone out there help me in this matter?
My second question is Can we use crosstab query in the subform?
Thank you.
View 2 Replies
View Related
Aug 12, 2005
I have a form that I want to use in order to work out a global figure for average travel time for my engineers (data coming from another database).
I have a form with two text boxes, which allow the user to define a date range. This links into my query which works fine and returns all records from that date range.
Now comes the troubling part!
On the click of a command button I want the label to change and display the average for my work time column form my query (which by the way is in decimal format).
Can anyone help me? I have tried searcing but to no avail
View 14 Replies
View Related
Oct 5, 2006
If there's a post out there that addresses this, please point me in that direction as I've had no luck.
I have my queries listed in a table. On my form I have them listed in my combo box. The user selects the query they want and hits the command button "Run". I would like to show the selected query in a subform on the main form and then give the user the option of selecting another command button to export the query to Excel.
I thought it would be as simple as setting the recordsource of the subform to the query name in my combo box, but that doesn't work.
Current code to run query in separate window:
Command button "Run"
Private Sub Command9_Click()
DoCmd.OpenQuery Me.Combo2.Column(1)
Attempt at getting what I want:
Privat Sub_Command9_Click()
Forms!frmQuerySelection!sfrmQuery.Recordsource = Me.Combo2.Column(1)
or perhaps
sfrmQuery.Recordsource = Me.Combo2
Am I even close?
Thanks
View 1 Replies
View Related
Jan 28, 2005
I have created a query with parameters keyed in from unbound textboxes on a form. Does anyone know of a way to display the results (queried) in the sub-form of the same form (main) the textboxes reside? I have the query set up to create a table but when I use that table (or the query itself) in the subform I get the error message:
"The database engine could not lock table "tablename" because it is already in use by another person or process."
This seems appropriate because I am querying for results, but is there any way to get the results of a query into a form, rather than the more common pop up datasheet? I have tried several macros to open and close the tables - queries - copied tables ..re-opened forms....etc, but can't get it to work.
Thanks, for any help, I have spent some time on this one!!!
View 4 Replies
View Related
May 13, 2005
Hi,
I have created a basic query, in MS 1997
Would someone be kind enough to give me the code for the following,
I need to display only the last 13 weeks worth of dates in this query.
The table is called "Tbl_History_RotAwaitShip"
The field is called "Time/Date" and is formatted as "13/05/2005"
Thankyou
View 2 Replies
View Related
Jul 28, 2013
How can I display the count value from a query on a subform for the related/selected record?
I've tried a DLookUp without success (the text box remains empty):
=DLookUp("[CountOfMethodID]","Methods count","[MethodsRungVisitID] = Form![Form4b]")
Here's my table/query/form info:
Query is called "Methods Count"
Field of that query which I want displayed on the sub form is "CountOfMethodID"
Sub form is "Quarters and peals sub"
Main form is "Form4b"
Both form and sub are parent/child linked
There is a Relationship between the VisitID field in the "Visit Dates" table and the MethodsRungVisitID field of the "Methods Rung" table.
View 1 Replies
View Related
Sep 22, 2014
in my query i have multiple tables, one table just has one field called product thats joined to another table the other table displays a list of all products, if the product is in the other table.i want a "X" to display in that field both columns are text the IF im using is,
IIf([Product] Is Null,"","X")
but when i run this, it displays no records what so ever, if i take out that iff, it displays all records i want, but that field is the product number when i need an X
View 4 Replies
View Related
Jan 15, 2014
I have a table where I record attendance. In the table they select the type of training in via a check box "Training, Drill or Other". I created a query to show how many times someone has attended the Drills in each quarter (counts the check box). I am wanting to be able to specify which year is displayed as atm it is showing all te quarters for years. The SQL is below.
From this I have a report where it displays the data and have conditional formatted it so that if they have not attended more than 4 times in one quarter the "cell" turns red - the only problem is is that some of the cells have no data in it. How can I get the query to display 0 if there is no data?
QUERY SQL:
TRANSFORM Sum(Abs([ATTENDANCE 2011].DRILL)) AS SumOfDRILL
SELECT [MEMBERS LIST].[NUMBER/NAME], Sum(Abs(Nz([ATTENDANCE 2011].DRILL,0))) AS [Total Of DRILL]
FROM [MEMBERS LIST] INNER JOIN [ATTENDANCE 2011] ON [MEMBERS LIST].ID = [ATTENDANCE 2011].ATTENDED.Value
GROUP BY [MEMBERS LIST].[NUMBER/NAME]
PIVOT "Qtr " & Format([DATE],"yyyy/q");
View 1 Replies
View Related
Aug 8, 2014
I have a separate form in which I want to use as a loading screen for my users while the query loads on the other form. Now I can do this with text easy but not with a .gif..I have the loading form to
Code:
Private Sub Form_Timer()
DoCmd.Close acForm, "Loading Screen", acSaveNo
DoCmd.OpenForm "Form1", acNormal
But it just stops the .gif and then runs the query before opening Form1.
View 7 Replies
View Related
