Table Fields (Name / Number) Dependent On Another Tables Data.

May 18, 2006

I have an ... interesting issue. I am wondering if others have dealt with this. I have a solution, but am not the happiest with it.

I basically have 2 databases here. (Or I will when I'm done making them) and one is dependent upon the other. In fact, the 2nd's tables are dependent upon the fields in the first database's table.

This database is for quality control checking purposes.

First off,

Database 1:
Basically a list of standardized audits and what needs to be checked for those audits. What needs to be checked can change as things progress, but stays pretty standard.

Database 2:
This will hold the checks that the QC branch will do, based on what type of Quality Check they are doing, they have an audit standard. This audit standard exists in database 1.

What basically ends up happening is that the data in Database1 become Table fields in database2.

Example:

Database1:
Radio Check Audit Standard.
Needs to check following:
Process Specs
EDMS Drawings
Manuals
SoP / Policy
Used Radio callback
Used radio during emergency
Etc.

Another Random Check Audit Standard
Needs to check the following
Process Specs
EDMS Drawings
Manuals
SoP / Policy
Did something specific for this Audit
Didn't do something else, also specific for this Audit.
Etc.

Continue on with other Audits.


Database2: Will keep track of all the quality checks that have been preformed.

Today we will check Radio Protocol
Did they:
Process Specs
EDMS Drawings
Manuals
SoP / Policy
Used Radio callback
Used radio during emergency
Etc.

Yes, No as necessary.

2 Days later, we need to do another QC on the Radio Protocol
Need to check if they did:
Process Specs
EDMS Drawings
Manuals
SoP / Policy
Used Radio callback
Used radio during emergency
Etc.


Etc.


But what will be checked for in Database2 depends on what the standard is for the "Radio Check Protocol," or etc.

Right now, i see 2 options:

Option1
Make the 2 databases, and when a user "Picks" the Radio Check protocol, a linked table is filled with the Name / object that needs to be checked with a field in the same row to hold the data if the object was done / not done etc.
The fields in Database1 are automatically put into this specific instance of Quality Check, etc.

Only problem with this: Requires coding to auto update. Easy to do, but if errors occur, things will screw up.

Option2:
When the user picks the Radio Check protocol, simply allow a subform with a refreshed drop down box. Allow user to pick items they need (Limit it to only this Audit list, etc) to set to true. Assume false for other items not specifically chosen by user.

Problem with this: If an Audit standard changes to remove an item, or add item. Any quality checks done will also change for the assumed false items. If another Item added in, then all old QCs will now also have that item added in / reported assumed false. And if it is deleted, all old QCs will loose the item that was reported false.


I'm seen the first option as the only viable one.

Am I missing this, is there some other way to do this that would be better / wiser? Etc?

Kelemit

View Replies


ADVERTISEMENT

Tables :: Dependent Fields - If One Is Other Will Be No

Dec 6, 2014

I have a table with 3 yes/no fields. I need them to be dependent on each other meaning if one is yes the others can not be yes. How can I set this up?

View 5 Replies View Related

Dependent Combo Boxes With Fields From Different Tables

May 16, 2012

I thought that my previous request on how to create a dependent combobox based on selections from two previous comboboxes, but the example provided only worked if the fields were all in the same table. Here is an explanation of my problem...I have 3 tables:

-FoodType
-fruit
-vegetable

Color
green
red
yellow

FoodName
apple
banana
cabbage
green beans
kiwi
lettuce
lime
raspberries
beets
spinach
star fruit
strawberries
squash
tomato

I have 3 comboboxes on a form: cboFoodType, cboColor, cboFoodName.I would like to filter the value lists in cboFoodName based on the selections from cboFoodType and cboColor.For example, if I select "fruit" from cboFoodType and "red" from cboColor, I want the value list available in cboFood Name to consist of "apple;raspberries;strawberries".Likewise, if I select "vegetable" from cboFoodType and "green" from cboColor, I want the value list available in cboFoodName to consist of "cabbage;green beans;lettuce;spinach".

View 2 Replies View Related

How To Link Time-dependent Data Tables ?

Jan 13, 2007

While much of the discussions here are out of my league, this may just be the place to finally get some help.

I have a hobby weather station and I am also into gardening. Over the years I have accumulated a fair amount of data/ observations which are currently in assorted Excel spreadsheets & are becoming more an more difficult to maintain.

So, I decided to move the various Excel tables into MS Access. While I was able to draft a entity-relationship diagram for my horticultural data, I am at a total loss when it comes to weather/ climate data which are primarily time-dependent. With other words, I cannot find any references that explain how to design pairs of foreign and primary key for linking datasheets that contain time varying data such as various weather and climate data.

I have ordered dozens of books through the local library. Every book presents exactly the same example: customers and orders/sales. This example is fine when you have a myriad of interdependencies in your data (and proved useful for my horticultural data). But, my weather data are more or less independent of one another; the only thing they have in common is the time/date when I make the observations.

Do I have to create dozens of surrogate keys and add a ton of redundant data ?? I hope not !

View 14 Replies View Related

Tables :: Data Conversion - Short Text Fields To ID Number

Jun 15, 2014

I have an old Table with Movies, Actress, Actor, & Director Fields & I have converted it to .accdb. I want to turn those short text fields to an ID number. I have built Tables for those fields(indexed no dups) with an ID field. I have over 5000 records in the original table and dont want to input those numbers by hand. I am using Access 2013.

View 5 Replies View Related

Tables :: How To Have 2 Auto Number Fields In One Table

Jun 4, 2013

I am having a table for invoicing and vouchers, and I need to have two fields that will have auto incremental numbers. How I can have two fields in the same table with autonumbers and how can I get one of them to start from a different value.

Eg. Record 1 - [inv No] 1 [Vno] 0005
Record 2 - [Inv No] 2 [Vno] 0006

View 14 Replies View Related

Forms :: DLookup - Using Two Fields (number And Drop Down Text) To Query Table For Data

May 29, 2015

DLookup function. (this is for a stamp collection database).

On my form ("InventoryInput") I have a text box called "Catalog" for a numeric entry and a text combo box for selecting a "Country" in drop down list.

I want to query a table called "CatNameList" to get the "StampName" of the item (based on the entries of Catalog and Country) and populate that name in the text box. The fields in this table are called "StampName", "CatNumb" & "CName" respectively.

I have successfully placed the following expression in the control source of that textbox and able to populate the StampName I need based solely on the catalog number alone.

That express is :
=DLookUp("StampName", "CatNameList", "CatNumb = Form![Catalog]")

So it will populate the "StampName" data to match the "Catalog" number entry just fine.

However, I need to add a second layer to incorporate the Country.

Example : There is a catalog "1" for "USA", and a catalog "1" for "Canada" but both have different "StampName".

I have been attempting to get that second piece added with no success. Here is the expression I have been trying to get to work :

=DLookUp("StampName", "CatNameList", "[CatNumb] = " & [Catalog] & " And CName = '" & [Country] & "'")

Right now, the text box is just blank with the above expression. I thought it may be because there was no match found, but I have triple checked to ensure I have the spelling correct on the country name in both places.

Basically, I just need the dlookup to take the "catalog" and "country" off the form and match it to the "CatNameList" table fields of "CatNumb" and "CName" to give me "StampName" field back on the form.

View 4 Replies View Related

DLookup - Using Two Form Fields (Number And Drop Down Text) To Query Table For Data

May 29, 2015

Having problems getting dlookup to work in the control source field of a text box.

My form has fields : Catalog # (numeric value) and Country (drop down text selection).

I would like to query a table CatNameList for a name (text) if the catalog # and country find a match on the table.
My field names on the CatNameList table are : Name, Number (to validate against the Catalog # entered on the form) and CName (to validate against the Country drop down on the form).

I am successfully able to populate the name from the CatNameList table on my form using lookup of the catalog # using this :

=DLookUp("Name","CatNameList","Number = Form![Catalog #]")

However, I will eventually have several catalog numbers that will be identical in the table CatNameList, thus why the country is important as the second criteria to be added into the dlookup.

I have tried for a few hours unsuccessfully to add the second portion to my dlookup.

This is what I have currently (not working) that I have been playing with, I'm sure I'm missing a quote mark, & or something simple.

=DLookUp("Name", "CatNameList", "Number = Form![Catalog #] And CName = ‘”& Form![Country] & ”’”)

View 14 Replies View Related

Tables :: Accessing Data From Other Fields In Another Table

Sep 19, 2014

I have a database with a number of tables 2 of which are:

# Personal Members Table (with all their details and addresses and contact details)
# Organisation Table (including who are members of this Org)

My conundrum is this; In the Org table I have just created a 'Treasurer' Field and want to choose a name from the Members Table so that I do not have to put the data in twice (which is what has happened in the past.)

I have linked the tables and but within the Members table the Field names for the Personal Name are as follows <FirstName> <LastName> and there is another for the <Org> they are connected with...

In the Members table it would also be good to have the ability to hover the mouse over the <org> field (or click) on it and then underneath a box appears with everyone who is a member of that org. I have seen this working on another database but cannot seem to replicate it here for some reason?

View 6 Replies View Related

Tables :: Merging Table Data Fields If Names Are Identical

Sep 24, 2012

Have 2 tables:

...TableA: 1 indexed field "Name" and many data fields in ~ 1000 records
...TableB: 1 indexed field "Name" and 2 data fields (DataB1 and DataB2) in about
~ 50 records

Tables currently have NO relationship set.

Want to add TableB data fields to TableA if Name are identical.

DataB1 and DataB2 fields are Not currently in TableA but could be added very easily.

Update (or whatever) will be done weekly where all previous data for DataB1 and DataB2 will be cleared from TableA before job is run.

View 1 Replies View Related

Tables :: Import Excel Into Table / Overwrite Data Of Fields Imported

Mar 17, 2015

I have a table, which contains many fields, around 90. Out of these 90 Fields, around 40 will be imported from an Excel sheet, which has same headers as the 40 field names in my table, in which they have to get copied.

So when I import data, it copies them as new records, instead what I am looking at is, the records in Excel and in Access table has a Unique Key, so when I import data, matching with this unique key, the fields should be overwritten into those records from Excel, instead of adding new records.

E.g:

Say I have 2 records in my access:

ID(auto generated) Fab_name Issue_ID Location Remarks Data_1 Data_2 Data_3
1 Fab1 193 NY Bug OC DC MC
2 Fab2 641 DRS Error AC KC FC

Now when I am importing data from Excel with the following records:

Fab_name Issue_ID Location Remarks
Fab1 193 NY - GRM Solved
Fab2 641 DRS - SGP Forwarded

So when I import the above data into my Access from Excel, based on Issue ID, it should update the values of different fields that are imported from Excel, leaving other fields (data_1, Data_2, Data_3) as it is.

And I need this to be written as a macro, so that user just has to choose the file, which he has to import.

View 6 Replies View Related

Tables :: Extract Data From Memo Field And Put Into Separate Fields Of New Table

Jun 15, 2014

I have a notes field in the customer table that is a memo field. An example of one customer's notes field data :

<div>20.3.14 Ordered 2 cartons</div>
<div>4.3.14 Ordered 2 cartons</div>
<div>18.2.14 ordered 1 carton</div>
<div>30.1.14 ordered 3 cartons SCENTED wipes</div>

[Code] ....

I want to extract the date to append to a date field in a "Calls" table and the comment into a text field in the "Calls" table. Is there a way I can do this via query or code?

View 2 Replies View Related

Automatically Copy Data From One Field In A Table To Other Related Fields In Other Tables

Aug 7, 2014

I'm trying to create a system where if I enter data into one field it will automatically appear in the corresponding field in another table. For instance if I enter the values 10,12,15 into a field called QID in table A I would like this to appear automatically in a field called QID in table B. Both tables are related and I have enforced referential integrity and 'cascade' options but this still doesn't seem to allow one table to automatically update another. My ultimate aim would be to have a form that you filled in data for the field QID once that then propagated to both Table A and Table B.

View 2 Replies View Related

Tables :: Consecutively Number Table Rows With User Determined Start Number

Jul 13, 2014

I have an Access2007/SQL Server 2012 system with 20 users for an insurance company. The company does most of its business via a network of vehicle dealers around the country. If someone comes in to buy a motorcycle, boat, or recreational vehicle at a dealership they need insurance to take it home, and our dealers send the quotes to us.

The dealers, in turn, receive payment from us each month for their efforts. Some are paid a % commission on the premium, some are paid for each quote they send regardless of whether the policy actually sells or not, and some are paid a set amount per sold policy. (Yes, that is relevant information!)

We already have reports that tally the amounts due each dealer based on their payment scheme, but last month our bookkeeper had to write about 650 checks manually because the check writing is not automated. She'd look at the report, and then enter name, address, and amount (in digits and words) into Quick Books and print the checks from there, a horribly tedious process. I've been asked to print the checks from Access. Basically one click would print all 650 checks.

I've opted to use a Make Table query to move the commissioned dealers amounts to a single location, and then to run two append queries to add the records from those paid per quote and those paid per policy. At the end of the day, one table contains all the information necessary to print the checks...except one.

The check number.

I need a way to sequentially number each record in the new table with a user generated starting point, the first check number.

By the way, the check blanks are on standard letter sized paper, three to a page, with tear-off perforations to separate them, in case that information has any relevance.

I think the best way to accomplish this is from the report itself. I've created a blank field on each record for the check number, and what seems most logical is that the sequential number is generated on print and written back to the table, rather than just generating all the numbers at once. That way, should print ever be interrupted, it will be easy to take up where we left off.

View 14 Replies View Related

Validating A Field (dependent Fields)

May 28, 2005

I am trying to put in a validation rule so that data in one field cannot be less than that in another.
e.g. i want to make a validation rule so that a phone call end time cannot be before the phone call start time.
Can anyone help???

View 5 Replies View Related

Dependent Fields In Access Forms

Sep 14, 2004

Hello All -

I think that my problem is best explained by an example.

In the first field the user chooses between two options for the reporter.
Field 1 - Reporter: Client OR Employee

If the user selects "Client" they have to enter an address, if they select "Employee" they don't have to enter an address.

So, what I hope is to create a form where if they select the "Employee" the address fields (address, city, state, zip) "gray out" and not accept any data entry.

Any help would be SO appreciated!

Thanks -
Amy

View 1 Replies View Related

General :: How To Create Fields Dependent On Each Other

Jun 19, 2012

I am very new to Access and I am not familiar with SQL. How I can create fields that are similar to each other?

Let's say I have 3 fields in my table: Name, State, Area Code.

I want it so that each time I select "Name" the appropriate State and Area Code populate. Anyway I can do this?

View 3 Replies View Related

Tables :: Why Do All New Number Fields Have A Default Of 0

Jan 7, 2015

I am using Access 2013 and in table design view. Whenever I add a new 'Number' or 'Currency' field the 'Default Value' property is (by default) always 0.This is fine except when i want to refer to a foreign key or amount which could be unknown. In either of those cases the resulting field will either be invalid (because no foreign key has ID = 0) or inaccurate (because 0 is not the same as Unknown).

I would prefer if the 'Default Value' property for new fields was always nothing, then I can add my own if and when required. I want to change the default setting on a property for new fields, not the value of the field itself.

I'm guessing this isn't actually possible, but at least this post has served to vent my frustrations! What bugs me even more is that i'm sure it wasn't like this in Access '10, is this another "upgrade".

View 5 Replies View Related

Can Fields In Access Tables Be Referenced By Number?

Jun 4, 2012

Do fields in Access Tables have a 'Number' property that you can use to reference them?

For example, if you have the following fields:

EmpID
FName
LName
Department
Hire_Date

Is there a way to query an employee's Hire_Date by telling Access to return the value that is in the 5th field [Hire_Date]?

[URL] ....

View 8 Replies View Related

Tables :: Set Up Validation Of Fields Unit And Size Based On Another Fields Data?

Nov 18, 2013

Basically in my order details table i have the following fields

Product
Unit
Size

At the moment i have the Product field with a dropdown that gives me all the products from my ProductT. But once i choose the correct product in the unit field it gives me all the possibilities of every product not just the units associated with that product. ie

ProductT
Grasshopper Box1000 Adult
Grasshopper Box1000 Subadult
Worm 10pz Big

When I select the grasshopper product and move on to the unit field i also get "10pz" option but this is not a product available.

How do i set up validation of the fields Unit and size based on another fields data?

View 4 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

Forms :: Label Dependent Upon Graph Having Data

Mar 21, 2014

I created a number of graphs on forms that graph GamesSold vs RDate. If a game has no sales, there won't be any data. Is there a way I can add a label that will automatically display "Game is not yet released" and make it dependant upon the graph having data, and invisible if it does?

View 2 Replies View Related

Is There A Limit To The Number Of Fields To A Table??

Sep 1, 2006

My boss has a table with tons of fields on it, he designed it this way because it was easier to add data to one place, but now the table keeps crashing, does ms 2k have a limit to the number of fields in a table??
Thanks in advance.

View 3 Replies View Related

Maximum Number Of Fields In Table

Dec 27, 2006

I have run into a problem with an ODBC table connection. The table I am connected to exceeds the number of fields allowed in an Access table. I am using Access 2003. When I looked on Microsoft help site I think the max number of fields in a recordset is 242 so I am unable to see all of the fields in the original table I am linking to. Does anyone know if this limitation has changed in the newest version of Access? I checked the Filemaker www site and that program does not have the same limitations. Thanks in advance for any help!

View 4 Replies View Related

Maximum Number Of Fields In A Table

Feb 8, 2008

Hello there.

Is it better to have many fields in one table or to split the data between a number of tables and link them?

Thanks.

# gingette

View 2 Replies View Related

Queries :: Delete Records In One Table Dependent On Another Table

Apr 17, 2015

I have a table which lists amendments (amendments history file) which need to take effect of another table (M0070), I can select the records fine and so the sub query looks to be okay but I'm missing something (probably obvious) in the deletion query where by it does only delete the records selected but everything (I know that the asterisk is wrong in the code below but I'm not sure what to replace it with).

At this point I'm trying to delete all records for a specific employee on a specific contract (they can be 1 or more employee records for that individual on a contract - and they could exist on multiple contracts).

DELETE *
FROM M0070
WHERE EXISTS
(SELECT M0070.[Contract No], M0070.[Contract Name], M0070.[Employee No], M0070.Forename, M0070.Surname, M0070.Branch, M0070.[Long Desc], M0070.[Emp Post Start Date], M0070.[Days per week],

[Code] .....

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved