Minimum Value Across Range of Fields(?)

[Butterfly_Collector]

Mods - feel free to change the title of the thread to suit.

Hi,

I'm not fully conversant with access and I've only been using it for a week! The spreadsheet I have been using has become far too big with over 52 columns and 2500 rows of data - hence my question. (I've attached a screenshot to enable anybody who can assist to follow my question).

I've created a table based the excel sheet and from that I have ran another query sheet. I've also devised a form for the new access data base I have created to enable future users to input data without problem - but I also want the form to do some automatic calculations based on the query sheet I have created.

In the query sheet I have created a list of 8 document sources (Claim_1, Claim_2 etc) which is across 8 fields (or headings) across the query. The next field is headed 'Claim No.' and the function I want to perform in this column, based on the 8 document sources is find out when a particular ID was issued first. So ID 13 was issued within Claim 4 and Claim 7 but I require the first Claim in which the ID was issued so this column should read "4".

Can I do such a function based on yes/no format or do I need to change the format of the fields in Claims 1 -Claims 8?

I've tried DMin and DMax but to no avail. The original document was based on a min function in excel but I can't get this to work!!

I had to paste the screenshot in image shack as the file was too large:

http://img826.imageshack.us/img826/8770/screenshotxf.png

Help or pointers appreciated!

B.C

 

[Galaxiom]

You need to restructure your data. Although a table looks like a spreadsheet they have virtually nothing in common.

Search the forum for "normalization" to learn about how to structure data in a database.

 

[gemma-the-husky]

as galaxiom says, given a spreadsheet with 52 columns, it is likely that some of these columns form sub-groups amongst your data. So the idea is to break this single table into a number of structured tables, so that there are no repeating groups of data. (its not exactly this, but this is what you are aiming for)

you may then end up with say 4 or 5 tables representing a NORMALISED data structure, where each table relates to data in another table, in a particular way.

its an art as much as a science, and needs practice and experience.

so although an access table resembles a spreadsheet, in practice they are quite different. eg - one thing that is simple in excel, but quite difficult in access, is to calculate data that depends on a previous row, since access does not have a basic concept of an order.

now you should find designing entry forms, and extracting data is far easier.

Having said all this, access isnt quite as accessible, if you will, as excel. You cant just "hack" a database in the same way as you can with excel. In access its hard to achieve much without using VBA code - whereas in excel I guess its pretty rare to need to do a lot of code/macros.

 

[Butterfly_Collector]

as galaxiom says, given a spreadsheet with 52 columns, it is likely that some of these columns form sub-groups amongst your data. So the idea is to break this single table into a number of structured tables, so that there are no repeating groups of data. (its not exactly this, but this is what you are aiming for)

you may then end up with say 4 or 5 tables representing a NORMALISED data structure, where each table relates to data in another table, in a particular way.

its an art as much as a science, and needs practice and experience.

so although an access table resembles a spreadsheet, in practice they are quite different. eg - one thing that is simple in excel, but quite difficult in access, is to calculate data that depends on a previous row, since access does not have a basic concept of an order.

now you should find designing entry forms, and extracting data is far easier.

Having said all this, access isnt quite as accessible, if you will, as excel. You cant just "hack" a database in the same way as you can with excel. In access its hard to achieve much without using VBA code - whereas in excel I guess its pretty rare to need to do a lot of code/macros.

Thank you both for your replies Galax / Gemma.

I managed to find something via google for normalization:

http://www.devshed.com/c/a/MySQL/An-Introduction-to-Database-Normalization/

I couldn't quite grasp the jargon.

I'm a bit of a novice when it comes to access I have to admit. I am starting to worry as to whether I've bitten off more than I can chew?! I've taken this from work and been working on it all weekend! :-(

The source data from excel is all formatted as per access requires and is essentially a "Master" table and in here I've created a primary key based on the each Unique ID. From that I've ran a query wizard and pulled out the data I have based for my form. I've had no problems with the form and I've even been able to create a tabbed form and drag controls in based on the query wizard.

I'm a bit confused about the issue of duplicating data - on the Master table, there is no duplication of data (with the exception of the single last field on the original attachment which summarises the claim number in which a particular document was issued).

I've only got two calculations on the form, the one that I thought would be the easiest I left till last, which is this one!

Please excuse my ignorance, this is major crash course in Access and I've so far bought a couple of books on it but its a struggle!! :-(

 

[Butterfly_Collector]

As an afterthought if the master table is only to contain the raw data can I just not delete the last numerical columns on the right hand side and from using the form I have devised (based on the query sheet) run the formula on there? I have claims 1 to 8 on the form as a tick box.

 

[gemma-the-husky]

i'm sure the excel form is not normalised correctly as far as access goes

if that was the case you could probably get what you want just be setting autofilters on all the columns in the excel sheet, and then using the drop downs.

Access has a learning curve though. You cant just pick it up and go, in the way you can with excel. RAther than a gradual improvement, there are a series of levels to get over, and a whole lot of "lightbulb-flashing" , "once seen, not forgotten moments" to pick up.

two things are different about access

1) its different enough that MS dont put in standard Office Professional - its a conscious decision, and purchase

