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.
I am trying to create a hyperlink from other values in a number of fields, I have found a thread which I think anwers my problem:
Link to Thread (http://www.access-programmers.co.uk/forums/showthread.php?t=124117&highlight=hyperlink)
Only problem I is I don't really understand it. :confused:
e.g.
Dim stGunlib As String
Dim stGunlib2 As String
stGunlib2 = Me![Stock number]
stGunlib = "#http://www.shooting.uk.com/Gunlibrary/" + stGunlib2 + ".jpg#"
Me![Link] = stGunlib
Me![Picture] = stGunlib
What does Dim, Me!, As String mean? and where would this code be entered, is it part of a module or just in the properties of a table/form etc?
Any help on explanations of this would be greatly appreciated!
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!
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
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
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.
Newbie question: In datasheet view, I don't see an option to save the data as a table (so I can use the table for other operations).
For example, if I create a query in design view, the "Save" option only seems to save the query-syntax, not the actual data. So I end up having to first export the data to Excel and then import it as a table. Seems kind of silly the idea of having to rely on a spreadsheet to build a database table. Can't I use the database to build a databae table?
On a related note, I had hoped to use one query as a "database object" accessible from a second query. What I mean is, having saved Query1, I wanted to write: SELECT FROM Query1 Where... but I got an error when I tried this.
First query = Sum Products:
Code:
SELECT Sum(Tab1.Inputs) AS SumOfInputs, Sum(Tab1.ValInp) AS SumOfValInp, Sum(Tab1.Outputs) AS SumOfOutputs, Sum(Tab1.ValOut) AS SumOfValOut, Products.Product, Products.VAT, Products.UM
FROM Tab1 INNER JOIN Produse ON Tab1.ProductID = Products.ProductID
GROUP BY Products.Product, Products.VAT, Product.UM, Tab1.ProductID;
Second query :
Code:
SELECT Nz([SumOfInputs],0)-Nz([SumOfOutputs],0) AS Stoc, Nz([SumOfValInp],0)-Nz([SumOfValOut],0) AS ValStoc, IIf([Stoc]=0,0,([ValStoc]/[Stoc])) AS CMP, [Sum Products].Product, [Sum Products].SumOfInputs, [Sum Products].SumOfOutputs, [Sum Products].SumOfValInp, [Sum Products].SumOfValOut, [Sum Products].VAT, [Sum Products].UM
FROM [Sum Products]
GROUP BY [Sum Products].Product, [Sum Products].SumOfInputs, [Sum Products].SumOfOutputs, [Sum Products].SumOfValInp, [Sum Products].SumOfValOut, [Sum Products].VAT, [Sum Products].UM
HAVING (((Nz([SumOfInputs],0)-Nz([SumOfOutputs],0))>0.09 Or (Nz([SumOfInputs],0)-Nz([SumOfOutputs],0))<-0.09));
I need to combine those two query sql code to make only one query.
I have this code from a Query that works.
SELECT Contacts.*,Contacts.Address1
FROM Contacts
WHERE (((Contacts.Address1) like "*" * (Enter 1st Line of Address here) & "*") AND ((Contacts.Site) = "Miscellaneous"))
ORDER BY Contacts.Site;
I want to replace the data "Miscellaneous" with a Public variable strSite.
If you can help I would be very happy and of course grateful.
Thanks
Ted :confused:
I have been asked to help with a database that someone is writing.
He has a Table that has 200+ columns and approximately 70 of those columns are Qualifications. The first few columns are Title, Forename, Surname.
What he is trying to do is query the table to find all people with a specific Qualification and that is a Mr.
If I had written the db from the start then I would not have produced so many columns for qualifications. Instead I would have 1 column for quals and the populate each record in this way.
My question to the forum is the following:
With the current structure of his table (200+ Columns) Is there a way that I can produce a Query that will find the results that he requires, 1 specific Qualification and Title, but without displaying all other details in the table?
I understand that I can choose what field to include in the query but what I really do not with to do, is produce 70 different queries in order to filter each qualification.
Any and all help with this question really is appreciated.
Thank you
Bev
Hi
I have three fields on the form. First one is Combo box - "Contact person", Second - "Code" is Text field, Third - "Department" is the text field. Corresponding to this I have a table "Department "with fields Contact person, Code, Department.
I want that when I select the Name from the combo box, corresponding records like Code and Department should appear in the respective text box.
Help me in writing this code.
Thanks in Advance Very much
Deepak
Hi
I have three fields on the form. First one is Combo box - "Contact person", Second - "Code" is Text field, Third - "Department" is the text field. Corresponding to this I have a table "Department "with fields Contact person, Code, Department.
I want that when I select the Name from the combo box, corresponding records like Code and Department should appear in the respective text box.
Help me in writing this code.
Thanks in Advance Very much
Deepak
I am using the two lines of code below Each in separate columns of a query (or two text Boxes on a form) to calculate the elapsed time between two dates formatted as General Dates. The first line of code (in column A, "txtTime1") calculates to the total minutes between the two dates, the second line of code (in column B, "txtTime2") takes the minutes and converts them to total elapsed hours and minutes. They work great, however, I would like to combine this code and place it in only one query column or form Text Box. My VBA is not up to it, can someone out there show me how to combine the code below to produce the hours and minutes in one line of code? Thanks
=DateDiff("n",[StartDate],[CompleteDate])
=[txtTime1]60 & Format([txtTime1] Mod 60,":00") :confused:
Hi
I have three fields on the form. First one is Combo box - "Contact person", Second - "Code" is Text field, Third - "Department" is the text field. Corresponding to this I have a table "Department "with fields Contact person, Code, Department.
I want that when I select the Name from the combo box, corresponding records like Code and Department should appear in the respective text box.
Help me in writing this code.
Thanks in Advance Very much
Deepak