Creating Query
Aug 18, 2007
I have 2 coloumns
Size------------Sale (in bottles)
750------------900
1000-----------450
750------------30
90-------------80
1000----------60
90-----------57
Now I want to create a query containing one more coloumn which is
Sale (in cases).
If size =750 it divided by 12,
If size =1000 it divided by 9 and
If size =90 it divided by 100
and this value save in another coloumn Naming Sale (in cases)
please help me creating a query on this
Thanks
Ashish
View Replies
ADVERTISEMENT
Mar 18, 2008
HelloBascially I am trying to make a query to check if certain Parameters are met over all in the table then add up records.For example i have a record set up likeBob Brown|Access Programmers|18/3/08|1Bob Brown|Access Programmers|17/3/08|5I would want to make a query compound those records up in a report (e.g the compounded values don't need to be saved just displayed)If the records name field is the same and the dates fall in a certain range then the records will be compounded together e.g the numbers being added upSo the end result for that example would beBob Brown|Access Programmers|6Because the records name and "Access Programmers" is the same and they fall within a certain date range the records are compounded togetherMy question is, Would you make a query to do that or can it be done with a report. Bascially what would be the best way to do it.:)Thankyou In Advance!
View 4 Replies
View Related
Aug 12, 2005
I have a form (frmTraveler) in which a user is given a selection of levels for a type of trip from a drop down box (TripLevel). Basically, if a user picks level A, then I would like a value to be written to the corresponding record in another table (tblHotel). The PK is an autonumber (TravelID) and the tables have a one to many relationship. Hope that's enough information for some tips! :)
View 1 Replies
View Related
Nov 25, 2005
I have the following two querys which are supposed to return only the latest Note for a Prospect.
QRYNotes
SELECT TBLNotes.ProspectID AS Expr1, Max(TBLNotes.NoteDate) AS MaxOfNoteDate
FROM TBLNotes
GROUP BY TBLNotes.ProspectID;
QRYNotes2
SELECT DISTINCT TBLProspects.CompanyName, TBLNotes.NoteID, TBLNotes.Note, TBLNotes.NoteDate
FROM (TBLProspects INNER JOIN TBLNotes ON TBLProspects.ProspectID=TBLNotes.ProspectID) INNER JOIN QRYNotes ON TBLNotes.NoteDate=QRYNotes.MaxOfNoteDate;
When I run the QRYNotes2, I get more than one record per Prospect instead of just the newest NoteDate.
What is wrong?
View 3 Replies
View Related
Mar 20, 2006
Hey :)
is it possible to create a query for 1 table and for the same query to retrieve some data from another table
Cheers
Tom Evans
View 2 Replies
View Related
Nov 27, 2006
Guys,
I am trying to create a parameter query to be able to access from 5 different tables data on a product that has been delivered to retail stores between a range of dates.
I need the query to have the ability for the user to enter the product number and the range of dates to activate the selection.
Any ideas?
View 1 Replies
View Related
Jul 3, 2007
Hi guys,
Please would some be able to help me?
I have created a query (its a holiday chart). The table the query is created from shows dates taken in each of the 12 months under the 'month name' column and then in the next column it has the total for each of the individual month.
For example,
Field 1 (Jan)
Field 2 (total Jan)
Field 3 (Feb)
Field 4 (Total Feb)
Field 5 (March)
Field 6 (Total March)
and so on.
Then at the end I have a field for amount left. So what I need the query to do is add all the numbers in each individuals record in all the 'total month' column and then show this in another column which is my 'amount taken' column.
Thank you in advance for your help.
View 1 Replies
View Related
Aug 10, 2007
Hey, I'm a VBA Newbie. I have a query that pulls information from my form as criteria and i want to create a table with the results...I am having trouble searching online for a solution and Access Help is no help. Is there a way I can do this using vba?
View 1 Replies
View Related
Oct 19, 2004
Sorry about the simple question, but my brain is not functioning today. How do you create a report from a simple query?
View 2 Replies
View Related
Nov 24, 2004
Dear Friends,
I am creating a table using query, i want to mention the following criteria
1) Required
2) Allow Zero Length
3) Indexed
I could able to give only NoT NULL criteria. How to give other criterias in query.
regards
shruthi
View 1 Replies
View Related
Jan 3, 2005
I need to create a query that will output the number of times doors have been used during time bands of a day.
I have no problem getting the info I want from the doors that have been used (in other words where there is a record in the field) however, how can I ammend the attached statement so that it reports no records as a "Zero"? in the same format always ie,
North Entrance, 2
South Entrance, 4
East Exit, 0
West Entrance, 3
I need the data in this format so that I can export it to excel.
Here is the SQL view of the query I am using
SELECT [Staff Entrance].[Door Location], Count([Staff Entrance].Time) AS CountOfTime
FROM [Staff Entrance]
WHERE ((([Staff Entrance].Time)>#12/30/1899 2:30:0# And ([Staff Entrance].Time)<#12/30/1899 23:0:0#))
GROUP BY [Staff Entrance].[Door Location];
The sample table is attached
Thanks in advance
View 5 Replies
View Related
Aug 22, 2004
i was wondering how to go about doing this, i currently have a query which returns all the payments due in the next month, going to a report which acts as an invoice. I was wondering how to create a unique id for each invoice and store the last id so that i can automatically itterate it later
View 4 Replies
View Related
Mar 23, 2006
I have been having a brain fart and cant remember how to do this...
I have two tables and I want to create a query where the TransactionID from one table is not equal to the TransactionID from the other table so that it will display only those items that are not active in this other table.
Please help ASAP.
Thank you!
Kerry
View 6 Replies
View Related
May 19, 2005
Here's my problem. We maintain a list of items that are sold on ebay. We keep track of all items purchased by individuals and the dates that those items were purchased. We guarantee shipping by 6 weeks after the sell date...
Therefore, I am trying to create a query that will show me all items that are 6 weeks and older and have not shipped. There is a column that says date purchased and one for date shipped if an item is already completed. Any suggestions!!??!?! :confused:
View 2 Replies
View Related
Oct 2, 2005
i have joined a jobs table and payout table together and performed a query.
however i would like to perform SUM by the job_id but crew_id fields are duplicated.
(the payout table comes from a subform that doesnt differentiate crew type so my SUM totals all the payouts whereas i want to sum the payouts by job_id when the crew id are repeated.
TOTAL JOB_ID CREW_ID
$5000 2 2
$3290 2 2
$1000 2 7
$2500 2 7
need to return $8290 for one query (5000+3290)
then $3500 from another (1000+2500)
View 2 Replies
View Related
Nov 1, 2006
I am trying to create a make table query that extracts information from another table based on its year and month.
The problem I am having occurs when it comes to selecting data across multiple years ie. April 2005 - March 2006. The table that contains the data has two fields that are year and month.
I have created a form to allow the user to enter the required years, but I am unsure how to get the query not to select duplicate months e.g. April 2005 and April 2006.:confused:
Any help would be appreciated.:)
View 12 Replies
View Related
Aug 16, 2007
Hello
I'd like to create a query like this:
select table.column, table.column, table.column
from table
where table.column = textboxvalue
How can I do this?
View 2 Replies
View Related
Oct 19, 2007
Wasen't sure whether this is a Reporting thing or a Querie thing as it resides inbetween the two.
Basically (and believe me I thought this would be pretty straight forward!!!!), I am trying to create a chart from a query that returns the occurences of concerns raised each month for a year. The query returns the following:
MthTextExpr1
Jan0
Feb0
Mar0
Apr1
May0
Jun0
Jul1
Aug1
Sep2
Oct1
Nov0
Dec0
In order that the user can specify a year that the chart will be applicable to, there is a field within the query that relies on the input of a year on an unbound text box on a form: [Forms]![frm_concern_frtpage]![Enter Year] is inputted into the critera box of this field.
However, if you try to create the chart it does not like this and just gives the following two error messages. If you enter a value into this text box and run the report containing the chart, it just gives a blank chart!!!!
Why is this error occuring?
I just want months along the X axis and the values from the Expr 1 column to be displayed on the Y axis!
View 5 Replies
View Related
Sep 6, 2004
I've been reading up quite intensively on using QueryDef's collection to try to pass a Parameter to a Query in order to then Open a Report. But can't seem to get it to work. I've been trying to write an SQL statement that the VB will accept in code but never got it to work. Depite copy/pasting the SQL from the Query Access creates. regardless.. I really need some help with this one. Thanks. Procedure follows. Get error that Report not found... The name is correct... I just want to pass the integer (index) from the ListBox into the Query then Open the Report... Thats all...
Private Sub previewReport_Click()
On Error GoTo Err_previewReport_Click
Dim intSelectMonth As Integer 'The Index of the Item Selected in Form List Box
Dim db As Database
Dim smReport As Report
Dim smQuery As QueryDef
Dim month As Parameter
Set db = CurrentDb
Set smQuery = QueryDef![GetReviewsMonthQuery]
smQuery.Parameters![rvMonth] = 9 'Using 9 as September just for Testing
Set smReport = Reports![Review Schedule by Month/Analyst]
DoCmd.OpenReport smReport, acPreview
Exit_previewReport_Click:
Exit Sub
Err_previewReport_Click:
MsgBox Err.Description
Resume Exit_previewReport_Click
End Sub
View 2 Replies
View Related
Jun 14, 2004
Hi,
I don't know how to create a simple query that will compare 2 tables and create a new one after that.
Each table has only one field.
Example:
Table 1 has records A, B, C, D and E
Table 2 has records A and C
I want new table to be like table 1 minus table 2. Like cleaning up table 1.
New table would have records B, D and E
I think it's very simple but I can figure how to do it.
Could you pls give me advice?
Thanks a lot,
skuba
View 3 Replies
View Related
Jul 18, 2005
Hello,
I can run a query but when I try to display this information on a report I get the following message:
The specified field “tblMachineData.TagName’ could refer to more than one table listed in the FROM clause of your SQL Statement.
Here is my SQL code from the quey.
SELECT tblMachineData.TagName, [tblMachineFloat Query].DateAndTime, tblMachineRunTag.TagName, tblMachineFloat.Val
FROM tblMachineRunTag INNER JOIN (tblMachineData INNER JOIN ([tblMachineFloat Query] INNER JOIN tblMachineFloat ON [tblMachineFloat Query].DateAndTime = tblMachineFloat.DateAndTime) ON tblMachineData.TagIndex = [tblMachineFloat Query].TagIndex) ON tblMachineRunTag.TagIndex = tblMachineFloat.TagIndex
WHERE (((tblMachineFloat.TagIndex)=0 Or (tblMachineFloat.TagIndex)=1 Or (tblMachineFloat.TagIndex)=3 Or (tblMachineFloat.TagIndex)=4))
ORDER BY [tblMachineFloat Query].DateAndTime DESC;
Any help is appreciated,
Bob
View 2 Replies
View Related
Dec 27, 2005
Hello,
I was wondering if it was possible to show data sorted by date. But I only want to see the dates that are before another date in a different table. Is that possible? (So two tables of data; one date is on the first table, the other is on another table)
Thank you.
View 5 Replies
View Related
Nov 9, 2014
I am quite new to MS access and started to develop a small database for my practice. I have made four tables that are for Patient details, their doctor detail, their problem and their treatment. I have successfully created form to enter the data.However I an unable to proceed, now I want to create a discharge letter which will be addressed to the doctor (based on the tables described above)
I want to make this in a way that if I enter the patient details i-e date of birth, ID and / or name a letter is created based on the tables above. I also want this letter to be saved for future use. how to design that letter and should it be a query or report.
View 6 Replies
View Related
Apr 10, 2013
I need to create a form, in which would be a query at the bottom.
I have table f.e. "A" in which is all data about cars.
I have table "B" in which is all data about customers.
I would like to create form "B" - which would add new customers, but at the bottom of that form I would like to see tables "A" filtered results.
Something like: In form B I create new customer. When I insert what car they want and how much they can pay, at the bottom generates query which would show all possible results from table "A".
With one customer I know how to do it. But when I insert second customer in the form, that query stops working while it doesn't know from which entry it should take the information. I don't know how to tell, that the query should take the data from the customer ID1 when I'm looking customer ID1, and from customer IDX - when I'm looking customer IDX.
I thought, perhaps it possible to create button with macro - when I press the button it copies all data from the current entry, it pastes in the form where is only one entry and works with query, than generates the query results which will be showed in the same entry that I pressed the button.
View 3 Replies
View Related
Aug 18, 2011
I am trying to finish off my database with this last query. I have two fields named date_to and date_from, within a table tbl_non_avail . I need to show people who are available not between the from and to dates.
View 3 Replies
View Related
May 20, 2015
I am creating a database for creating quotations. The quotation number is generated using the date, for example the first quote today would be quote number "05202015-1" because it is the first one today. The next quote today would be quote number "05202015-2" and so on. Is there a way to make access automatically generate these quote numbers based on the date?
View 3 Replies
View Related