2) most organisations actually use access by changing it, so it is not a user tool, but a programmed experience, such as your accounts package. Its that hard to use properly.

 

[Butterfly_Collector]

Right ok,

Just so I know I am going along the right lines here. (I am currently in work and access to internet is somewhat restricted)....

The raw data I had from excel I have now placed on 6 separate tables in access according to the broad area they relate to. (Any calculated columns I previously had in excel I have removed).

Upon pasting the table data over the primary key I have used is the default ID created by Access. This is because despite there being a number of fields each particular ID or event is unique in its own way depending on these variables i.e. there is no duplication of data other than the primary key, I've only entered the data in once.

I've then created a query in which I have pulled together the 6 tables containing the raw data and created a relationship from the query to each of the six primary keys (based on ID#) in the six tables. I then plan to use the query sheet as a basis for my form. Within the query I plan to add a couple more columns which provide me with my calculations based on the raw data that I have pulled together.

Is this a methodical approach to take or am I just creating problems again?

 

[gemma-the-husky]

thats the way to do it.

the point is however you dont necessarily need six different tables to describe the same items. If they are basically the same type of thing, then you only need 1 table.

You havent said exactly what is in your spreadsheet though, so its hard to know for sure.

BIT - say this is a plant register - then you DONT have a table for Motors, another table for Equipment, and another for Fixtures. You have a single table for all the plant. But one field in the table would be a planttype field, where a value (best to be NUMERIC) corresponds to the type of equipment so eg, 1,2,3. Generally you have another lookup table to translate the numeric to a TEXT description. so 1 = MOTORS, 2=EQUIPMENT, 3=FIXTURES

Then in use, in some cases, you may want to process everything. IIn others you may want to filter just a particular class of asset. So your forms provide an easy interface to the queries and code running behind the application.


Again, unlike Excel, because Access tends to be used for managing large datasets - in practice it is very rare to see ALL the data at one time. Everything tends to be processing a part of the data only. That's why it works with enormous data sets, that would not physically fit on a spreadsheet.

--------------
eg, if you were storing sales orders you would have tables for

Customers
Customer_Orders
Customer_OrderLines
Product_Codes

so that one customer can have several orders.
each order can have several lines
each line corresponds to a product code

you then extend this. you may want to include a price history, or designated suppliers for each table. your customers may be allocated to sales reps, or split into geographical areas. So your data structure and table layout depends on what oyu are trying ot model.

 

[Butterfly_Collector]

Hi Gemma,

I've managed now to condense all the information into 11 columns. I originally had 8 Claim Documents which contained information concerning the issue of drawings. Within each claim there are a number of sub-events so one drawing (e.g. #366) may be applicable to more than one sub-event (events x, y and z).

