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 RepliesI 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
First 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
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.
Great forum!! Hope I someone on here can help me!
Here's the situation. I have a Database in Excel (~3000 rows & 7 Columns)
The first Column has numbers like this (3-10-34 or 223-7-45) the other columns have names, addresses and other stuff.
What I'm trying to do is to eliminate the use of huge binders. I want to have a Blank screen with only one input box where I can type a number and retrieve a specific row from my Database. Time saving.
Now, when I'm searching, I usually only have the first numbers before the dash (such as the 3 or the 223) not the other numbers. So I cannot input 3-10-34 for instance. I can only put 3...:confused:
I've already imported it to Access and it works fine but have no idea how to do what I just described!
Thanks for the input!!
A very very green access user! :o
Hello
sorry if this question is answered somewhere else i did check through the forum a bit first
anyway to the question
is there a way in access 2003 so when i change a value in one table it creates a new record in another table
example
i have a table called tickets and one called fixes, when i change a value in a record in the tickets table i want a new record in the fixes table to be created for me
any help would be great
ADAM
Hi there,
I am not sure if i am supposed to post in this section......
--------------------------------
Using Access 2003.
I am very new to Access and have started off very well building my first project..
My help is that i would like a macro button to delete a record once you have put in to the database if that is possible also i would like a save button if that is possible...
I have put a Tab Contol on and want to change the backgound but i can't see on the properties box to change this. Is there a piece of code that allow me to do so...
Hope some one can help
Emailjunky
Hi There All,
I am still very new to Access 2003 and have started off very well..
Just a few help please:
Help 1. I need a macro button that will allow me to delete a record from the database via a form.
Help 2. I need a macro button to save the database if that is possible via a form.
Help 3. I have a Tab Control on my form and i want to change the background of the Tab Control but can't find it on the properties is there a piece of code i can put in to VB which will do this for me or is there another way.
Hope someone can help
Emailjunky
hello guys i m very new to access and i need some help!
if i had a form that contains a field like salary and i need to add all the salaries from all the records into one total salary how can i do it?
plz take into consideration that i m totally new and dumb:p in access
thank you
Alright here is what I am trying to accomplish. I have A, and B. Then I have 6 different tables that relate to the six different things B could be and contain all sorts of other information.
What I want is to make a form that lets you type in A, and B. Then the correct table/form for the respective B will pop in to the subform area. Then as soon as you select or type in a new A and B, the subform will change, or stay the same if the B has not changed, but a new record will have started.
"A" is just an ID... something like AA001, AA002, etc.
"B" is the stage each ID is at... like beginning, middle, end, etc.
Sorry if it is a bit confusing, I am willing to explain any details that could help. I consider myself a somewhat advanced Access user, but this just stumps me for some reason.
Hello all,
Im looking to create a frontend and backend for a Dojo (Martial arts center).
Basically, it will log in people(record their training time, by day not hour) either by Number or name.
Secondly, I want to be able to have all these fields seen by the admin:
Dojo Number
Date Joined
Last Name
First Name
Male/Female
Address
City
Zip
Home Phone
Work Phone
E-mail
Birthday
Occupation
Adult/teen/child
Rank
Active/ Inactive status
Martial Art Exp.
Staff Y/N
ASNJ Dues Info
USAF Dues Info
USAF Membership Number
Misc. Notes
So the backend/ admin would be able to update this field and keep records up to date.
There would be around 800 entries/people, and only one admin accessing the db at any time. The frontend would be used to log in..
Hi,
Can some kind soul help with pointing me in the right direction to a problem below?
In my database (property database), I have some old properties which are now sold. There are relationships between property, rent received, billing, lessees, etc. I want to be able to keep the details of the 'sold' properties.(i.e., who owned it, what they paid in the past, when they paid etc. in case I need it, but don't want it shown any longer on the main working database). I want to be able to delete the properties that are sold from the main table. Any ideas how I go about this? Hope I've explained myself ok.
Thanks
HMC