This may be a simple problem, but I cannot find the solution myself.
I need to design a query based on a table describing all sales during a year to a number of clients of my company.
However , even if 50 sales may have been made during the year to some clients, I want the query to return only the top 3 sales for each client.
Any suggestions
Thanks
I am trying to filter a form to show the entire weekend's activity on Monday but only yesterday's activity Tuesday through Friday. Using this code I can return Friday's results on Monday and yesterday's for the rest. How do I get the range Friday to Sunday?
IIf(DatePart("w",Now())=2,Date()-3,Date()-1)
Using >Date()-3 doesn't work.
Thanks
Bruce
I have built a custom search form in a MS Access 2010 database so that users can find specific records to edit. After entering the search criteria and hitting a Search button, another form opens up that shows the search results. This second form includes a command button for generating a report of the search results.
Right now, the custom search form and the search results form are both working properly, but the search results report is showing every record in the database instead of just the search results. This is true whether I access the report via the command button in the form or the navigation pane. I'm not sure if I need to correct my VBA code or the report's properties.
I used to queries ,1 to get items that are taken ( its all about sign in sign out for equipment) and other query is list of all items.
How can i make 3rd query which will give me all but taken items from query1?
(of course items from query 1 are in query2)
thx in advance
How can I pull the last two values, grouping by property id, so I can compare them? I can use Max to get the last value but not sure how to get the previous one too!
View 1 Replies View RelatedHi Guys I'm not really sure wether this should be in queries or forms (as not sure where the code needs to go) but heres the problem. i have a front end form (where the user has various drop down boxes to choose from) which then runs a query and outputs the results in another form.
At the moment when there are no results it just brings up a big grey empty box, which is annoying since I want to use custom close buttons and disable the red x. Is there any way that if there are no results the query could open a different form (where I'd put some kind of error message and navigation choices).
Any help would be appreciated.
Hi All,
I made a form with a specific field. If I enter a value into this field it automatically runs a query. The results are shown in another form. However if I return to the previous form and enter the same (or another) value it still runs the query but this time with no results? I think it is a memory problem. The previous form is closing.
Does anyone have the answer to this one?
Thanks
I'm trying to run an SQL statement to Sum the values of a table column. No problem. It would be useful for me to take that total and be able to set it equal to a variable within my VB code that's running, I just don't know how to do that, I'm very new to VB programming.
I cannot find help anywhere on the internet for this. :( I'd like to do something like this, just don't know how:
VariableName = Run.SQL ("Sum(tblName.Column) AS SumOfColumn FROM tblName")
I'm not a programmer by trade, so I am picking up things piece by piece, and just cannot find my way out of this task. Thanks for your help guys.
I'm doing a table for Future Business Leaders of America competitive events assignments.
I want the same table to hold Name and Event Name and be able to sort
However, I want the database to be able to sort to tell me each event that a person has, and at the same time, how many people are in each event. Presently, I have to switch between databases.
On one table, the event name is the primary key, and on the other the person's name is the primary key.
For the event name table, categories are "Event Name" , "Participant 1" , "Participant 2", "participant 3", etc.
For the Person's name table, Categories are "Name", "event 1" , "event 2", "event 3", etc.
I want to have ONE database that will sort lists for all participants in each event AND that will sort into a report or form so that I can inform participants of their events.
How can I do this?
I probably was not very clear, so ask lots of questions if you're unsure.
THANKS A LOT!!
Im having a real problem in creating a query to return the results that I'm after. I have a wargaming Database. Units are created on seperate forms i.e Armour, Infantry etc. There could be a number of Battalions of each say Armour1, Armour2, Infantry1 Infantry2, the amount is endless. All of these battalions are linked to a game name i.e Battle 1, Battle 2 etc.
Im trying to show in a seperate table the name of each battlion that is involved. At present whaen I create my query I use the Arour Table and the Infantry table but if Ive only created 1 Armour unit and 2 Infantry unit, it shows the record twice. ie:
Battle 1 Armour 1 Infantry 1
Battle 1 Armour 1 Infantry 2
I Want Battle 1 Armour 1 Infantry 1 Infantry 2
Any ideas, a bad explanation I know but hopefully you understand!
I am wanting my query results to appear in my main form so that i can edit them, rather than a table the query is just a look up of my form that stores about 500 records and stores all the same fields
any help would be appreciated
thanks
Greetings all,
Below is a query were I can get eveything into tow columns but I would like to have the results in one column?
SELECT tbl_Admin_Company_Details.Companyname, tbl_Admin_Company_Details_1.Companyname
FROM tbl_Data_JobOrder INNER JOIN tbl_Admin_Company_Details ON tbl_Data_JobOrder.CompanyID = tbl_Admin_Company_Details.CompanyNumber, tbl_Admin_Company_Details AS tbl_Admin_Company_Details_1 INNER JOIN tbl_Data_Receival ON tbl_Admin_Company_Details_1.CompanyNumber = tbl_Data_Receival.OwnerID
GROUP BY tbl_Admin_Company_Details.Companyname, tbl_Admin_Company_Details_1.Companyname;
The Results are;
tbl_Admin_Company_Details.Companynametbl_Admin_Company_Details_1.Companyname
Company 1Company 1
Company 3Company 2
What I would like is something like
Company 1
Company 3
Company 2
Thanks in advance
rgmb
Hi,
Is it possible to change the resulting format of a new field (expression) within a query?
I.e. Instead of the resulting when running query being displayed results in decimal places but instead being displayed in percentages
Regards
I'm trying to run a query that has a 3 expressions within the query. I'm using the following:
Exp2-Exp1 this would be the results for Exp3.
The problem I am having is that I only need the results for Exp3 to be returned if they equal "0".
Any help would be appreciated.....Thanks
I have this table
AircratfID
Flight
Time
I have a aircraft logbook with 4 entries for page
I tried to build a query that shows
"Time until this page"
"flight time"
"flight time"
"flight time"
"flight time"
"Time to copy to next page"
Anyone? Thanks
I've created a database to keep track of all our Microsoft licences for the company.
Just to make sure we're fully licenced, to allocate where needed, and to help reallocate when we replace a pc.
I have 2 tables, 1 hardware (listing PC ID,location etc.) and 1 software (listing software type, software licences, and to which PC ID allocated).
What i'm trying to do is filter the pc's missing either an Office licence or a Windows licence or both, so i can base a report on the results.
I've tried but can't quite get it to work.
Any help would be greatly appreciated.
I have two database tables, one of information of members and one of staff. I want to do a query to show all members and staff that have the title of 'Mr' in the database.
There are only 5 staff details in my staff database at the moment, all with the title 'Mr'. There are 20 members details, 13 with the title 'Mr'. When i run the query i get only 8 names, 4 from the staff and 4 from the members.
The 4 staff ones i get are the 2nd, 3rd, 4th and 4th records.
The first record of the members field does not have the title 'Mr' and so it appears that this is blocking the first staff one to appear. And the members stop appearing after the 5th record too, so it appears they are stopped as there are no more staff records.
I hope this makes some sense to you, and if anyone could tell me how to get all the records to appear that would be great. Thanks.
Is there a way to write one query that will work for different criteria and is run by different buttons to produce bar graphs or a basic report depending on the button the user clicks??
View 5 Replies View RelatedHi all :o
I am working on a query but can manage to get it to do what I want.
I have a main table with a job_ID and a faults table which documents faults that occur related to that Job_ID the tables are related with a one to many relationship. I want the query to bring up all the jobs even those with no faults, at present it is only bring up those jobs that have faults, how do I include details of all the jobs and if there are no faults then that field is left blank, probably very simple to do but after working on this db for hours my brain is no longer functioning!! :eek:
Thanks
Jackie
Hi
I have a query which is made up of several other queries that total different fields (these are a base for a report) sometimes one of the queries will be empty and then the whole query is blank, how can I return 0 when there are no records to count in 1 query so that the results from the others still show?
Thanks
Hi All,
I have a existing database that I use for pulling data out of our companies data warehouse and I have noticed that when I run the query it is pulling information more than once. So I'll have 13 extra entries which then screws up my reports.
Thanks
Hello all,
I have a table called tblODF and within that table, I have these fields:
- ODFNumber
- ODFScanDate
- Status (Combo box: Pending, Complete, Licensing)
- LastFollowup
In my query, I have the same fields, except I added a calculated field.
I added DayCount to
Codecalculate:(Date()-[ODFScanDate]
This should calculate the number we've had the ODF.
What I want to happen is, let's say the status is Maturity, I want the DayCount to say 0. If it's pending, I want it to calculate the actual days.
Is this possible?
Thanks
please can someone help me with a vb code to view search results in a subform such that
double clicking search result on subform displays another form with details of the result.
Heeeeeeelp.
Thanks
I am running VBA that "deletes" a record by putting a 1 in a byte field called "deleted_b". But first it checks to see if the record is referenced anywhere in the database. So it may not actually mark the field.
There is no error if the "deletion" fails - the stored procedure runs regardless.
So I want the following code to run - basically it checks to see what the deleted_b value of the record referenced (Call_Attorney_ID) is. The problem is, I think I'm going about it incorrectly because I get a warning saying that a function or variable is expected in the IF statement - instead of the command to run the sql. Could someone tell me how to fix the code below to be the correct syntax?
Code:Dim mySQL As StringmySQL = "SELECT Deleted_B FROM dbo.MED_Attorneys_T WHERE Attorney ID = Call_Attorney_ID" If (DoCmd.RunSQL(mySQL)) = 0 Then MsgBox "Deletion failed. Record in use.", vbOKOnly Else Me.Requery MsgBox "Deletion has been completed", vbOKOnly End If
hi i need some advise here. I know a little about Microsoft Access and I am trying to organise a database.I have a table called receipts with column of firstname, last name,total cost, deposit, receipt, receipt#, receipt date. I did a calculated field in query to find balance due. My problem is adding all the receipts recieved from an individual to subtract it from the total cost to get the correct balance due by each individual. (Remember all receipt on the same table)
Could someone tell me how to organise it to get balance due.
Thanks
I've just started using Access 2007, but I've been an Access 2003 user for some years and have a couple of databases, both of which I've now brought over to 2007.
Something I've noticed and is probably very simple. In 2007, after I entered information in a table via one of my forms, I queried on that information but it wasn't included in the results. But everything else - from 2003 - was. I then went thru each field in the underlying table to "match" fields there were filled in for a 2003 record and now the new entry does appear in the query results.
This hadn't happened before in 2003 - it didn't matter if all or even particular fields were filled in. But now it needs for at least some particular fields to be filled in. Don't know if it's something different in 2007, or probably coincidental. I haven't changed anything in the table, query or entry form.
It's not a crisis (now that I see my results), but I'm wondering what is going on - now, that apparently wasn't occurring before?
Thanks!