So, basically I've revisited and stacked every single drawing that was referred to in the claims on top of each other so now I have the following columns on different tables:
Table 1
Unique ID
Claim (1, 2, 3, 4)
Date of claim (Each of the 8 Claims has it's own date)
Sub Event Reference (I have about 40 of these)
Sub Event Description (Ditto above)
Table 2
Unique ID
Drawing Reference
Description of Drawing
Date of Receipt
Package / Supplier
Table 3
Unique ID
Date of Acknowledgement Letter
Letter Reference

I've set up a primary key between the tables based on a unique ID generated by Access. In table 1 I want to be able to ascertain the earliest claim in which a drawing was referred. If possible I would also like to be able, if asked, to be able to track those claims a particular drawing was referred.

Table 2 does what it says on the tin and in Table 3 I play to run a query which basically does a sum to calculate the days after a drawing was issued to drawing receipt and then do a sum that calculates the difference in days between receipt and receipt of the claim.

The plan is to create a form whereby a user can use a tick box to check off a particular drawing in a particular claim and then a field within that form which shows the earliest date of the claim and also a simple tick list which shows the other claims the drawing is referred.

I hope I've explained it a bit better and I hope I am starting to get it the jist of it???! Cheers

 

[Galaxiom]

The Claims and SubEvents should be in their own tables and related to Table1 by ID.

One record per Claim with Table1ID, ClaimDate, OtherStuffAboutClaim

Multiple fields hold the same type of information is always an indicator of the need to restructure. There is essentially no difference between the number claims.

How would you deal with Claim9, Claim10 etc? A separate table allows an unlimited number of claims. It also allows you to compare and order records such as claims by date or other fields that may be associated with the them. (Which I think is where you started this question.)

 

[Butterfly_Collector]

Hi,

I’ve had to go back to the drawing board. I’m not sure whether I should be creating 8 tables based on the number of claims there are or create 3 separate tables totalling 4000 rows which contain:
Table (Claim)
ID (Primary Key)
Excel ID (Based on original spreadsheet and purely as a means of checking final data is correct)
Claim #
Claim Received
Event Reference
Event Title
Table (Sub-Event)
ID (Primary Key)
Excel ID (Based on original spreadsheet and purely as a means of checking final data is correct)
Drawing Type
Drawing Reference
Description
Date
Package
Notice
ID (Primary Key)
Excel ID (Based on original spreadsheet and purely as a means of checking final data is correct)
Notice Reference
Notice Received

Based on the above, I plan to run a query that will determine ultimately determine whether the engineering company has complied with what is required under its contract in each and every instance (6,000 of them) i.e. to issue a notice acknowledging receipt of a drawing within 28 days and then send its claim detailing costs within 60 days (however that is a job for a query further down the line).

The purpose of the primary key is due to a combination of Drawing Reference, Package, Notice, and event reference and claim number making each entry unique in its own special way.

Across these 6,000 entries there are some 60 sub-events and I want to find:
1. Earliest date a claim was issued against a drawing within a sub-event;
2. Earliest date a notice was issued against a drawing within a sub-event (again, multiple notice may have been issued inside and outside the time limit but I want to be able to calculate this by sub-event and/or package)

In doing this I want to produce a form as a one stop shop for every unique event as a basis for checking the entries. The checks are to be based on going through all the letters and all the claims, this is so as to enable to check dates are correct and the drawing references in the claims are correct.
It is important however in respect of item 1 that I’d like the form to contain a snapshot (in the form of a ticklist) of those claims a particular unique ID was referred to – linked to calculate the difference in days, so if an original data entry is incorrect it can be remedied on the form.

That is where I want to get to but getting there is the problem!

Setting up the database is obviously the key and at the moment I am struggling massively. I think I have a many to many relationship, do I need to set up a junction table with only 3 tables originally?

Form design and generating reports I can do no problem but I feel like I have a mountain to climb!:-(

I had intended to create 8 tables just in case any more claims do arrive in the future.