Tables :: Conditional Data Entry Into Tables

Oct 25, 2014

I am new to Access in a way, and developed a small tool as a warehouse management system. the system is working great but I need to add a rule or condition to this tool.

I have Tables having warehouses locations (LOCATION1,LOCATION2, LOCATION3, etc...)

Some items (products) will be entered into a transaction table where with queries and reports, can see each item in which location it is or in a particular location what are the products stored in.

however, if there is no limitation or condition in a way or another into the access application to limit users from entering an item OUT in the transaction from a location which doesn't have this product, I need access to tell me a message or stop me from entering that transaction.

To explain more, if I have ProductA in LOCATION1, and one of the data entry guys entered a transaction in LOCATION2 for the same ProductA, access will not allow him since there is no quantity of that particular product in that LOCATION2.

How to do it? I checked in Data Validation and Validation rules, but its either hard for me to understand or am not getting how to do it?

View Replies

General :: Conditional Combination For Data In Tables

Jun 12, 2014

I have specific query related to Access, Currently I'm trying to create all possible combination of lot data. I need to know if this would be possible in MS Access.Below is simple example of what is required

Table1 Table2 Table3
Value1 TokenW GateA
Value2 TokenX GateB
Value3 TokenY GateC
Value4 TokenZ GateD

I have 3 sets of data for which I've to create all the possible combinations but with restrictions.Below are few of my restrictions

1. "Value2" always comes with "GateC" & Vice versa but, both don't exist independently.
2. "Value4" always takes "TokenW", But "TokenW" exists independtly as well doesn't need Value4 always.

View 1 Replies View Related

Queries :: Conditional Query Pulling Data From Two Tables

Mar 6, 2014

So I have two tables:

users
primary key = user_id (AutoNumber)
surname (Text)
forename (Text)
...

user_change
primary key = user_change_id (AutoNumber)
user_id (Number) which relates to the user_id from "users" table
change_type (Text)
action_date (Date/Time)
...

In user_change I record any changes made to the users table.

What I am wanting to do, within a query, is pull basic details from the "users" table (forename, surname, etc.) which is working fine, but also add in SPECIFIC data from the user_change table if it exists.

I want to pull ALL rows from the users table, not just specific rows, and not just rows where my criteria for the user_change table match.

This is the data I want to pull from user_change...

The MOST RECENT action_date WHERE change_type is LIKE "*issued*".

However there won't be a change_type LIKE "*issued*" for everyone - I want it to be included only if it exists.

At the moment my query is ignoring any users who don't have a user_change record with "*issued*" in the change_type value. I'm also getting duplicate user rows where people have more than one value for "*issued*" - I only want the most recent one...

View 7 Replies View Related

Data Entry In Different Tables

Jan 20, 2006

Hello,
I understand that this is against any database rules but I need to add data to my database concurrently in two tables.

I will use one form (frmMaster) and write my data to my main table (table1). At the same time, I need some of this data to be stored into another table (table2).

I do not want to use an append query.

Is this possible via code withough having to close the active form?
Can anyone help with with some code?

Thanks.

View 6 Replies View Related

Data Entry From 1 Form To Many Tables

Sep 10, 2004

Can I use subforms to create ONE form that will do data entry to more than 3 tables at once in a relational db? How can I do this? Do I have to use Javascript, etc?

View 3 Replies View Related

Data Entry Form - Multiple Tables?

Aug 18, 2006

I have been trying to figure this one out for a while and I can't seem to find anything helpful. I'm going to present a simplified version of my problem to help keep it simple.

I have an "Activities" table that contains fields:
ActivityID
ActivityName

I have a table of 7 Risk areas which contains fields:
RiskID
RiskName

What I need to do is to create a form that will allow the user to select an activity and then, for each of the 7 risk areas, input a risk measurement.

This involves the creation of a 3rd table to collect the user input which would contain fields:

MeasurementID
RiskMeasurement
ActivityID
RiskID

I can do a dropdown of the Activities and have a subform that links to it but the problem I'm having is trying to get that subform to list the 7 Risk Areas AND collect the RiskMeasurement information from the user. How can I have a listing of these 7 areas from one table that are tied to inputs that go into another table? This seems so basic and simple yet I have tried numerous things and all have failed.

