I have tried a fee different methods but none of which are giving me exactly what i need, i was hoping some one out there might be able to suggest a good approach.
From this i want to be able to get the rows per ID with the highest price (count is inrelevant i am just indicating that there are other fields in the table)
I have tried various combinations of the Max command and it works fine if i just select the ID & Price but i need it to display the product related to that highest price.
Anyone got any suggestions for me, they will be greatly received.
I'm fairly new to Access. 's various select queries containing useful and useless results. I want to create a select query that will pick out all the useful figures into a 1 row table that can then be pasted into Excel.
e.g Existing Select Query 1 returns 1 row showing Average Age, Average Price, Total rainfall Existing Select Query 2 returns 1 row showing Average Weight, Average Salary, Total snowfall Existing Select Query 3 returns *2* rows: It returns Distance from London, Hours daylight and population for Town A and Town B
I want a select query that returns 1 row showing (6 items):
Total rainfall, Total snowfall, Town A Distance from London, Town A Population, Town B Distance from London, Town B Population.
I've been able to handle getting Total rainfall and Total snowfall. But I cant figure out how to get Town A Distance from London, Town A Population, Town B Distance from London, Town B Population to appear in the same row of the same query results as Total rainfall, Total snowfall.
(This is a modified repost - which hopefully makes sense) I am using Access2003. I am trying to set up a fast method of creating a union query. I have a jobs table that stores info about jobs with a separate table for each job that pulls together info from elsewhere when a review is conducted. The tables are as follows (and are linked from a paradox DB) :-
Table Name: jobs JobID (J000001, J000002, etc) Status (Live, Filled, etc)
Review Tables
Table Name: J000001 / J000002 etc Consultant: (Joe, Terry etc) ObjectID: (RoberI, SmithJ etc) Status: (H, P, D, R etc)
The jobs table contains information about jobs, including a unique code (JobID) that identifies the job. There is also a status filed that tells us whether the job is Live or closed etc.
The first time a job is reviewed a new table, a review table is generated, and the name of the table is the same as the JobID for that job. So Job J000001 has a review table with table name J000001 etc. The review tables may contain information with the same ObjectID (as they are unique fields from a third table – the candidates table)
I would like to generate a union query for all jobs in table jobs with a status of live. I can do this manually, if I review a list of live jobs, with the following sql expression;
SELECT ObjectID, Consultant, Status, "J000001" as [JobNo] FROM J000001 UNION SELECT ObjectID, Consultant, Status, "J000002" as [JobNo] FROM J000002 UNION SELECT ObjectID, Consultant, Status, "J000003" as [JobNo] FROM J000003;
I can then append the info into a new table. However this query is run at least twice a day and things change.
I would like to know is there a means of automatically generating sql for the union query based on results of a query of the jobs table ?
SELECT UnionTable.groupby, UnionTable.SeqID, UnionTable.Actual FROM (
SELECT VAP1.groupby, VAP1.SeqID, VAP1.SomVanbedrag as Actual FROM qryVoorplaatActualPillar_Forecast AS VAP1
UNION
SELECT VAP2.groupby, VAP2.SeqID, VAP2.SomVanbedrag as Actual FROM qryVoorplaatActualPillarIST_Forecast AS VAP2) AS UnionTablewhich is two select queries called UnionTable and a wrapper. Access handles this very well. Until you look at the SQL statement. If you don't pay attention Access stores the next query SELECT UnionTable.groupby, UnionTable.SeqID, UnionTable.Actual FROM [
SELECT VAP1.groupby, VAP1.SeqID, VAP1.SomVanbedrag as Actual FROM qryVoorplaatActualPillar_Forecast AS VAP1
UNION
SELECT VAP2.groupby, VAP2.SeqID, VAP2.SomVanbedrag as Actual FROM qryVoorplaatActualPillarIST_Forecast AS VAP2]. AS UnionTable Now when you want to execute the query you get an errormessage. Has anyone seen this before? Is this a known bug or is it a feature? Is there a workaround?
I have a crosstab query built on a select query.It works perfect. However, when I try to use the "Between [BegDate] And [EndDate]" it tells me access engine can not recognize it.If I use this in the actual select query it works fine. But when I try to use the crosstab query it gives me the above error.
Also, I'm using two undbound textboxes on a form for [BegDate] and [EndDate]. I have the syntax correct in my select query and it works fine. But it won't work with the crosstab query.
I have a multi slect list box (simple) and I need to find and select an item using vba - e.g., the bound column is the ID field and I need to select a specific ID (which will be different each time) as opposed to selecting the 100th record for example. How do I do this?
I am trying to run a select query which I can't get to work.
What I want to do is select a purchase order number from table1 then (possibly using a subquery?) do another select where it will return the most recent version number (from table2) for the purchase order number returned in the first select statement...
I have a simple select query that pulls data from a table in a sql db. the query shows all records for "agent1". if agent1 has made numerous entrys it shows all of them
how can i get it to just show the total of all entries?
i've tried selecting the sum option in query design but it says data type mismatch in criteria expression
Ok I'm having a problem running a query, below is description:
TableA has columns RecievedDate ResultsID
TableB has columns SentDate SentID
TableC has columns ResultsID Sent ID
I want to have a result of all values in Table A that have a value of Recieved Date is less than SentDate in TableB, I can link up through table C but I'm not sure on what way would be the best to do this?
I've created a database that we use to manage our sales orders. Two of the tables are 'Orders' and 'orderdetails'. The order table stores the order header info, such as customer name, account number, etc and the orderdetails table stores the products, qnty's, price, etc. They are linked with a one-to-many relationship using the orderID field.
I want to run a select query to list the orders1111 that include productID = 57 (thats the easy part) but I also need to list of the other order lines on the selected sales order.
I am trying to setup a query where all the data related to one item is in line but all I get are sets of blank spaces and shows the main item three different rows with one item that is related to it one three different rows
So what I am trying to do is have a column for the part number, front, rear, side and for the part number all the related data will show in one row instead of one 3 different rows with one piece of data in each row.
Sorry if this is a basic question. I'm new to Access and Visual Basic. I'm trying to get a select query to work sort of as a conditional.
My code is PHP Code:If ("SELECT TOP 1 RF_Connectors.ppl FROM RF_Connectors WHERE (((RF_Connectors.my_part_num)=Forms![Supplier Part Number]!my_part_num));" = "Yes") Then
I can't however seem to get it to work. I also set up a query using the design wizard that is the exact same, and when I run that I will get the correct result displayed.
Any idea why this isn't working?
Also say I set up: strppl = ("SELECT TOP 1 RF_Connectors.ppl FROM RF_Connectors WHERE (((RF_Connectors.my_part_num)=Forms![Supplier Part Number]!my_part_num));"
Would that fill strppl with whatever the field ppl contains?
im new to microsoft access and i need help with a easy problem.
i made a table with Salary information and stuff. and i made a select query, now i need an expersion to calulate a new Salary from the old one with a 5% increase.
I have a subform containing a list of Funds and attributes such as Asset Type, Fund Manager, etc.
Currently, I have a textbox, where the the control source is set so that it will be updated with the Asset Type from the subform.
I also have an unbound combo box that contains a list of Asset Types queried from a table via row source, where user can select the Asset Type.
What I would like is when a record is selected from the subform, the Asset Type is selected on the combo box as a default value. User can select another Asset Type if required. How can I do this?
Hi, is there any (reasonably simple) way to select or deselect multiple items from the List Box with individual clicks without using Ctrl key. Eg first click on an item would select it leaving all other items as they are, subsequent click on the already selected item would deselect it etc. I hope this is not too confusing and I would appreciate some help. Thanks!
I don't know if this post is valid here but I thought it might be so here goes. I have a MYsql database and I am developing a Perl script that runs a query against this database to output all the registrations and colours of the make and model of the car selected in a form.
I think my problem lies in my select query, I hope someone here can spot the error as it is driving me nuts, I know there is a problem because if I swap the fields registration and colour around my results change.
Anyway here is the query, at the moment this only outputs one registration number and no colour per query.
if (!($query = $dbh->prepare("SELECT registration, colour FROM car WHERE make = '$make' AND model = '$model' "))) { &outputErr("couldn't prepare statement".$dbh->errstr); }
I have an issue I've run into and have searched around and tried to resolve. The issue is that I have a table that is updated once a week with new data, calculations are performed on new fields, etc...Ok, no problem on that. Oh, to give you a quick overview, the table is for Open and Received orders. Ok, the new field is Actual Received Date. Well, if open, obviously it's not received. If received, it's placed in that field, if Open, that field is blank for that record. I need to create a SELECT QUERY, would save TONS of time here if possible, to do an IF THEN ELSE type aspect that basically, if Actual Receive Date is present, then use this calc, if not, use that calc, etc...ANY IDEAS? Take a look below for additional information. The following are ONLY EXAMPLES to give you a better understanding of the issue.
1 Table, 1 Query, 2 Fields, IF THEN ELSE EXPRESSION within SELECT QUERY under 1 Field.
If IsNull([Tables]![tblOrdersMain]![mActReceiveDate].value) = True Then [Query]![Query]![DAYS LATE].value = Value1 Else [Query]![Query]![DAYS LATE].value = Value2 End If
Well, definitely need HELP! Any questions or anything please let me know.
After reading some of what people already had in this forum, I was still left a little confused. Say I have a form with three combo boxes and I want the user to be able to select something from all 3 or just one at a time. Which kind of query should I be using. Right now I have a select query, but it doesn't work right due to the Where Clause, which will work 1 way or the other, but not both(that I know of). Any advice would be appreciated. Thanks.
I had two tables parent(f1) , child(f1,f2,f3). I had to display f3 value for parent(f1) when f1 has only one f2, if f2 values are more than one for single f1 and if all f3 values are 0 then i should display f3 as 0, else if some values for f3 are nonZeros and some are Zeros then is should display f3 value as '-'.
i have a query based on 3 tables, 2 tables (table 1 & 2)contain 10k records each, the 3rd tables (added very recently) only contains 3k records. My problem is that when i run a query to show all relevant data from the 3 tables it only will show the most recent records (the 3k from the new tables) if i remove table 3 it will show all 10k records..
i need the full query to include the 10k records. it is a basic query with no criteria at all.
I am using Select Query in my project. Query selects and counts records from the table that satisfy specific conditions.
First I built a new query in Design view and it worked, gave me the results I wanted.
Then I opened that query in SQL view, copied SQL code(Select Count () AS....From.....Where...) and tried to use it inline:
Dim conDatabase As ADODB.Connection Dim rstAppointments As ADODB.Recordset Dim strSQL As String Dim RECNUMBER As String
Set conDatabase = CurrentProject.Connection
strSQL = “SELECT COUNT (DATE) AS RECNUMBER FROM APPOINTMENTS WHERE (((DATE =[Forms]![NEW APPOINTMENT]![Calendar0]) AND (TIME =[Forms]![NEW APPOINTMENT]![cmbTIME]) AND (INTERVIEWER =[Forms]![NEW APPOINTMENT]![cmbINTERVIEWER])) OR ((DATE =[Forms]![NEW APPOINTMENT]![Calendar0]) AND (INTERVIEWER =[Forms]![NEW APPOINTMENT]![cmbINTERVIEWER]) AND (TIMEFINISH =[Forms]![NEW APPOINTMENT]![cmbTIMEFINISH])) OR ((DATE =[Forms]![NEW APPOINTMENT]![Calendar0]) AND (TIME =[Forms]![NEW APPOINTMENT]![cmbTIME]) AND (INTERVIEWER =[Forms]![NEW APPOINTMENT]![cmbINTERVIEWER]) AND (TIMEFINISH =[Forms]![NEW APPOINTMENT]![cmbTIMEFINISH])) OR ((DATE =[Forms]![NEW APPOINTMENT]![Calendar0]) AND (TIME <[Forms]![NEW APPOINTMENT]![cmbTIME]) AND (INTERVIEWER =[Forms]![NEW APPOINTMENT]![cmbINTERVIEWER]) AND (TIMEFINISH >[Forms]![NEW APPOINTMENT]![cmbTIME])) OR ((DATE =[Forms]![NEW APPOINTMENT]![Calendar0]) AND (TIME <[Forms]![NEW APPOINTMENT]![cmbTIMEFINISH]) AND (INTERVIEWER =[Forms]![NEW APPOINTMENT]![cmbINTERVIEWER]) AND (TIMEFINISH >[Forms]![NEW APPOINTMENT]![cmbTIMEFINISH])))
'(cmbTIME, cmbTIMEFINISH. Calendar0, cmbINTERVIEWER - input fields on a form, DATE, TIME, TIMEFINISH, INTERVIEWER-fields of the table)
Set rstAppointments = New ADODB.Recordset rstAppointments.Open strSQL, conDatabase, adOpenDynamic, adLockOptimistic
if RECNUMBER>0 then
my code here
end if
rstAppointments.Close conDatabase.Close Set rstAppointments = Nothing Set conDatabase = Nothing
Unfortunately, the same query does not work. I am getting message:"You did not provide one or more values for required parameters"
