I have a stock-type record and a stock-item record.
I want to update a flag on the stock-type record called instock to be true if there are related stock-item records and false if there are no related records.
Does anyone know how to run a updating query that will do this?
I am fairly new to this so I'm sorry if this is a bit of a basic question.
Thank you for your help in advance.
John
I am trying to determine the best method for how to handle this query using Access 2013. I have a clients table that contains the following:
clientID fName lName admissionDate dischargeDate
1 John Doe 05/06/2014 06/27/2014
2 Jane Doe 04/24/2014 05/15/2014
3 Steven Smith 05/15/2014 NULL/Empty
4 Chris Davis 06/12/2014 NULL/Empty
Then there is a WeeklyProgressNotes table that is there for the person that is responsible for auditing the clients charts. It does not contain the actual weeklyprogressnotes, it only contains a Yes/No field and a date field for the date the weeklyprogressnote was completed. Like below:
noteID completed dateCompleted clientID
1 yes 05/08/2014 1
2 yes 05/14/2014 1
3 yes 04/25/2014 2
I am creating a form that the auditor can open to determine what weeks she needs to check for each client to see if they have their weeklyprogressnotes completed that week. The weeks run Mon - Sun and there will be no record in the WeeklyProgressNotes table if she has not yet checked and confirmed for that week. So the form would basically look like this:
fName lName week completed date clientID(hidden)
John Doe 5/19/14-5/25/14 Checkbox Null 1
John Doe 5/26/14-6/1/14 Checkbox Null 1
John Doe 6/2/14-6/8/14 Checkbox Null 1
John Doe 6/9/14-6/15/14 Checkbox Null 1
John Doe 6/16/14-6/22/14 Checkbox Null 1
John Doe 6/23/14-6/29/14 Checkbox Null 1
Jane Doe 4/28/14-5/4/14 Checkbox Null 2
and so on.......
I have thought about creating an SQL statement to select all of the clients and then creating a function that determines their admission date within the specific week and their discharge date withing the specific week and then create a loop with another SQL statement with a BETWEEN clause for all the weeks and determine if there is an entry in the WeeklyProgressNotes table or not. If not then I would display out the above info. I'm not sure if there is an easier, less search intensive way of doing it. Maybe an SQL query that can cut done on some of the looping.
I have one table let's call is table1. It contains about 5 columns with standard employee data with about 10,000 rows of data containing about 1,150 employees. All employees have multiple rows of data. There is one column titled JOB. I need to pull all rows of data for each employee ONLY if there is at the minimum one value for the employee in the JOB column. I do not want to pull employees that have no values in the JOB column. They can be excluded from the query.
Example of all record for one employee. I need to pull all rows of data ONLY if the employee has a value in the JOB column.
Name EE_ID JOB CAT EFF_DATE
John Doe 1006 CLERK F 01-JAN-2010
John Doe 1006 P 21-JAN-2010
John Doe 1006 CLERK P 01-FEB-2011
John Doe 1006 F 01-MAR-2011
John Doe 1006 P 01-APR-2011
John Doe 1006 CLERK F 01-JUL-2012
I have one table let's call is table1. It contains about 5 columns with standard employee data with about 10,000 rows of data containing about 1,150 employees. All employees have multiple rows of data. There is one column titled JOB. I need to pull all rows of data for each employee ONLY if there is at the minimum one value for the employee in the JOB column. I do not want to pull employees that have no values in the JOB column. They can be excluded from the query.
Example of all record for one employee. I need to pull all rows of data ONLY if the employee has a value in the JOB column.
Name EE_ID JOB CAT EFF_DATE
John Doe 1006 CLERK F 01-JAN-2010
John Doe 1006 P 21-JAN-2010
John Doe 1006 CLERK P 01-FEB-2011
John Doe 1006 F 01-MAR-2011
John Doe 1006 P 01-APR-2011
John Doe 1006 CLERK F 01-JUL-2012
Sorry this is so long but I am trying to make it as detailed as possible...
The Setup
My database is similar to the sample "Service Call" db. I have a main table called "tblTT" which has an autonumber primary key. The table also contains several foreign keys such as UserID (not an autonumber) from "tblUser", TechID (also not an autonumber) from "tblTech", etc. I have the relationships setup with "Enforced Referential Integrity" for both updating and deleting records. Each relationship has a RIGHT OUTER JOIN ("Join 3" in access) so that all records from child (tblTT) and only equal from parent (tblUser, tblTech, etc.) will be included. I have a form for nearly every table which serves different purposes but the main function of the DB is to create new Trouble Tickets (TT's), a.k.a. service calls. Therefore the main form used is my "frmTT" form in add mode. The form contains all the fields from my "tblTT" table and contains (directly) no fields from any other table (I guess indirectly it contains fields from all the parent tables...).
The Problem
When I pull up "frmTT" and try to create a new Trouble Ticket for a user that does not yet exist in the table "tblUser" I get the error "You cannot add or change a record bcause a related record is required in table 'tblUser'".
What I want is for my users (the "Techs") to be able to create a new Trouble Ticket without having to worry about populating the "tblUser" table (and other parent tables) first.
My Solution
I was going to (and unless someone can find an answer for me still will) fix this using VB script by setting up a query to check all the parent tables for the values in their corresponding fields in the form. If the query returns no results an "INSERT INTO" statement will run to populate the parents tables so that the form will save itself into the Trouble Ticket table ("tblTT").
I feel that this is a huge work around and not the proper fix. I would prefer to do this the right way both to have a correctly setup DB and for future reference. Can anyone help me with this?
Thanks,
Andy
Here's a query that the bottom listview in the attached form i.e. a listview representing a table of calls(many) to fims (1 top listview)
Code:
SELECT calls.id, calls.firm_id, calls.called, calls.said, calls.spoke_to, calls.next
FROM calls
WHERE (((calls.firm_id)=[firms].[id]))
ORDER BY calls.called DESC , calls.next DESC;
When I run the thing...I get a dialog asking me for firm id.
I want to change this so when I move up and down the firms LV (top)... the bottom LV updates taking firm id from the top LV with focus.
Access 2003.
Hi, just so you are aware, I am totally clueless when it comes to Microsoft Access. I tried finding tutorials and help online that I could use to do this but I'm having trouble understanding what I'm supposed to do. I have two huge tables linked to each other by an ID number. One table contains multiple records with the same ID number while the other does not. There are records in the first table that don't appear in the second one, and there MAY be entries in the second table that don't appear in the first table. The second table has a flag that I would like to add to records in the first table that appear in the second table. What is the easiestt way to do this? This may be an extremely stupid question or it may be very hard, I'm not sure which, though I'm leaning toward it being a stupid question. Thank you in advance for your help.
View 4 Replies View RelatedHi,
I'm trying to have a button on a form that would delete a record from a table only if there are no child records linked to it.
If there are child records for the selected record, the button would be disabled.
Is there a simple way of doing this?
TIA.
I am creating a purchase request form for my company. The main form contains the PO Request # (autonumber) and general vendor and shipping information. The subform contains the item numbers on the order, the quantity and costs. What I am trying to do is make it so that users can click a "duplicate" button on the main form of an existing PO Request and have all the data replicated into a new PO Request. A lot of what we order is repetitious and it would time saving if users could just duplicate an order and modify as necessary. Can anyone help me with this one...
Thanks!:confused:
I have modules and procedures. A module can consist of one or more procedures. A procedure can belong to one or more modules.
tblModules
--------
ModID
ModName
tblProcedures
----------
ProcID
ProcName
ModProcs
--------
ModProcID
ModID (FK)
ProcID (FK)
In a listbox I have a one or more modules selected and will construct the SQL in the AfterUpdate, presumably.
How to get a query to list only those procedures that belong to all the selected modules? To get into the result list a procedure must belong to all the modules that were selected. The code below gives me all the procedures that belong to at least one of the modules selected. I need ALL.
Code:
strIN = Left(strIN, Len(strIN) - 1) ' list of ModID's of selected in listbox
strSQL = SELECT tblModuleProcs.ProcID
FROM (tblModules INNER JOIN tblModuleProcs
ON tblModules.ModID = tblModuleProcs.ModID)
INNER JOIN tblProcedures ON tblModuleProcs.ProcID = tblProcedures.ProcID
WHERE (((tblModules.ModID) IN(" & strIN & "))) "
ORDER BY tblProcedures.ProcedureTag;
I have an unbound new contact data entry form that adds records to many different tables. I have no problems adding one record to a child table at a time, but one of the things we are collecting is a list of online networks a person belongs to, and I want to be able to stick a "check all that apply box" on my form and have it add multiple records to the child table. Here's the basic set up, I have a parent table with the main contact information with a primary key field "IID", a lookup table with "Facebook, LinkedIn, Twitter..." etc in it with a primary key field "online_id", and a bridge table to link the two which should have multiple entries for IID, one for each online_id. I want to use a listbox (or something like it) that the user can select multiple online networks and then have records added to the bridge table. I can figure out how to add the listbox on the form, what I can't figure out is how to get the values out of the listbox. T
View 13 Replies View RelatedI have a table with numerous fields including timeIn and timeOut field (these are date/time format). I need to find out how many people were clocked in during each operating hour (based upon their time in and time out). How can I run a query that will allow me to use the times in and out to 'flag' each hour that each person was on duty (a new field for each hour) so that I can run a count on the completed data for every hour?
View 4 Replies View RelatedI created an update query that says it has successfully updated 600 records, but when you go into the table, it has not been updated. I am working with an .mde. Does that make a difference?
I first created a select query to find everyone in TX. Then I changed it to an update query to change all TX to CA. Since it did not update the table, I tried running the update query again and this time it says there are not records to update (since it uppposedly updated my 600 records).
Your help is appreciated.
Can anyone help me with this - I have an append query to a statement table for invoices with a macro set. When the query is run the data is added to the statement query.
what is happening is that it is adding the data ok on a seaparate line but is also updating any previous invoices for the same client to the new invoice number obviously not what I want.
many thanks
Regards
Humph
Hi,
I am trying to implement an update query per the instructions at http://support.microsoft.com/default.aspx?scid=kb;en-us;127977 to append records from table2 to table1.
I would like to add a criteria here saying update (and append) only records that match the criteria table2.dateofreview <= #12/17/2006#
This does not seem to work. The same query works without this criteria.
To test this, I created just one record in table2 (table1 is blank at this time) and manually entered the dateofreview as 12/17/2006 and tried to execute the query. It is not able to show any records.
Won't this query work *with* a Criteria?
Table in my access 2010 is configured to have a unique records (no duplicates)which has now records more than 2000 so i copied the table and pasted Structure only. what i would like to have is that new table which presently is empty should not add any record which is already available in old table. While entering data in new table i would like users to see the error if they try to enter the record which was previously entered in old table.
View 8 Replies View RelatedI have a list box whose data is inserted in a table named as "test0" ,now in a macro of vba i want to select all the items in the list box and create there pdf files in a folder at my desired location. So far i have managed to create a pdf file of single item ,but i want to select multiple items at once ,
Code:
SELECT test0.ID, test0.item FROM test0 WHERE (((test0.item)=[ItemNumber]));
So, in this query itemNumber are multiple and i want to create there pdf files at once ..just on a click of one button ?
Hi
I have a parent-child base table (accounts) with the following fields:
- id
- parentid
- name
every record's parentid is either 0 ( which means it is in the most upper level) or another record's id (which means it is the other record's child)
different levels of records are being used. I mean some accounts are used in the most 1st level with no childs, some in the second level, ....
I need a query to list the accounts which are not parent to any othe records, regardless of what level they are defined.
Thanks again for the help.
I have a parent table (tblLabels) and a child table (tblRevision) where the revision history for the parent table is kept.
The parent table is populated via an excel import and may have several records imported at once. Instead of having the user manually enter a new record note in the child table for each record imported into the parent table, I've created a form that collects the necessary data (date, person who added the record, person who authorized the record, and notes) and then creates a revision history for each new record.
This is what I have so far:
Code:
Private Sub cmdAddNotes_Click()
Dim strSQL As String
Dim RevisionDate As String
Dim RevisionRevisedBy As String
Dim RevisionDesc As String
[Code] ....
When I run the code nothing happens. No error, no new records create, etc. My suspicion is that I may need to use an INSERT INTO query instead of an UPDATE query, but I'm not sure how to go about matching up the foreign keys if that's the case.
I'm trying to create a capacity report for my database. Originally, I only had the one table, which summed the number of Packs and number of Images, worked out percentage capacity used and then put it in a report, showing for each week of the year. However since then, I've added a child table to this, allowing me to create subrecords. The reason we did this was so we could easily group together multiple mailings under one single master record, so to speak.
Going back to the capacity, I've managed to work it so the Pack capacity is worked out from the mailing quantity in the subrecord. However, the number of Images is only entered onto the Parent record. Now my capacity query is summing the same number of images as there are subrecords, where in reality I only want it to sum the Images in the main record. I know I could add an Images field to the child table and work it out the same way, but I'd rather not do that (because that's not how our business works).
So essentially the question is, how can I sum the Images from the parent table without repeating the sum and massively overexagerating the sum?
Hi, i have a simple query but for some reason it's not working.
Let me get to the point.
2 tables - table1(id,txtfield1) table2(userid, idfield)
idfield contains id so foreign key (although hasnt been set up as that yet)
Aim: select all fields from table1 where table2 doesn't contain them.
e.g if table 1 had, 5 rows containing:
1 - test
2 - test2
3 - test3
4 - test4
5 - test6
and table 2 had:
1 - 4
2 - 5
Then when the query ran, it would only pick up 1 - test, 2 - test2, 3 - test3.
Any ideas. I have tried but with no luck:
SELECT [ID], [txtfield1] FROM table1 WHERE NOT EXISTS (SELECT [idfield] FROM table2)
I'm using an UPDATE query to update records in one table (tblMain) from another table (tblTemp)
Here is my SQL :
Quote:
UPDATE [tblMain]
INNER JOIN [tblTemp] ON [tblMain].[MainField1] = [tblTemp].[TempField1]
SET [tblMain].[MainField2] = [tblTemp].[TempField2];
I only want to update the records in tblMain which have a corresponding record in tblTemp (linked by MainField1 / TempField1)
If any record doesn't appear in tblTemp, I want tblMain to retain the existing value for that record.
However, it appears that in such situations, the record in tblMain has it's MainField2 value set to null / ZLS.
I've tried using LEFT JOIN and RIGHT JOIN and also tried WHERE clauses but the result is the same every time.
I have a multi table query that I am looking to retrieve a single record from
However one of these tables has multiple records for each previous record so when I run my query, I am recieving results like this
value1 value2 value3 value4
A A A 1
A A A 2
A A A 3
A A A 4
B B B 1
B B B 2
B B B 4
I have a finite number of values that value4 can equal so I would like to have my results come up like this.
value1 value2 value3 value4(1) value4(2) value4(3) value4(4)
A A A True TRUE TRUE TRUE
B B B True FALSE TRUE TRUE
Hello all,
I have a form that looks at my "master table" where users put in general info about a sheet of material.
There are two combo boxes on this form, Batch# and Lot#. They will use the same Batch# and Lot# for many sheets. Every time a new batch and lot are entered, this will bring up a pop-up form where the user needs to take a measurement of that batch and lot for QA. This will go into a different table, where the batch and lot #'s are unique(many of the same batch#'s with different Lot#'s).
After the lot# is entered, I want to run a query in VB that looks for those unique identifiers, and if it doesn't find a record, my pop-up form will run.
This is probably something very easy....... It was a very long weekend and I'm tired of thinking.
Thanks
Scott
Hi guys...got a problem here....
I got a query which joins two table....
Individual
fid1
fQSname
fQSRegno
Point
fid2
fQSRegno
fQSPoint
Tables join using fQSRegno.
Scenario is
Tbl Individual contains all the members info.Tbl Point contains point given to members who attend courses. Problem is when I created a query...
Query1
fid3
fQSname
fQSRegno *from tblPoint
fQSPoint
the data shows only members who got points. Is it possible to include member who don't have point like new registred member to show in this query....
TQ
I have a module that I am using to verify that file paths in my table point to valid jpg files. The table has grown quite a bit since I first created the module and it has gotten to the point where it's taking too long for the module to execute. I'm in the process of trying to change the record set for the module from the table to a stored query procedure but it's turning out to be a little tricky for me. When I execute the following module, I'm not getting any error code, it just doesn't seem to do anything at all. The bits in red are the parts I've changed. Before that the module executed as I expected it would.
Code:
Sub TestIt2()
Dim strFileName As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb()
Set rs = db.OpenRecordset("QryUpdateRevisionHistory")
[code]....