Data Switch Guide Review

Microsoft Data Warehouse
Microsoft Access – Form that lets me search for previous records?

I wish to have a form that will allow me to easily search through previous records. I have a database set up that is used to request parts from our external warehouse, to bring them back to the main plant. The main form(frmRequestForm) is set up for data entry that saves to a table (tblRequests). I now want to make a form that I can use as a search for previous records. I envision a form that has fields such as date, person, department, part #, etc. So when you open this form you can fill in whatever infomation you already know and it will tell you which Transfer Request records match the information you are looking for. Also for something like the part # search if the part number is “123xyz” and someone were to just search for “123″ would the record containing the 123xyz part number show up in the results? Also since some searches will bring up multiple records that match, is there a way to display each of the matching records without physically opening the record right away?

Gettin into some advanced Access development with this one…

What you are trying to do is create a Query By Form. Microsoft has an article on it here:

http://support.microsoft.com/kb/304428/EN-US/

You will want to check out Google for some more help. Thing is, this is pretty complicated to get working correctly. Took me a LOT of effort to make it work, then, for some godforsaken reason, it stopped working at random.

Access 07 makes the technique work a little more predictably at least.

Best of luck…

Microsoft Data Warehouse

Relational Data Model is a data management model devised by Edgar F. Codd in the year 1970. It is considered as one of the most beautifully designed and widely used data models in recent times. Based on the predicate logic and set of theory of mathematics, relational data models help in managing the data efficiently.

A relational data model is implemented in a database where a relation is represented by a table, a tuple is represented by a row, an attribute is represented by a column of the table, attribute name is the name of the column such as ‘identifier’, ‘name’, ‘city’ etc., attribute value contains the value for column in the row, constraints are applied to the table and form a logical schema. Mostly, relational data modeling is used in OLTP systems, which are transaction, oriented, while dimensional data modeling is used in OLAP systems that are analytical based. Relational data modeling is closely related to data warehousing as in a data warehouse environment, the staging area is designed on OLTP concepts. Therefore, the data requires be normalizing, cleansing and profiling before it is loaded into a data warehouse.

Relational algebra operations such as Select, Intersection, Product, Union, Difference, Project, Join and Division, Merge can be performed on a relational data model. Below are the fundamental concepts in relational data models:

– Domain: A domain “D” is the original sets of atomic values used to model data. Atomic here refers to each value in the domain that is indivisible as far as the relational model is concerned.

– Relation (Relation state): A relation is a subset of the Cartesian product of a list of domains characterized by a name. Relation can be viewed as a “table”. In that table, each row represents a tuple of data values and each column represents an attribute.

– Attribute: A column of a relation designated by name and the name associated should be meaningful. Further, each attribute associates with a domain.

– Relation schema: Denoted by “R”, relation schema is a list of attributes. The degree of the relation is the number of attributes of its relation schema. The cardinality of the relation is the number of tuples in the relation.

Following are the terms used in relational data model:

– Candidate Key: Candidate key refers to any field or a combination of fields that identifies a record uniquely. The Candidate Key cannot contain NULL value and should always contain a unique value.

– Primary Key: Primary key is a candidate key that identifies a record uniquely.

– Foreign Key: A Foreign key is a primary key for other table, in which it uniquely identifies a record. Such a key defines relation between two (or more) tables. It can contain NULL value.

– Constraints: Constraints are logic rules used to ensure data consistency or avoid certain unacceptable operations on the data.

A relational data model provides basis for:

– Research on theory of data/relationship/constraint.

– Numerous database design methodologies.

– The standard database access language SQL.

– Several modern commercial database management systems.

This article was written by Brian May who has worked with companies that offer data warehousing consultants. He truly understands the value that a data warehousing architecture can offer.

How can I find duplicates in Microsoft Excel?

Hi,

You would need to use the VLOOKUP function.
create a separate column in Excel on one of the spreadsheets, then click on the top cell in that column.
In that cell, type =, then on the function list in the top left, choose VLOOKUP, this will bring up a dialogue box that will have 4 options in it.
For Lookup_value, highlight all the data on the first spreadsheet that you are wanting to check against.
For Table_array, highlight the same column, on the other spreadsheet
For Col_index_num, type in 2
For Range_lookup type FALSE
Click OK

Then drag that cell down the entire column and the ones that don’t appear twice should have a #VALUE! or #NAME! in the box, the ones that do will have a different text in, depending on what you had in column 2

EDIT: You must make sure the column you’re checking against is the first column on the left in both spreadsheets

Microsoft PASS Summit Data Warehousing