I hope this was clear. Any help is appreciated.

View 14 Replies View Related

Tables :: Multiple Data Entry Formats

Mar 26, 2014

I am having an issue with a database I'm creating. It is a root cause analysis database. One of the fields is Target_Date. I need to set up queries that alert us if the target date is coming up, (in a week or so), or if it is past due. The issue is that some target dates are simply, "Next Run", which is to say that the corrective action must happen on the next run of the particular item which, at that point, has not been scheduled.

In order to do the above query, the dates must be formatted as Date/Time as <date() will bring up all dates regardless of whether they are in fact less than today if they are in text format. However, this restricts the ability to put in the target_date of "Next Run". Next Run will be required for another query in which we will specify the products coming up and it will let us know which "Next Run" items associated with those products we will do.

Essentially, I have taken the long route to ask, is it possible to create a field format that measures dates as dates and text as text?

View 3 Replies View Related

Tables :: Data Entry In Matrix Format

Nov 25, 2012

I am designing a little database to hold physical activity log info for 2 school districts, required by a grant. Four-five times each year every kid (2000) will complete a log form for 3 days that lists separately for 34 intervals (1/2 hour each, from 7am - midnight) in which physical activity s/he engaged (59 options) and the level of effort of each (4 options, from light to very hard).

The log sheets look like a datasheet with 5 blank columns: #1 enter the activity code (1-59) and #2-5 mark an "X" in the column describing level of effort (light to very hard). There are 34 rows, each labeled from 7am thru midnight in intervals of 30 minutes. The kids **have to** enter an activity code and a level of effort on every row. They do this for three days in a row, 4-5 times during the year. (believe it or not ... federal grant requirement)

.........................#1 .......... #2 .........#3 ........#4 ....... #5
TimePeriod ......ActivityCode .Light ....Moderate .Hard ...VeryHard
7-730........ | .................. | ....... | ............ | ...... |............. |
730-8........ | .................. | ....... | ............ | ...... |............. |
8-830........ | .................. | ....... | ............ | ...... |............. |

I am maintaining survey responses (also 4-5 times a year) in this same database. I have a basic demographic table (Id, name, grade level, school). I am also maintaining eating habits (4-5 times per year) from food logs, like the activity logs, but these are for 5-7 days at a time. Finally, I will be importing number of steps walked for 4-7 days (also 4-5 times each year) from pedometer downloads, one per kid.

