Hi, i'm a newbie so please be gentle :)
ok, i have 2 tables, one is for part number and product ID and the other has product ID and Vehicle details.
i know how to set up a parameter query but just can't quite get the result i want, which is to set up a query based on the result of the first query.
in short i want to enter a part number and it comes up with the vehicle details.
Many thanks in advance
Ty
hi this is my first post. This is probably so basic you will think i am stupid but your help would be appritiated.
I have a very simple database which displays the questions stored in a table in a form . The recipent answers the questions generaly yes or No. On answering yes or no i wish to attach values (hidden) to thier answers and then based on the answers produce a simple set of bar charts. I have tried but do not know how to assign the values to the questions . i would then like to produce 5 bar charts 1 for each of the catogories and one as a total (as an overall and also in sub groups of which there are 4 each of which has a field with a specific prefix either b,c,r,s dependant on the type of question. )
hope someone can help
Page Jump
I have a from where users select from one of four Options. The Print putton on the form enters text into an unbound textbox based on the selection. Here is the code:
If Me.optWhichList = 1 Then
Me.txtWhichList = WHAT TO PUT HERE
ElseIf Me.optWhichList = 2 Then
Me.txtWhichList = "LS"
ElseIf Me.optWhichList = 3 Then
Me.txtWhichList = "AS"
Else: Me.txtWhichList = "SM"
In the Query I have teh Criteria as
[Forms]![popfrmChooseMailingLabels]![txtWhichList]
Works fine for options 2, 3 and 4 which choose the relevant criteria in the corresponding Query, but I want Option 1 to select all records. What do I put there so that all records are selected (i.e., no criteria)
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:
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 have been reading this is how I am able to articulate my problem with some degree of knowledge.
I have 3 tables as Follows:
Table 1. [ID]pk, [Contestant Name],[Event],[Category],[Class]
Table 2. [ID]pk, [Judge Name],[Event],[Category]
Table 3. [ID]pk, [Contestant Name],[Event],[Category],[Class],[Score1],[Score2],[Remarks]
Table 3 is a record of score from a specific Judge to a specific contestant. There will be a record for each contestant from each judge that is judging that specific [Event] & [Category]. therefore if there are two judges and two contestants there will 4 records entered into the table.
Problems 1. (I can't get my tables related properly)
I should not have to enter the duplicate information each time I enter a record into the Table3. The common information should be available within the other tables using queries.
Why i think I have problem 1:
There is a many to many relationship here on the parent form what i want is the [Judge Name], [Event], [Category].
On the related Sub form i want to see a query that shows me parts of tables 1,2,3 for each Contestant that is entered in the matching [Event],[Category] combined with the query of 1,2,3 and be able to enter this judges scores against the contestant Name and then when I change to a new judge I may see the same contestants but be able to enter a new score record against that contestant with a different judge.
In English
Assume you are a judge for a contest you have a form in front of you
Parent form Select your Event, Name, Category from a pull down box in the sub form you are presented with a list of Contestants that are on stage. You enter Score1 & Score2 for each Contestant and the record is created.
Judge #2 is doing the same thing
He selects his Event,Name, Category from the pull down menu on the Parent form and walla the subform populates with the same contestants as Judge#1 and he enters his Score1 & Score2 into the subform
and
What we have is 2 Judges, 5 Contestants and 10 Records created into Table3
Sorry for the length but the last part helps me visualize what I am trying to explain.
End result is I can't get it to work is this a job for a union query ?
:( anyone pls help...
here's my setup i have 3 tables w/ same field(example Field name: PC)
i need to add all the values to another table because i need to get the quantity..
example Table1: 5 Table2:6 Table3: 7
Table4: 5+6+7 (18 should be in Table4 w/ field name Quantity)
can email me at icetumbler03@yahoo.com tnxx...
really appreciate and need asap.
I have two tables
Table1 fields: Date, MoneyIn, MoneyOut, (plus other fields...)
Table2 fields: Date (some <> from Table1, other match), MoneyIn, MoneyOut, (plus other different fields...)
Question: Can I run a query that creates a list with
field1: all dates from Table1 AND Table2
field2: MoneyIn
field3: MoneyOut
Thanks
Hi,
I have two tables.
1. Customer (Id)
2. Transaction (Customer1Id, Customer2Id)
I want to delete all records in Transaction where Transaction.Customer1Id or Transaction.Customer2Id are not matched against the Customer.Id value.
I realise this query is very simple but I cannot get it to work in Access. Any help would be greatly appreciated!!
Thanks,
Ben.
I work for a nonprofit. Everyone has 10 jobs. I am the only person who uses/has used access. I have a donor database listing names addresses, and different fields for donations made/not made for each year. I do simple queries to pull names from the list that our board members would like to personalize a letter to, and the rest goes to a mailing service for addressing and stuffing of envelopes. This year, we would like to send a different letter to those who have donated in any of the last 3 years. I can pull contact info and the 3 fields (donted 2003, 2002, and 2001) together in a query, but not sure what to make the criteria. If I put >1 (since some of my fields automatically have a zero, and are therefore not null), then I only get those names that have donated all three years. Some may have donated two years ago, and not again, some may have donated only last year, or 2 out of 3 years, etc. I know there must be a way to do this, but can't get my head there having only had basic interaction with access. I know by process of elimination how many there should be, so I will know if it runs correctly. Hope this makes some sense. Bear in mind, I am a novice, and certainly not a software person.
View 3 Replies View RelatedHi all,
Another quick Q - am a newbie at Access!
I am performing the following query:
.Open "SELECT DISTINCT([Date]), SUM([Batch Qty]) As [Batch] FROM [" & TableName & "] " & _
"WHERE [Date] BETWEEN #" & sDate & "# AND #" & eDate & "# " & _
"GROUP BY [Date], [Batch Qty]", cn, , , adCmdText
Data Produced:
29/03/200610
29/03/200620
29/03/2006100
30/03/200660
30/03/200680
30/03/20065
30/03/20065
How do I Sum the Totals for the Dates? So it produces:
29/03/2006130
30/03/2006150
TIA
OK, I have two tables that have pretty much the same data in them, but, the first table has SOME data that the second table doesn't and I need to get that data into the table that does not have it.
Here's a description of what I want to do:
Table #1 has about 10,000 lines of data with the employee SSN as the ID for the records. In this table are two extra columns of data (HRContact)and(HR ContactCode) that are not always populated in Table #2.
Table #2 has about 300,000 lines of data with the SSN as the ID field. Some of the records that match the SSN's from Table #1 have the data HRContact and HRContactCode, but not all of the records have those fields populated.
So, what I need to happen is for the query to go through Table #1, find the SSN of a record. As it finds each SSN, it goes to Table #2, finds that same record with the same SSN, then looks in the HRContact field to see if there is data there, or if it is Null. If there is data in that field, then it goes on to the next SSN in Table #1 and repeats the preceeding process. If the data in HRContact is Null in Table #2, then it goes back to Table #1 and grabs the HRContact and HRContactCode data for that record and writes it into the HRContact and HRContactCode field for the record in Table #2. the query would repeat this process until it reaches the end of file in Table #1.
I hope this is clear and if you have any questions, please ask me...
Thanks for your help,
Dave
I have an asset table, an equipment table, and a customer table. The asset and customer table has a field DateOut and equipment table has a field OnHand. I would like the OnHand table to decrement by one each time the DateOut is set to current, Date(). Tried Iif, tried expression builder, now completely lost. Any help would be great.
View 1 Replies View RelatedFirst off let me say that I'm VERY new to Access. I've been put in charge of administering a large (to me) database and I've just started working with the insides of the database for about 3 weeks now.
The most urgent thing though is I need to make an "Honour Roll" report for the employees, that is, they get pass/fail inspections and I'm trying to create a query that returns all employees that have 10 or more passes IN A ROW, with at least 2 of those passes being a personal evaluation vs. an after the fact quality verification. There's already a query in the database that returns the employee ID, total passes, total fails. I've managed to add the field that includes type of inspection, but I can't seem to find a way to count the number of PE's in the field.
The second issue is getting the query to ignore all inspections prior to their last fail. I think I can work out moving the date of the inspection into the query, but then using it is another story. If it requires a script of some sort, then I'm hopelessly lost. The last programming I did was BASIC back in school... many years ago.
If I were to break it down into a statement, it would be something like this:
If (employee passes) >= 10 since last (employee fail) AND (inspection type="PE") >=2, then send (employee ID) and info to a report.
Any help with this is appreciated, I'm starting to get desperate!
In the code of a button, ive got....
Which should be pulling the value in cb1 in the last cat query, but im getting an error saying it cant find the field "|" refered too...
Basically there is a table called Last Cat, and I want to draw the value of cb1 out of it where the catpathid = 5 so i made the query to do that... but now im abit lost....
Me.combo1.Value = [Last Cat Query1]![cb1]
any help would be awesome.
Hi,
I have a database with a query that returns a list of printers. This is created by finding all the printers in the database that take a perticular cartridge by code. Sample below.
PrinterName
-----------------
Some Printer 1
Some Printer 2
Some Printer 3
ETC
What I want to do is take the results from the query and convert it into a single line of text and place it into a column of a table related to that cartridge code.
So if say the cartridge code '12345' returns a list of printers such as;
printer 1
printer 2
printer 3
I want to take this data and turn it into a line like this;
printer 1, printer 2, printer 3
I then want to take this new line and place it in a column called Desc in another table relative to the original cartridge code.
table should look like this after query.
BEFORE:
CartCode | CartDesc | Price | Desc
12345 | Something here | 10.00 |
AFTER:
CartCode | CartDesc | Price | Desc
12345 | Something here | 10.00 | printer 1, printer 2, printer 3
I hope what I have said makes sence. I am pretty new to access and feel I have done quite well so far. Please can someone help me solve this problem?
Thank you
Hello, Really hope someone can help me.
I have 2 lots of info in 1 table - nameley customer address & posting address
I need to print a form that always has the posting address on the same side. but in some cases if the postal address is the same as the customer address then it is not filled in. hope this makes sense:
Please Im Really stuck
Hi all. I am fairly new to access and am trying to do something that shouldn't be that hard. I have a simple form with one text box and a command button on it. I have the command button set to run an update query.
My problem is that when I enter data and press the button, I don't know how to get the entered data into the query?
I know this is simple to do, but since I am new to access, I can't figure it out. Does anyone have a simple example or step by step details that they can share with me?
Thanks
Chris
Hi there - i'm looking for a bit of help on something that is probably obvious to experienced Access users - i've checked the forum for about 25 min or so and couldn't find a related answer to this question.
I have a select query (QRY2) that reads another select qery (QRY1), and I need to (or rather, i'd like to) add a field to QRY1 - how do I get it so i can view this new field from QRY1 in QRY2?
thanks so much for anyone who could help me!
Hi,
just finding my way in Access 97 with no real support. I have made a query that creates duplicate records when I only need to see one for a certain criteria (lets say criteria X). This is due to the data feed involved in making the query where criteria X is found in numerous records.
What I want to display is only one record per entry containing an exact match for criteria X.
Finding it hard to articulate this but I would need something that defines:
If criteria X is the same, just display one record.
Hope this is somehow clear as I am struggling a bit. Would be gratefull of some help.
Thanks
Hi, I've looked all over the forum for an answer to my problem and can't find it. This will probably make regular contributors groan, but I'm totally confused and if someone could help that would be ace.
I've got a popular budget airline website (never mind it's URL, I don't like Spam either) and want/need to move it to Access and thence to run user queries via SQL.
So far so good.
So I set up an access DB:-
I've got three tables:-
Airline Info has three fields:- Airline Code (3 letters, primary key), Airline Name and Airline URL.
Airline Routes has four fields:- RouteID (9 letters, primary key), Airline Code (3 Letters), From Airport Code (3 Letters), To Airport Code (3 Letters).
Airport Info has three fields:- Airport Code (3 letters, primary key), Airport URL and Airport Name.
Hopefully anyone reading is still with me.
I've set up the relationships so that Airline Info.Airline Code is linked to Airline Routes.Airline Code.
Now I want Airline Routes.From Airport Code and Airline Routes.To Airport Code to both link to Airport Info.Airport Code (the ultimate idea being to find the Airport Name), and Access will let me do that but therein the problem lies.
Essentially I want a report that would say (eg) Jet Blue fly from John F Kennedy New York to Los Angeles.
All I can manage is to get "Jet Blue from John F Kennedy New York to LAX". I can convert the From airport code to its name, but not simultaneously convert the To airport code to its name. It's gotta be something to do with the query, but I don't know what and have spent two days on this now Anyone a) understand all of that b) know what's going on?
Ta in advance,
Alex.
I have a very involved question regarding the export of data from and Access front end, SQL back end into multiple Excel Worksheets.
Where is the best place to post this in order to get maximum exposure to the great minds that cruise this board.
Regards
I have a form that has several multiple choice question and i have also designed a table and update queries for the DB so far i can enter the values and then have them changed from the original to the values requested by my bos however i also need to find a way to make a report that will allow me to shot the percentage of people that chose each individual answer with anywhere from 2 to 10 possible choices on each question i see no logical way to do this....... help!
View 1 Replies View RelatedHello every one
I am new here and this is my first post
I am trying to make overtime record for my company
I use the following feilds
field name .................... format
Start_Time ................... short time
End_Time ..................... short time
Total_Over_Time ........... short time
now when i use
Total_Over_Time = [End_Time]-[Start_Time]
I got correct answer
but
while i am trying to make monthly report
if Total_Over_Time is greater then 24 hours it shows me 1
means for 25 hours is shows 1
Please help me to overcome this problem
Thanks
Pehalwan
I am developing an Access (2000) database to provide reporting data gathered from SAP and make it easily accessable for 12 analysts. The data set is HUGE, 5 months nearly a gig and will need at least 2 years worth, so I don't see any option other than using SQL as a back end. I have experiance using SQL server as the back end but have never had to develop my own server.
I do not have access to an existing server but do have permission to purchase SQL software, this is where I need the help. If anyone has suggestions, advice, links or other information I would be greatly appreciative.
Thanks in advance for your help.
“where I added information”
A lot of you have been reading this but no help.:eek: Did I use a poor title? I have used SQL as a back end but really need to know how to produce one out of thin air.
I ordered SQL 2005 off my company's IT store but when reading further it needs a special operating system, I think. Is this true? What do I need to have in order to provide a very large data set to a bunch of barely computer literate analysts?
These people are excited when they realize that vlookup can do more than one thing!! The concept of a union query would blow them away. I am frustrated with the competency of my co-workers but need to give them something that is idiot proof without letting them know that this is the object.
Currently they are having an SAP download sent to a specified share drive and running an Excel “macro”, moves a couple of columns an such, that puts it into a format that is semi-usable. This is totally inefficient for their needs but they don’t know it as yet.
What I am asking is how to take a fairly robust pc and make it a SQL server that will service a dozen or so people. What kind of operating system is needed and what sort of admin rights will be necessary to allow access to the desktop so they can get their data?
The IT help desk is very scary, not really understanding the requests presented. I haven’t been with the company very long but what I have garnered disturbs me I don’t believe the IT people have a clue.
I’ve been an Access developer for about 4 years now and know this is not an insurmountable obstacle. I am confident that SQL is the solution but haven’t ever had to create it from scratch before. Please, links or other suggestions are MOST appreciated.
Preface: I don't know where to post this since I'm using a VBA executed SQL command or if this is a table issue.
I'm trying to import a .csv file into a "master" table of several .csv files. I've figured out how to link them and import them even though they have different field names, but the problem I'm running into is with serial numbers.
Some serial numbers are entirely numbers, while others are alphanumeric. When the field is set to numbers all of the parts (a different field) show up, but not all of the serial numbers are imported. When the field is set to text some of the parts are dropped, along with their associated serial numbers and then some of the serial numbers show up as scientific notation.
Any ideas on how to solve this problem?