Query Troubles (DuplicateDate)
Jan 4, 2006
Hi all,
I have a query that returned multiple duplicates of a record (3 or more) between a specified start date and end date (entered by the operator) and displays them into a report. I created the query using the duplicate wizard and changed >1 to >=3 and added "AND ((Guardcalloutsarchive.Date) Between [Start Date] And [End Date]))" to promoted operator to enter date range.
The problem is I only want the query to give me multiple duplicates from within the specified date range only. The query is returning multiple duplicates form the entire table.
Any input would be appreciated.
View Replies
ADVERTISEMENT
May 10, 2007
Hello all,
Does anyone know (and would be willing to share) how one can order the results of a Union query in Access by something other than the returned values?
I have a simple Union query that would work perfectly - if I could get the thing to order the results in a particular order everytime:mad:. The query returns counts (all from one table) of separate select statements that each meet certain conditions (e.g. having state=Nevada, having state=California, etc.) as records - but since Union queries in Access order results in ascending or descending order by returned value, it puts my records in a different order every time the record values change. I need to have them ordered the same way (in the same order as they appear in the Union query, if possible) everytime so that I can return those values to specific cells in MS Excel. Any suggestions would be much appreciated!
View 3 Replies
View Related
Jan 17, 2007
First off, thanks to all who have helped me with my other posts :)
I have yet another question that y'all might be able to assist me with... I'm looking to create a report and am having trouble figuring out how exactly to format the stupid thing... And, like my other posts, this could be a little confusing... I'll do my best ;)
I've modified this a bit to figure one thing out at a time. If you have a solution to my original post (below) feel free to help out, but for now I am just trying to figure out how to get the variables in the report to go across the page instead of down. I would like my quantity field to be listed across the page with the price for the part below it:
1-9 10-24 ... 50K+
$$ $$ $$
instead of:
1-9
$$
10-24
$$
...
50000+
$$
Thanks!
------ Here's my original post------
Ok, I've already got a query with all the data and computations needed. Basically I'm just trying to create a price sheet for our products here, but it's not quite that simple... I have 72 different part sizes, 12 for each of 6 voltages (3KV, 5KV...20KV), and a price for 13 different qty ranges (1-9, 10-24...50000+). I would like it to look something like this:
Qty1-9 Qty10-24 ... 50K+
Part 1 $$ $$ $$ $$
Part 2 $$ $$ $$ $$
Part 3 $$ $$ $$ $$
Part 4 $$ $$ $$ $$
Part 5 $$ $$ $$ $$
Part 6 $$ $$ $$ $$
Part 7 $$ $$ $$ $$
Part 8 $$ $$ $$ $$
Part 9 $$ $$ $$ $$
Part 10 $$ $$ $$ $$
Part 11 $$ $$ $$ $$
Part 12 $$ $$ $$ $$
...
Part 72 $$ $$ $$ $$
Parts 1-12 will be grouped by Voltage 1, Parts 13-24 by Voltage 2, and so on... Have I lost you yet? :confused: :confused: :confused:
Grouping by voltage can be left out for now if it add too much confusion...
View 2 Replies
View Related
Aug 19, 2007
Hi, this is my first message on this forum ;)!
I have a little problem with my Access 2000 (with 2003 too on another compuer), in fact when I put an icon on a page of my Tab Control, the background of that icon is white and not transparent (the file I use is an "ico" file and so it HAS a transparent background, anyway I treid all the possible file types); the strange thing is that when I put an icon on a button, the icon is shown properly. Here is an image of my problem:
http://img529.imageshack.us/img529/9922/testuu9.jpg
Thank you,
StockBreak
View 10 Replies
View Related
Feb 29, 2008
here is what i have:
tables - tblAssets, tblSites, tblManufacturers, tblModels
i have 1-to-many relationships set up as follows; tblManufacturers --> tblAssets, tblManufacturers --> tblModels, tblModels --> tblAssets, tblSites --> tblAssets.
what i would like to happen is if i add a new site in tblAssets it will add the site in tblSites as well. if i have "enforce referential integrity" checked then it will only let me insert sites that are on the tblSites table, and if i uncheck the box then it will allow me to put in a site that's not on the table, but it doesn't add it to the table.
i only have 1 form and it's just linked to the tblAssets table. please forgive my noobness, i've been having a rough time trying to understand relationships even though i've read about everything i could find on them, so could someone please point me in the right direction?
thanks
View 8 Replies
View Related
Dec 1, 2005
I created a form from a table (maintbl) with a combo box. The combo box retreives information from another table (model#tbl)and autofills it in the fields below. the information that is autofilled does not automatically get entered into maintbl. Theres probably a good reason for this but i cant figure it out. Anyone?? Im not very good with code but if thats what it takes thats what it takes.
View 2 Replies
View Related
Apr 4, 2005
Hi,
I have hit a brick wall with my A-Level Access Project. Basically, I have a form for ordering where a user can select items from a combo box linked to a table that contains products. They then select the quantity and a subtotal is calculated for that particular item(s). By that I mean the subtotal generated is only for one selection from the combo box. Any subsequent selections have their own subtotals generated afterwards (I hope I'm not being too vague).
This is the form I am talking about:
http://img.photobucket.com/albums/v318/GOAT2G/FrmMakeOrder1.jpg
The thing I am having trouble with is the final box; "Order Total". I have no idea how to set it to sum all of the above Subtotals to equal an overall total. I will send anyone willing to help a copy of the database via email or something if they need it.
I would be immensely grateful for any help you can give.
Thanks,
Adam
View 5 Replies
View Related
Feb 19, 2006
Hi
I have just started using access to design Data entry screens for our SAS databases.
One of our major fields in our databases is an update date field which is set up in this format 20062002. year day month.
Now I am trying to make a text box on our form that displays the date like this.
I know i can do =year(date()) to show the year but I cant work out how i can get it to display year, day month in that format.
Any help would be much appreciated.
View 1 Replies
View Related
Oct 26, 2006
Hi,
Im currently making my first database for a company i work for. As expected im having some troubles hehe im not sure im going about everything the right way. At the moment im trying to create a form which will get product information from one table, display the details in a few combo boxes and based on a selection will store the selections in a separate table. Ive done everything up till that last bit, i cant seem to be able to record the selections in a separate table.
i would appreciate some help as my bosses have to be kept up to speed on my progress and as you can imagine it doesnt look so good when you tell them you havent made any progress hehe.
Cheers
Marc.
View 4 Replies
View Related
Jun 16, 2007
I have a form that I'm trying to make a sub-form for. I create the sub-form and link the two forms by a text field, but for some reason instead of being the contents of the field that links them being the same, it's the name of the Form, and thus any time I refresh the page the subform data gets lost in the table and is unassociated with any records I have on the main form...
I've done subforms before without a problem, so why are they messing up for me now?
View 7 Replies
View Related
Jul 7, 2005
Hi everybody,
I have to calculate somme totals for decimal numbers, and... I can't make it to work.
I have a report:
Quantity Unit_price Price_without_Taxes Taxes Price
2 1,62 3,24 0,62 3,86
2 2,13 4,26 0,81 5,07
TI: 8,91
TI it's the sum of Price, and here is the problem, because the value of TI should be 8,93 Euro (3,86+5,07) not 8,91. In this case it's a loss of 3 cent , but in other cases, if i have more value to calculate the sum for I can loss a few euro!
The values are placed in the detail section, TI on the footer section of the report. To calculate TI I used a Text box wich format is currency, Decimal places 2, Control source =Sum([Price]).
I make this same calculations on forms also, there the situation it's worst, the value it's rounding down or up in each case, in this particular case insted of 8,93 I got 8,00.
What's wrong with it? I'm making mistakes somwhere?
Any help is apreciated. I need it soooooo bedly! :)
Thanx,
Attila
View 14 Replies
View Related
Nov 21, 2005
Hi all,
I am a fairly new User to Access and have been charged at work with making some spreadsheets useful. I have 5 workbooks, with approx. 5-9 wkshts per wkbk, each with anywhere from 200-1200 rows. (CT Scanner Protocol information).
My Idea was to link all of the sheets to a reference excel spreadsheet and then to import that as a linked file to access. The problem is that I cannot change a linked fields property, among other things, and have trouble with some blank fields. i have been working on this for a while, searching the net and am still clueless.
Anybody have any Ideas on what I can do? I can email copies of the "sample" sheet I am using (a condensed version that way I don't goof up the main one, plus it is smaller and easier to work with) to anyone who might be able to help. Or any inout here would be appreciated.
Thanks!
Tony Blatnica
View 1 Replies
View Related
Mar 18, 2005
I have a form(frmGetRecord) with a subform(frmSubGetRecord). frmGetRecord has a combobox cmbCNO to choose the patient. The subform displays the admit date and discharge date(if there is one). There can be multiple admit/Discharge dates for a patient. Based on the values from fields on the form and subform, I would like to open a data entry form(frmEvents) for the particular record. I have a command button to run the code to open frmEvents. I am having trouble with the criteria in the where condition.
stLinkCriteria = "[CNo]=" & "'" & Me![cmbCNO] & "'" _
And "[AdmitDate]=" & "#" & Forms![frmGetRecord]![frmSubGetRecord].Form![AdmitDate] & "#" _
And "[DischDate]=" & "#" & Forms![frmGetRecord]![frmSubGetRecord].Form![DischDate] & "#"
DoCmd.OpenForm stDocName, , stQryName, stLinkCriteria, acFormEdit
When I click on the command button I get a type mismatch error. If I test the StLinkCriteria separately, the DoCmd.OpenForm will work for just the CNO field or just the AdmitDate field. If I test those two strings together, I get the type mismatch. Also, DischDate could be blank and I'm not sure how to add an IIf statement to the string to test for that in the where statement. I'm also not sure how to make sure it picks the correct record if there is more than one admit date. I would like to select the desired date record, then have the command button open the appropriate record.
Hope this makes sense. Thanks for any help.
View 10 Replies
View Related
Sep 1, 2005
I have a number of list boxes that dont show up in the VBEditor under the form name.
To be more specific, these controls do not show up in the drop-down list on top of the code window. If I try to assign an event procedure through 'code builder' to these controls, they refer back to a pre-existing module instead of the form under "Access Class Objects".
I have tried repairing and compacting the database to no avail. Can someone guide me? Please ask for more info if you would like some.
Thanks in advance.
View 2 Replies
View Related
May 8, 2007
Hi all - It's been years since I did anything in access. I'm trying to develop a membership database, and among other things, I want the app to launch with a search screen. I have 3 fields on the screen:
1. surname
2. firstname
3. childname
I want the user to enter anything (even partial info) in any of these fields, and when they hit the search button, a listbox - (best choice?) will display all records that match on any inputted info. If nothing is entered, and the search button is hit, I want all records in the members table to be displayed. I expect the query to be something like this:
if not isnull (surname field and firstname field and childname field) then
select surname, firstname, childname
from members
where surname like [txt_surname] or firstname like [txt_first_name] or childname like [txt_child_name]
else select surname, firstname, childname
from members
endif
What I do not know is HOW to implement this through MS ACCESS 2003.
Any help would be greatly appreciated.
Thanks.
View 3 Replies
View Related
Feb 14, 2008
Hi All,
I'm relatively new to Access and having some difficulty using conditional statements - if anyone could help I'd really appreciate it.
Basically I want to take an order summary table (OrderData) that shows total orders over different date ranges and expand them to show the average order per day for each product.
I'm using two queries to do this, the first finds the average per day for each record (Demand) using this formula: Expr: Sum(([OrderData]![orderamount])/([OrderData]![EndDate]-[OrderData]![StartDate]+1)) That part works fine.
The second query then needs to lookup each individual date of the year against the date range on the original data table, and if the date falls within the range, places the daily average from the prior query in a column.
I had been trying to use this - Expr: iif([Output]![Date] Between [OrderData]![Start] And [OrderData]![End], [Demand]![OptimalAverage],"0") but it's not working.
Any ideas?
View 12 Replies
View Related
Jun 26, 2007
I'm having a problem with queries, and I can't seem to find a solution in books - I looked through about ten of them and none of them addressed the problem. This may be because it has a painfully obvious solution...
A little background:
I am designing a database for a debt-collection law firm. One of the functions it must have is to keep track of various different sorts of financial transactions which can pertain to a given debtor (ie, a received payment, a cost expended, and a few other things).
The problem is that, in generating reports, I need to use queries to find several sums of only those transactions which fall into specific categories (for instance, to calculate the amount a debtor has paid against his balance, it needs to sum only those entries which are both linked to that debtor's ID number and whose type field reads "payment", and then subtract from that those entries whose type field reads "cost"). The problem is this: not all debtors may have "costs" entries, and when there are none, the report comes up blank with a single "#Error" written in the name field and nothing else present.
I believe the problem is that the Sum aggregate is returning a null value when the query finds nothing that meets the criteria. I have been unable to find a way around this; the Nz() and IIf() with IsNull() functions don't seem to be helping.
The query runs as intended when there are entries for every relevant type; however, it is undesired to have to enter a "payment" of $0, "cost" of $0 etc for every entry just so that this function works.
Is there anything I can do about this? Any input would be appreciated, as I'm fairly inexperienced with the use of Office Access. (If it matters, I am using Office 2003).
View 2 Replies
View Related
Oct 19, 2004
What is the best way to impliment a query in a form so that the user can view the query records, and have the option to print or save the selected record using command buttons?
I tried subforms but I could not get the command buttons to work in the subform after it went into the form, it wanted to print the entire form instead of the selected record from the subform.
So in a nutshell I have 3-4 queries that are built, and I want to have them show up on my form in a format that the user can scroll through the results and select a single record of the results and then print or save that individual record from the form, if such a thing is possible.
Thanks in advance
Todd
View 1 Replies
View Related
Mar 24, 2013
I am trying to do the good 'ol sales report (query) to include customers with no sales.
I have a customers table, account number table, sales table & sales (line) detail table. (all linked in that order)
If I run a query to show customers (in the customer table) with account numbers, that works
An unmatched query to show customer without an account number works (but of course the unmatched account number field isn't shown).
How can I get the two two be shown together with the "unmatched" having a null or 0 for their account number?
I am guessing in principle, the resulting solution can be modified to show customers without sales alongside those with sales?
View 3 Replies
View Related
Mar 4, 2015
In some cases I create pass-through queries and use these in an Append or Make-table query to bring data locally.
All is well and fine until source data changes and the pass-through query runs too long and times out.
If needed, I can extend the timeout value in the Parameters of the pass-through query no problem, but when I try to open the Append or Make-table query in Design view to do the same, the pass-through query is first triggered and then throws the timeout, and I cannot access the Design view of the Append or Make-table
Is there a way to open an Append or Make-table query in Design view without invoking the source query?
View 1 Replies
View Related
Oct 28, 2005
Hello All,
I'm trying to run a UNION query that joins five queries through a MS WorkSpace into a DAO.recordset in VB. I'm pulling the data from a SQL Server Database through VB in Access. I'm attempting to open a recordset with a query passed to it as a string. The query is below. For some reason, I'm receiving a message: "MS Jet database engine cannot find the input table or query. Runtime Error 3078".
Here's what's puzzling. When I run a single query without any UNION statement, the code finds the table and runs fine without error, but anytime I join two or more queries with a UNION statement in the VB, it gives me the error.
I've executed the same UNION query in both Access Query Builder and SQL Server's Query Analyzer and they work fine in both environments. It's only when I call the query from a DAO.Recodset with VB that it causes this problem. The following is a sample of the UNION query joining two of the five queries. Does anyone have any idea what could be the problem? The following query executes in about 5 seconds so I don't think there's a "time-out" issue. I'm thinking that the UNION statement may be the culprit. Maybe there's another way to approach joining these separate queries? Any help would be most appreciated. Thanks.
SELECT SalespersonID, Sum([SlsPrice]-[RtnPrice]-[SlsDiscnt]+[RtnDiscnt]) AS fldPrice FROM MyTable WHERE (((Source)='d') AND ((DistrictID)='01') AND ((CategoryID) = 'HCPROD') AND ((BrandID)<>'CSS')) AND (((BrandID)<>'1356')) AND (((BrandID)<>'1400')) AND (((BrandID)<>'1551')) AND (((BrandID)<>'555')) AND (((BrandID)<>'66'))
AND (TransDate >= 07/01/2005) AND (TransDate <= 07/31/2005) GROUP BY SalespersonID
UNION
SELECT SalespersonID, Sum([SlsPrice]-[RtnPrice]-[SlsDiscnt]+[RtnDiscnt]) AS fldPrice FROM MyTable WHERE (((Source)='d') AND ((DistrictID)='01') AND ((ProductID) = '0029800')) AND (TransDate >= 07/01/2005) AND (TransDate <= 07/31/2005) GROUP BY SalespersonID
Set wrkJet = CreateWorkspace("", "pw", "", dbUseJet)
Set db = wrkJet.OpenDatabase("DW", _
dbDriverNoPrompt, True, _
"ODBC;DATABASE=DW;DSN=DW2")
'Set rs1 = db.OpenRecordset(strSQL)
View 9 Replies
View Related
Nov 20, 2013
I am wondering if there is a quicker way to export a query to excel then have the data in that query removed from the original table. (effectively cutting the data from the table and exporting to excel)
I understand that this can be done by exporting the query to excel then running the same query as a delete query to remove the data but I just wondered if this is the most efficient way.
I have experience of VB in excel but currently only use the basic macro builder in Access though if Access VB is more efficient I can easily learn.
View 5 Replies
View Related
Apr 2, 2013
I run a physical therapy office and patients come in for treatment either 3, 4 or 5 times per week. My database is used to track these frequencies (among other things).
I have 3 queries which count how many patients come in 5, 4 and 3 times/week.
In my main table I have fields called "how many 5's", "how many 4's" and "how many 3's".
I have tried to design an update query which will update those fileds in my main table to reflect the counts in the 3 queries mentioned above.
(I'm not using SQL view, I'm using the query design view)
In the "update to:" row, I use the Build function and locate the count I'm looking for.
Problem: when I run the query I get the error: Operation must use an updateable query.
View 3 Replies
View Related
Aug 12, 2015
I have a query where these are the fields:
ProductRevType
RevLag
RevFlowThru
CloseMoYr
ProjRevDate
CurrentMRC
ProjRevMRC
The ProjRevMRC field is an expression that reads:ProjRevMRC: IIf([ProjRevDate]>=DateSerial(Year(Date()),Month(Date()),1),[CurrentMRC]*[qry303a_ SFADetailMRC_ONLY]![Rev Flow Through],0)
When I run the query, it works perfectly, but when I created a crosstab query to show totals by month, I wanted the totals to be zero for the months less than the current month. Is there a way for the crosstab query to execute the expression and put zeroes for those months?
View 4 Replies
View Related
Jul 15, 2014
I have a field that is giving me the number of business days between a period of time and then I want to subtract that number - the person's PTO time to see the actual days they were available...when I simply type the number in (see below) it works great but I want to set up a prompt that will ask me how many PTO Days to calculate as it will be different for each person I am quering...is this possible?
View 9 Replies
View Related
Jul 23, 2015
I have a form which will be used as the basis to print a label.
It is bound to a query and when I open the form I pass over a 'where' condition to return 1 record. I then use the query to produce a report/label.
What I want to do is to update the form/query without updating the underlying tables to the query.
View 14 Replies
View Related