I have all tables and data entry forms except for this activity log set up, all data entered for the baseline period. What I would like to do with the activity log is something like a main form showing kid name, ID. I think I want to enter each row of the log (34 rows) into something like a datasheet, each row with a date field (I will use CTRL-' to copy down for one page of the paper log), and an activity code field (1-59 as codes) and a level of effort field (1-4, light to very hard).

What I think I want is one table for activity info, each record with these fields:

Kid_ID
Date_Logged
Time_Interval (these are the 34 rows, 7am - midnight, in 1/2 hour intervals)
Activity_Code (options 1-59)
Level_of_Effort (options 1-4)

I can see this in a datasheet format, but entering 2000 kids * 3 pages * 4-5 times a year [* 3 years] into Excel does not seem to be an efficient solution.

If I could open a form on the screen with a combo box to pick a kid and then pick a date, then fill in 34 rows of activity codes (1-59 as options) with a level of effort for each (1-4 as options), that would be great.

My obstacle right now is how to display 34 empty rows already loaded with an index related to the time interval, so that I don't have to enter that for each row -

Hence my subject descriptor, "data entry in matrix format". I am using Access 2007, Windows XP Pro (all service packs etc. up to date).

View 2 Replies View Related

Tables :: Lookup Queries For Data Entry - One Table To Another

Oct 8, 2014

I am creating a table for data entry. Three fields in the table are going to be Firstname, Lastname, and Address. I want the choices for data entry to be read from a master table which contains first and last names and town of residence.

That being said I would like the choice of Lastname to be all last names from the master table, and the choices of Firstname to be those from the master table but are limited to having the Lastname as entered in the previous field, finally I want the Address field to be limited to those records which match the lastname and firstname. I have been playing around with lookup queries for each of the fields to no avail.

View 1 Replies View Related

Forms :: Creating Form For Data Entry On Three Tables?

Nov 19, 2013

How do I create a form that will allow me to enter data into three different tables?

I have a rather simple database for tracking students. When I get a new student I need to take information off their paper application and enter it into three separate tables, Student, Families, and Demographics. Each of these tables contains the fields StudentID, StudentFirst, and StudentLast.There is a relationship between the StudentID field on each table, with Student being the main table and Families and Demographics coming off of it. All relationships are one to many. StudentID is the key for the table Student.

I want to create a form to add a new record to each of these tables. I want my data to be displayed in the Columnar style. I only want to enter StudentID, StudentFirst and Studentlast once and have it populate to all three tables.

I can create a form and a query that displays existing entries exactly like how I want to enter them, but of course I can not edit or add to them. I have tried using subforms, but they don't seem to link up. Once I enter the Name, I want it to be on all the forms. I don't want to have type it three times. I also tried creating three separate forms and connect them with the Navigation Form. The issue again is that once I enter the name in one form, it is not automatically on the next form.

View 3 Replies View Related

Tables :: Lock Record From Erroneous Data Entry Or Editing?

Apr 12, 2013

Is it possible to have a 2 step confirmation process.

I have a yes/no control box to confirm that the record is OK or correct.

Is it possible to have a second process where by I can lock the record from erroneous data entry or editing? Like a "Post" in accounting.

View 3 Replies View Related

Tables :: Populating Tables On Entry To Another Table

Apr 12, 2013

I've got a working database with pretty good structure etc. How I would go about creating 3 prepopulated records on a many side of a relationship on entering a new record in the one side of the relationship.

View 6 Replies View Related

Tables :: Linking Field Data Between Tables And Within Tables

Sep 26, 2012

Currently trying to build a database for customer management and order placement/tracking. Want to set a couple of rules so that if I for instance click yes of billing and shipping address the same that the database will automatically fill the shipping address with the data I inputted for the billing address in the same table.

The other issue I can see I'll run into is, I want to be able to select one of the company ID's (made up of a three letter abbreviation of the full company name) in the product ordering table and it will automatically fill in the rest of the customer data (phone, email, address etc) data into that form.

View 2 Replies View Related

Conditional From 2 Tables In Query

Jan 3, 2007

Happy new year all. I have 2 tablesI have a query that if any tbl2.num=tbl1.num AND tbl2.action = 'confirmed' this row should be selected for deletion from tbl1. I've tried linking the tables and using an iif statement, but no good and I'm not sure what else to do- any ideas?Forgot to add- tbl1.num might or might not exist on tbl2

View 2 Replies View Related

Tables :: Conditional Values In The Table?

Jan 30, 2013

I have two tables "Funds" and "Classes" with one-to-many relationship, resp. (through the "Fund_name" fields). There is the field "Active" in the "Funds" table and the field "Open/Closed" in the "Classes" table. What I would like to do is to put "No" value into the "Active" field record if the corresponding (with respect to the relation of course) "Open/Closed" field records have all values "closed". Otherwise put "yes". Is there any way, how I could make this?

View 14 Replies View Related

Tables :: Two Identical Database - Importing Tables By Linking To Data Source

Dec 3, 2012

I have 2 identical database in terms of structure but it differs in data.

Basically I would like to import data from subDatabase to mainDatabase and ensuring that there are no duplicate records.

I have used the "link to datasource method" through importing the tables to have the "updating" function.

However, this method also means that the records in mainDatabase are also imported over to subDatabase which I do not want.

Is there a method to ensure that the records are shared/update one way only? (i.e. import from subDatabase to mainDatabase and not main to sub?)

View 1 Replies View Related

Tables :: Calculated Fields As Data Type In Tables - Calculating Total?

Apr 23, 2013

I am using calculated field as a data type in access 2010.

They are working fine.

However, I added a new field and now the final calc won't work.

I have Subtotal adding loads of fields together. Works fine.

Then I have a VATunit field which is a double integer, so enter 20 and my next field is VATTotal calculates the SubTotal + the VATunit by doing (Subtotal/100)*VATunit. This calculation is fine and gives me the correct amount.

The next field is a Total field. Which adds Subtotal and the VATTotal together. Howver, the Total is the same as Subtotal. It is not adding the VATTotal to it?

View 2 Replies View Related

Tables :: 2 Similar Tables / One Takes Too Long To Append Data

Jul 19, 2014

I have being playing with ms access but I really don't know much about it or databases in general.I have created a very simple database to gather twitter following/followers data for research purposes.One table (table01) has a field for the "boss" user (=the user who I gather data for), another field for "client" (=bosses followers or friends).Both fields are numeric and contain the users id's.In order to distinguish if the link is follower or friend there is a third field, called type which can be either 1 (=follower) or 2 (=friend).So the data would look like this:

boss - client - type
12345, 67890, 1
12345, 54321, 2

If user with user id 12345 had a follower (type 1) with user id 67890 and a friend with user id 54321...In order to avoid getting duplicate rows I also added a unique identifier which is of the form boss_id-user_id-type.So the above row looks like this:

12345-67890-1, 12345, 67890, 1
12345-54321-2, 12345, 54321, 2

That works just fine.For several reasons I also needed data of the form source - target.So I also made another table (table02) of this form.

67890, 12345
12345, 54321
...

In table 2 you don't need the "type" field since the position of the user id shows the type of relationship.Still, you need a unique identifier in order to avoid duplicates, so I added on with the form: source_id-client_id..So table02 lookes like this

67890-12345, 67890, 12345
12345-54321, 12345, 54321
...

Both tables also have a date/time stamp for each line.As you can see, table01, having also a type field is bigger than table02.The problem is when I try to append data, exactly the same data in both tables.Appending data to table01 is ok, while appending data to table02 (which is smaller, having one less field) takes a really long time, maybe 10 times as long as appending data to table01.To make sure that no query's are causing the problem I have tried first creating temporary tables with the data to be appended, no duplicates, nothing that would cause the database to make extra calculations and used a simple update query with no filters to append data.Still I get the same result, table02 takes a very long time to finish while table01 finishes in no time.

View 2 Replies View Related

Tables :: Data In Linked Back End Tables Not Saved From Front End

Jun 19, 2015

I have split database (B/E is in the SharePoint library, F/E has users on a local PCs). Sometimes, when I update/add data (does not matter if it is via form or directly in the table) it looks OK, but when I re-open the database, the data are gone.

Problem is that I cannot catch the moment when data were not saved (sometimes data are saved, sometimes not). I can point out this: if I re-enter the missing data, primary key continues subsequently, it looks like the data have never been entered. I tried to use script

Code:
If Me.Dirty Then Me.Dirty = False

on "On Close" form event, does not work.

B/E is linked by VBA code and it looks OK (no error, Link Manager shows correct path). I suspect interrupted connetion to the SharePoint but I don't know how to check it. I implemented VBA script co keep open connection to the SP but the issue persists.

View 9 Replies View Related

Tables :: Linking Tables With No Common Data And Different Numbers Of Observations?

Apr 15, 2014

I am doing a project where we are collecting home owner data and information on all the dogs in the household. The data for homeowner has an autonumber primary key because no field is unique or has been consistently collected. I am struggling to link the data for the dogs to the owners because an autonumber primary key will not work since not all homes have dogs. I need to have this set up so that people who are not tech savvy can look up each homeowner (or dog) and get the dog (or home owner) information. And to make things even more fun we need up to 15 potential dogs per household each of whom will have 18 different pieces of data collected.

It looks a little like this (and you can see my not matching ID issue):

Homeowner table
ID First Name Last Name...........Total Dogs
1 Max Maximus 5
2 Min Minimus 0
3 Mus Musculus 1

Dogs 1-5 table
ID Date Dog 1 Name Dog 2 Name .......Dog 5 Name
1 (Max's) 4/11/14 Bobby Billy Betty
2 (Mus's) 4/11/14 Jojo

Min will have no dog records at all, just home information.how to link the dog's to the homeowners .

View 3 Replies View Related

Tables :: Keep Primary Key Consistent As Data Saved In Other Tables Will Use Field

May 1, 2014

i have 4 supplier tables with identical field names but different databases in sql.I want to have them all in one table and only want the information for reference i do not want to edit any of the data.I need to create a new primary key number for the complete table. The data will need to be refreshed as the data comes from MMS Sage looking at company PLsuppliers.

I have tried a linked SQL union view- but this has no primary key.I need to keep the Primary key consistent as the data saved in other tables will use this field.The overall goal is creating a Purchase order system and this list will be my complete supplier list.

View 2 Replies View Related

Moving Data From 2 Related Tables To 2 New Tables As An Archive

Apr 3, 2007

I have a form displaying the 11 fields of the parent/primary table using a selection from a combo box. I am using queries and vba code modules respective to form, combo box and command buttons. I have initial code that uses the two fields from the combo box selection to append same to a new parent/primary archive table. I now want to add to the append SQL the remaining fields to the parent/primary archive table. When I add the second sql string for the remaining fields to the same procedure and execute I keep getting 'null in primary key'. If I copy the primary record and paste same into the archive table it works.

Private Sub Command26_Click()
On Error GoTo Err_Archive_Primary_Click

Dim strSQL As String
Dim strSQL2 As String

strSQL = "INSERT INTO ARC_289325045 ([Survey Point ID], [Survey Area Detail], [Date On Site]) " & _
"VALUES ('" & Me.cboAreaDetailDate.Column(0) & "','" & Me.cboAreaDetailDate.Column(1) & "'," & _
"#" & Me.cboAreaDetailDate.Column(2) & "#)"

CurrentDb.Execute strSQL, dbFailOnError

'strSQL2 = "INSERT INTO ARC_289325045 (RecordID, UnitID, UserName, [TimeStamp], [Survey Point - Area], Measurement, NewArea, [EXIT Form] ) " & _
'"SELECT FORM_ID_289325045.RecordID, FORM_ID_289325045.UnitID, FORM_ID_289325045.UserName, FORM_ID_289325045.TimeStamp, FORM_ID_289325045.[Survey Point - Area], FORM_ID_289325045.Measurement, FORM_ID_289325045.NewArea, FORM_ID_289325045.[EXIT Form] " & _
'"FROM FORM_ID_289325045"

'CurrentDb.Execute strSQL2, dbFailOnError

Exit_Archive_Primary_Click:
Exit Sub

Err_Archive_Primary_Click:
MsgBox Err.Description
Resume Exit_Archive_Primary_Click

End Sub

The next step is to do the same for the child table and append related records to the child archive table.

View 4 Replies View Related

Moving Data From 2 Related Tables To 2 New Tables As An Archive

Apr 3, 2007

View 1 Replies View Related

Tables :: Storing Query Data In Temp Tables

Dec 15, 2014

I'm looking into storing query data in temp tables for my reports run better. From what I'm reading, it seems best to have the temp tables in a separate db, and to break the links to avoid bloating of the FE database. I'm unsure how to do this with VBA, especially since my temp database will be password protected. When do I break the link - when I close the FE database?

View 14 Replies View Related

Tables :: Auto Deletion And Addition Of Data To Several Tables

Nov 28, 2014

In my Membership Database (Access 2010), I have a Table entitled [Foreigners] in which are stored the names of prospective members of the organisation and the name and ID of the Interest Group they are proposing to join.
When a formal Application to join is received their personal details are recorded using an Entry Form related to the main [Mail List] Table.

The current procedure then is to manually delete the entry from the [Foreigner] Table and finally to record their Interest Group data using another Entry Form entitled [GroupMembers]. This relies on an operator remembering to do the necessary.

It occurs to me that this process could be automated. I wondered whether it could be entirely automated or would be best effected with a button on the main Entry Form, such that on completion of data entry of personal details the button would be activated to do the necessary deletion and addition.

The [Foreigner] Table is an entirely stand-alone table, having no relationship with the other concerned tables. The only common factor will be the Member Name, which in the main table is a concatenation of First Name & Surname..

View 8 Replies View Related

One Form Entry For Two Tables

Sep 22, 2004

I want to achieve Access data updates to two tables through the use of a single form. Fields are identical in each table. I set things up this way in order to view the whole report or a single page of the report, and to this effect I am using the two tables. Can anyone help?

View 3 Replies View Related