Hi,
I have a query which I run in the query design view. Then I have the same exact query which I run on the 'background' of a form.
The issue is, both queries turn out to have different record counts(eventhough I am using the same query and same tables). What is going on? Please help.
Thank you.
Joe
I am working with Access 2010, on vista. What I have is a query made up of two tables, one product the other inventory. (see below) query.jpg
In the product table i have a field called "minimum reorder level". In the inventory table i have two fields one called "number in stock" and "number on order". What i want to happen is "number on order" to be filtered by the result, if the "number in stock", is less than "minimum reorder level", if it is, have the result placed in the "number on order" field. EG. if the "number in stock" = 2 and the "minimum reorder level" = 5 then 3 would be placed in the field "number on order" and only the second record from the query would be visible (see below) Query result.jpg The result of this would mean that the field "number on order" would be populated with the result and the and query would also use this to filter the record.
I want to add a number to my results within a query depending on the month and how many results. For example I have 10 results in my query 3 from January, 5 from March and the rest from April. The 3 from January would be 1,2,3. The five in March would be 1,2,3,4,5 and so on. Is it possible to do?
I'm using access 2003.
I have a query that is based on two other queries (they feed a larger query that a report uses). They are all select queries, no make-table, etc.
The query runs fine against the production oracle database. However when the query is run off of either of the two identical test databases (production was dumped into test within the last week), the query stops with an error of Invalid Operation.
The Help Screen (see below) doesn't seem to apply to anything this query is doing. Any thoughts?
Invalid operation. (Error 3219)
Possible causes:
You tried to write to a read-only property. See the Help topic for the property to determine whether it is read/write.
You tried to use a method or property on a type of Recordset object that the method or property does not apply to. See the Recordset object summary topic to determine which methods and properties apply to a given type of Recordset object.
You tried to append a property to a Properties collection of an object that does not support user-defined properties.
You tried to use the Update method on a read-only Recordset object.
Thanks,
B
I there is no result in query, I need the default result zero in my form field. I only use query wizard to create queries.
View 5 Replies View RelatedI have multiple databases (one for each department). All 4 databases have an Agents table (tblAgents). Now I am creating another database that uses all of the agents on site. What I want to do is link all the agents tables and combine the values in one query.
Fields are:
UserID (PK)
AgentName
BadgeNumber (Employee Number)
Campaign (Department)
Coach (Supervisor)
HireDate
Status (Active / Leave of absense / Terminated)
The tables are linked to my new database. So logically, I'd think my next step is to query the tables. This is where I'm stuck. How do I say:
Agent = tblAgentsDepartment1.AgentName and also tblAgentsDepartment2.AgentName and also tblAgentsDepartment3.AgentName and also tblAgentsDepartment4.AgentName?
I'll have to do this for each field involved...
I have two tables: tblOne and tblTwo with identical categories.
TblOne and tblTwo both group by Category and Duration. When I make a union query between tblOne and tblTwo, I notice that if I have the exact same category and duration for BOTH tblOne and tblTwo, the union query "eats up" one of the identical entries. How can I fix this?
I need to have two different tables because they correspond to two different machines (but are both the same type of machine, hence the identical categories)
I have a spreadsheet with 4,000 plant names, for example one plant name reads "Acer palmatum (Japanese Maple Tree)".
I transposed this spreadsheet into a database.
And then I created a query with 3 columns - "Acer palmatum (Japanese Maple Tree)" - "Acer palmatum (Japanese Maple Tree)" - "Acer palmatum (Japanese Maple Tree)".
And now I want to convert the database into 3 columns - "FirstName" - "RemainingName" - "OtherName" - so I can manually change this plant name for example to "Acer", "palmatum", "(Japanese Maple Tree)".
But when I edit any one of the names - all of fields in that row change whereas I only want the name in one column to change at a time.
Hello, I have a combo box on a form which lists some names generated from a table.
I would like the selected name to be inputted into the 'critera' of another query called 'qryPBCustLevel' and for that query to be run.
I have tried to code this, but it is crashing at the point it trys to add the name into the query.
Can anyone help? Code listed below.
Sub cmbPB_AfterUpdate()
'Set the Dimensions of the Module
Dim strSQL As String, strOrder As String
Dim dbNm As Database
Dim qryDef As QueryDef
Set dbNm = CurrentDb()
'Constant Select statement for the Query definition
strSQL = "SELECT DISTINCT tblTempPB.PB_NAME" & _
"FROM tblTempPB"
strOrder = "tblTempPB.PB_NAME;"
' Find the record that matches the control.
Me.RecordsetClone.FindFirst "[PB_NAME] = '" & Me![cmbPB] & "'"
Me.Bookmark = Me.RecordsetClone.Bookmark
'Pass the QueryDef to the query
Set qryDef = dbNm.QueryDefs("qryPBCustLevel")
qryDef.SQL = strSQL & " " & strOrder
'Open the Query
DoCmd.OpenQuery "qryPBCustLevel", acViewNormal
End Sub
Thanks, Steve. :confused:
Friends own a book shoppe. They have closed up both their retail stores and are working on line only. A long time ago they bought a PC based program to keep their inventory and manage their on line sales. The program is called BookTrakker and still on sale today.
I called them to ask for assistance merging the access databases for but stores with their Master database. The owner sads they dont offer any assistance in combining databases.
I have had no trouble using the append command to combine the 40 odd tables in the database except for one small table called Keywords. This table only has 5 fields but the sallest has over 7,000 rows. This is one of the more important tables because Keywords are used by the search engines on-line for potential buyers find their interests.
The error message I get from Access is "Microsoft Outlook Access set 0 field(s) to nulll due to a type conversation failure, and it didn't add (number of rows in table) to the table due to key violations, 0 records due to lock violations, and 0 records due to validation rule violations"
The table structure is identical. The only difference is the data in the rows.
I tried removing both Primary Keys for both the tables (in and out). Nothing, same error message.
Next I tried appending the tables without the entire Keyword_id (Primary Key) field. Nothing same error message.
Hi,
I'm designing this system in which each employee has different area of strength (i.e. Math, Languages,..)
if an employee has 2 or 3 area of strength his name appears in the query more than once. I want his name to appear once.
I tried "group by" but it gave me an error. I think I'm doing it wrong.
Please Help!
CS.
I am trying to create a VB script to automate a mailing based on several query result sets from access. I have gotten to the stage that the output is correct but have a problem with the 5th and 6th record set query as they only return one record (When in fact there should be at least two for each).
I don't really understand why this is happeneing as the SQL is exactly the same as in the 2nd record set - which works perfectly. Also I've tested the SQL directly in an access query & there are no errors in the formatting that I can see... correct number of records returned.
Code:
Public emailaddress, ccaddress, Subject, body1 As String
Public baserow, toprow, countnumberofrows, emails As Integer
Public tempdir, projectlistdir, WBPATH As String
Option Compare Database
Option Explicit
[code]....
I have 2 tables, Event and Person Particulars.
In an event, groups of 2-5 persons may be tagged to this event by a randomly generated number (using autonumber).
Let's say Tom (social security number: 12345X) is tagged to events 2, 5 & 6. There are of course other persons together with Tom in the above 3 events.
If I would like to find out who are the persons who are in events which Tom had participated in, how do I find them using a query?
Currently, I'm thinking of using a searchform where it would return his "associates" if I just query using his social security number, i.e. 12345X.
One set of users enter sales orders that are pretty simple in nature... Order Number, Item, Quantity, Ship To... etc. They occasionally run across the need to make multiple entries that are identical, because a customer wants the order delivered on a regular time schedule or they want invoiced in increments, etc.
So, they have a need to be able to enter as many as 50 orders, all identical except for the Order Number (and delivery date), all at once. None of these fields is a key field or an autonumber. Order Number is manually entered, though it is indexed - no duplicates. I'd like to create the ability within a form for them to put in a list of Order Numbers, plus the values for the other required entries, and have them populate the source table by applying all the entries to each of the individual Order Numbers.
Any ideas would be greatly appreciated!!!
Tom
I have an access 2003 database which holds data for lorry loads of timber delivered to different places at different prices and by different hauliers who get paid different ammounts.
I have tables that hold the prices for both haulage (sorted by the delivery location and haulier) and product price (sorted by the delivery location and the haulier.
I have two almost identical queries which give the haulage price for a particular load and the product price for a particular load.
Only the haulage prices seem to be returned - i have checked the settings and relationships in each of the tables invlovled and they appear to be that same.
Why would one query return the value and the other not???
I have an Access DB built that links to an excel spreadsheet. The spreadsheet changes so I cannot import and have to link.
Now the source of my spreadsheets is producing two spreadsheets with identical fields but different data. I need to combine the data from these two spreadsheets. Can I link them both and combine them in a query? How?
Appreciate any advise.
I have a question regarding making a query with joint data that are not identical, as I'm not sure whether it's possible.
I have to look at certain serum levels at te start of a new medication regimen, and serum levels after 5 years.
I have one database with:
Patient number
Start date medication
Start date medication + 5 years
And one database with:Patient number
Date of blood test
Serum level molecule A
Serum level molecule B
What I want to do is make a query where the patient numbers are joined. The problem is that the "start date of the medication" and the "start date + 5 years" are not identical to the dates of the blood tests. E.g.: I have a patient that started medication on 01/March/2006, but he has had blood tests done every 3 months from 28/November/2003 till now, and not on 01/March/2006.
I would need the results of the blood tests (of molecule A and B) at the start of the medication and after 5 years, but if the test date differs by a day, Access already sees they're not identical.
I would like to have access select a blood test date that is as close to the medication start date as possible, within a time frame of e.g. +3 and -3 months. Is this at all possible?
I am trying to calculate the average patients age from 2671 records using this SQL:
SELECT tbl_Customer_Details.DOB, CalcAge([DOB]) AS Age, DAvg("[Age]","qryAvgAge") AS Average
FROM tbl_Customer_Details
GROUP BY tbl_Customer_Details.DOB, CalcAge([DOB]);
why am I getting the result:
68.1131066106
I would have thought that it would have been 68 a whole number, has anyone got any suggestions why this should be.
thanks
Hi
I have a query with 2 fields, when it is run it returns a result based upon the result of the set criteria.
Can you get this value to be displayed in a text box after a button is pressed.
dave
Hi
I am new to VBA with access. Im wanting to get the result of a query called "qLastRotaDate" into a variable called "datLastRota". The query returns a single date, it is not possible to have more than one result for this query. I have tried different variations of : datLastRota = qLastRotaDate but cannot get any to work. I'm assuming this is very simple yet I cannot figure it out as am very new to VBA.
Any help would be greatly appreciated. Sorry if this has been dealt with in previous posts but I could not find any info by searching the post.
Thanks
Mikee
Hi,
Can anyone see anything wrong with this query?
I have 10 combo boxes where years are selected. If the last year nothing is selected (meaning is empty) then my query should return the last selected value. It seems the IIF query does not seem to work (Still showing empty). Can you please advise and help me on this?
The query I am using is shown below.
Thank you
dfuas
IIF([Trade].[Vintage_ to] = ' ',([Trade].[Vintage_from9]) OR ([Trade].[Vintage_from8]) OR ([Trade].[Vintage_from7]) OR ([Trade].[Vintage_from6]) OR ([Trade].[Vintage_from6]),[Trade].[Vintage_ to]) AS [Vintage to]
hello every one,
first, im apologize for my English.
I have form that runs until the user answer the all the questions.
I place the last question Id in listbox. do I can place the result of the query (max of question id) in a variant in VB or I must place it in listbox/combobox/textbox?
Tsur triger.
Hi all,
Would appreciate help on this. I have a query which combines the results of 3 queries. Once the underlying queries each has a result I get a result in my query, but if 1 of the underlying queries has no result I get nothing. (I hope this makes sense).
How can I set my query to show results even if the underlying query doesn't.
thanks in advance.
rgs
Ginny
Hi,
I have the following query in a access 2000 db
SELECT [5k1].[Race Number], [5k1].[Name], [5k1].[Club], Date()-[Date of birth] AS Age, [5k1].[Handicap Time], [5k1].[Male / Female], [5k1].[Date of birth], [5k1].[handmin], [5k1].[handsecs], [tblRaceEntries].[intEntryRace1], [tblRaceEntries].[intEntryRace2], [tblRaceEntries].[intEntryRace3], [tblRaceEntries].[intEntryRace4], [tblRaceEntries].[intEntryRace5]
FROM 5k1 INNER JOIN tblRaceEntries ON [5k1].[Race Number]=[tblRaceEntries].[RaceNumber];
it is used as a means of input from a form.
When it is used in windows xp then all is good.
When it is used in windows 2k it doesn't work as I intended. The problem being that [5k1].[Race Number] field is reset with a default '0' value of [tblRaceEntries].[RaceNumber]
Whats the difference between the 2k/xp system that causes this.. what the solution?
many thanks
I want to know how to generate a result from a query in a control of a form? For exampel I have made a SQL count function in a query and I want to use this result i a control of a form.
View 1 Replies View RelatedI have a simple database I am trying to put together. One table, one form, very vanilla. I created a query that ranks values that I enter in a table. I would like to put the result of the query (the rank) next to the value in the form itself. I thought if I created a text box and entered the rank field in the text box, that would be it, but when I run the form, I get "#Name?".
Any thoughts? Is this even possible?
Thanks in advance.