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
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.
I have a datasheet as a sub-form on a main form. Because the main form is accessed from 2 different forms in the system (let's call them A and B), I hide a column on the datasheet that is not relevant when the access is from Form B. This was working fine.
Until I added a new field to the datasheet/subform with some conditional formatting (background colour change dependent on field content). Now the hide column function causes an error which locks the database. I removed the conditional formatting and the hide column functions works fine. Please note the column being hidden and the column with the conditional formatting are not the same column.
Seems the combination of hide column AND conditional formatting in the same datasheet is the problem.
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?
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...
1. I have a database (see attached) with three tables all of them with the same fields. The first three are numbers (InCo_No, Proto_No, Year_No). Each of these fields (numbers) can be the same in the other table(eg. Year_No), but the combination of the three cannot be.
How can I prevent the entry of a duplicate combination of these three fields?
2. I want to have a form to fill the three tables separately, depending the values in the other fields.
I am working on a fairly ancient manufacturing database that identifies items using a combination of letters and numbers. The usual format is to have a letter (which suggests something about the item type) followed by a sequence of numbers.
I am trying to write a query that looks up all the records beginning with a prefix or arbitrary length, strips away the text, and finds the highest number.
Code:
SELECT Right(LocalID,Len(LocalID) - 1) As IDSuffix FROM tblItemIDCrossReference WHERE Left(LocalID,1) = 'T' AND IsNumeric(Right(LocalID,Len(LocalID) - 1)=True)
This query produces the error given in the title of this thread, whilst the following works:
Code:
SELECT Right(LocalID,Len(LocalID) - 1) As IDSuffix FROM tblItemIDCrossReference WHERE Left(LocalID,1) = 'T' AND IsNumeric(Right(LocalID,5)=True)
This related query also works and shows a load of -1s and 0s correctly
Code:
SELECT Right(LocalID,Len(LocalID) - 1) As IDSuffix, IsNumeric(Right(LocalID,Len(LocalID) - 1)=True) As Alias FROM tblItemIDCrossReference WHERE Left(LocalID,1) = 'T' AND
But once again shows the error message when I try to filter the field Alias to -1 or 0 only through the right-click menu.I have tried piping Len(LocalID)-1 through CLng, CInt, Int, CDbl and CSng; this changes the error to 'Invalid Use Of Null' I have also tried removing the '=True' from the IsNumeric() term.
I have an Access 2007 database with two tables (I will call them table 1 and table 2)
Both tables contain the same two fields. (I will call the FirstName and LastName)
Table 1 has an associated Form where the user enters the two names. When the value in either one of the two fields in Table 1 change I want the corresponding field in Table 2 to automatically update with the same value that were entered in Table 1. Basically I want Table 2 to automatically replicate the same data in Table 1. So if I type the text "John" into the FirstName field in Table 1 then the FirstName field in Table 2 will automatically update with the text "John"
I am new to access and am struggling with the automatic updating.
If the automatic part is too hard then I will be happy to attach the update action to a command button.
I have uploaded my database file with the two tables for reference. I want to get the fields (for all records) in table 2 to replicate table 1 so that when table 1 updates table 2 values changes to show the same text.
I have a database with numerous tables. I have set up relationships between all tables (they are all pretty one-to-many relationships). I then created forms in which to enter and view data. Data entry is fine; however, when I go into my tables, I realize that it is not storing the ID# for related tables in the main table.
For example - I have a table called "ContractTypes" with a field for ID#. That table is linked to the main table called "Contracts. In the form, I have the ContractType as a lookup field, I select the one I want for this entry, but it is not storing that ContractTypeID into the Contracts table. Also, when I go back into the form to enter a new record, the data on the subforms is from the previous record and not the record I'm currently working on. In other words, it does not appear to be linking the ID#s from the different tables.
Table 1 has National Insurance Number, first name, last name, phone no, address. Table 2: has National Insurance Number and email address.
Table 1 is the master table where it will link to table 2 containing the email addresses of the individuals.
However, there will be a lot of email addresses in table 2 which do not relate to any record in table 1 because I do not have the individual in my database yet.
BUT I want to keep their email address because in the future this person may enlist in my imaginary business and therefore - if they do I would have their email address!
1. Is this possible in Access?
In Excel, it would be the case of a simple vlookup from the email address field into another sheet containing the National Insurance Number and email address.
2. Would it be possible to establish a one-one relationship while enforcing referential integrity?
3. Is there a way I can establish a lookup which can be built into table 1 which can lookup the email address of a person in table 2, matching on National Insurance Numbers?
So in the future if new data is input into table 1 or 2 which results in a match of National Insurance ID numbers, the outcome would mean an email address is now paired to the corresponding individual it belongs to.
I have two tables that I'm struggling with how to relate to each other. One is tblHardware which contains all the items that we sell. There are no duplicates in this table. The only columns are a part #, description, long description, and cost.
Then I have another table with is tblPackages. These are the packages we sell which pull from tblHardware. This one just has an autonumber, description, and image (image I'll add later and not worried about now).
What I'm struggling with is how to tie these two together because one Package will contain multiple items from Hardware however there are multiple Packages that have the same Hardware items (17" monitor for example).
I created a form that allows users to enter data into the fields and populates the table.But can I have it do that and populate instead of one table, two tables?
Table one is for person ID Table two is for number of absences
I created a one-to-many relationship with the update/delete option selected. It didn't work when I wanted to add new data into the tables (via form). Then I tried a many-to-many relationship (with a third table) but it doesn't seem logical to do so, as I only need to add particular data into one table, but need a field from the 1st to differentiate records.
(table 1 = one record per person, table 2 = multiple records for 1 person)
We have an "item" table that contains information specific to an item (the item number is unique); and multiple "production line" tables. There is a relationship between the Items table and the Production Line tables (all 8 of them). Each item can be entered on 1 or more production line tables because one item may be produced on multiple lines and the settings vary based on the line they're produced. When entering an item in the Production table we MUST make a selection from the Item table.
Now, what I want to do is identify which item number(s) from the Items table does not have a specific field entry in ANY of the Production Line tables.
I work in an office with Office2000. One branch of our non-profit organisation had a volunteer design a database in Access to track the usage of rented storage bins.The database works great for its intended purpose, however the volunteer is not available and the front-end is so locked down that I can't get into the table structure to run some usage queries I need for my own job, which is Resource Development (donations).
I don't know what to say to give more info, because the computer is a stand-alone in a different department detached from the network, so I have to physically go to the other end of the building & work in it, I can't get screencaps etc easily, and most of the menus have been disabled so it doesn't even give me the info about the version of Access etc.
I am trying to get my expiration date field to highlight a certain color once the date comes closer.I have weapons qualification dates. The user will input the date he/she qualified and then a expiration date (6 months from that day) automatically pops up.
Now what I want to do is once the expiration dates gets closer is change the color. So when it is 90 days from expiration it would highlight yellow, and when its 30 days away it would highlight red. I believe this can all be done through conditional formatting, but for the life of me I cant figure it out!!
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
I have a subform based on a query. In that query, I have calculated values for price+tax and another price+discount+tax. Now I have a conditional formatting that if the gross total is not equal to amount due, the amount due box will turn red. However even if they are equal visually, it still turns red. Ex. The gross total is 336.00 and the amount due is 336.00 also, so it should NOT turn red, but it does. When I click on the amount due control, the value turns into 335.999999195337. How do I tell access not to make 336.00 into 335.999999195337.
Having recently upgraded to Access 2013 I've noticed when designing forms or reports the Shape Effect button is greyed out. Also, when I click Conditional Formatting no dialogue box pops up. This is happening on all old Databases and new ones created with 2013. (Windows 7 64 bit)...
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?
