I wondered if someone could help
I am querying a table using a select query. The data I am pulling from the table is in format:
Period Year Value
0 2006 1000
1 2006 100000
2 2006 500
3 2006 5000
4 2006 50000
5 2006 200000
In my query, I wish to seperate:
Period 0 + value
from
Periods 1-5 which should be cumulative.
Thanks
Paul
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 ?
Any help greatly appreciated.
I have the following 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 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?
Ok, hope you can help me with this one.
I have a select query that pulls data from one table that includes the following data
MOS Grade Required Authorized
I have a separate count query that pulls data from another table and counts the following data
MOS Grade Assigned
How do I combine these 2 queries so the results I get will be
MOS Grade Required Authorized Assigned
For instance:
Select Query Says
MOS Grade Required Assigned
25C E4 1 1
42A O3 1 1
38A E7 3 3
Count Query Says
MOS Grade Assigned
25C E4 1
42A O3 1
38A E7 2
I want the results to read
MOS Grade Required Authorized Assigned
25C E4 1 1 1
42A O3 1 1 1
38A E7 3 3 2
Hope you can help.
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?
View 2 Replies View RelatedHi,
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.
I have the following data.
Source
ID , Product , Price , Count
1 , F2 , 10 , 6
1 , F3 , 20 , 7
1 , F4 , 30 , 5
2 , F2 , 10 , 6
2 , F3 , 20 , 5
3 , F4 , 30 , 5
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)
Result
ID , Product , Price , Count
1 , F4 , 30 , 5
2 , F3 , 20 , 5
3 , F4 , 30 , 5
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.
Many thanks
Tim
Hello,
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...
Does anyone know how I could do this?
Thanks :)
Hi
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
Any ideas?
Thanks
Ben
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.
tblPartNumber
qryFront
qryRear
qrySide
What I have now
PartNumber______Front______Rear_______Side
12345___________asdf
12345_____________________asdfs
12345______________________________asdfsaf
What I want
PartNumber________Front________Rear_____Side
12345____________asdf_________asdfs____asdsaf
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?
Thank you,
Kyle
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 have set up a query that includes:
An assignment start date
An assignment end date
and in addition a column has been set up to show me a Start Date where Assignment End Dates are Null i.e all live assignments.
When I enter a date only results for that particular date are visible.
Can anyone suggest how i can expand this to include all live assignments?
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);
}
PLEASE HELP!
Hello:
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.
Field name in query = "DAYS LATE"
ISSUE: (in vb view)
Dim Value1 as String
Dim Value2 as String
Value1 = Date() - [mDueDate]
Value2 = [mActReceiveDate] - [mDueDate]
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.
Thanks!
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.
Bobby
Hello everybody!
Can any body tell my how to make select query from 3 different tables.
Let say:
SELECT * FROM table1, table2, table3 WHERE id(from first table)=1
ID from table1 raleted to ID in table2 and table3
in this case there is same number of rows returned from each table. I need to know how to avoid this.
Tanks in advance
Andriy
Hi friends,
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 '-'.
How to do this?
please any suggestions?
Thanks.
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.
Hello,
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"
Thank you for your help and time.
I have a table which contains three fields, case number, member number and age.
Some case numbers have several member numbers associated with it.
I want to select the distinct case numbers where no member is <= age 18
Can someone point me in the right direction. I'm attaching a screen print of the table.
Thanks