Excel - Access help please

[dwbolland]

I am moving information from excel to access as i am sure it will help (in the long run)

However I am very comfortable with excel - macros etc.

I know I must be able to sort access out but I can't get started

Month NI Number Stay ID Stage ID Event Code Event Date
Aug-04 TN294233B 1 2 EZ0002 11/08/2004
May-04 II389177B 1 2 EZ0002 27/05/2004
Sep-04 JZ555402C 1 2 EZ0002 10/09/2004

This is the information I have in excel for around 100,000 entries.

They move through various event codes on various days.

I really need to analyse the movements of each member (NI Number) through each event code (and when).

Can someone help me to set this up please. All my info is in excel and I am happy at importing it - but how do i lay out the database????

by the way this site is tremendous and helps save time when thinking about complex excel issues you know you can fix but haven't time! 5 STARS to the site- - but can anyone help?

 

[Andrew Fergus]

Hello and welcome.

You have correctly identified the setup as being critical to the success of your database. Unfortunately I can't provide much help or advice on the best setup for your database given I don't understand your example.

However, there are some good support pages from MS here, here and here that might help you get started.

If you get stuck then post a reply here and help shouldn't be too far away.

HTH, Andrew.

 

[dwbolland]

brilliant - will check out tomorrow in work.

thanks again though

 

[dwbolland]

I have read these articles (thanks again) but am still unsure which way to go about it.

I have thousands of members who each go through 4 (usually) stages after various times spent on the stage.

I want to be able analyse the time spent on each stage?

I was initially thinking that four databases for each stage and date stage commenced all with reference to the member's ID.

Am I on the wrong track?

 

[Andrew Fergus]

Hi, I now understand the setup better. How about the following :

One database with the following three tables :

• first table = members (with member id plus other info)
  second table = stages (includes stages 1-4 plus any other stages with additional info on each stage)
  third table = member_stage table (includes member id, stage number, start date/time and end date/time)

One member can have many stages and one stage can have many members, so what you need is a many-to-many relationship between members and stages which is achieved through the intermediary member_stage table. Link the members to the member_stage table with a one to many link and link the stages to the member_stage table also with a one to many link.

HTH, Andrew.

 

[dwbolland]

how can i do so much on excel and yet be pityfully poor at this! is infuriating but I am very grateful of the pointers i feel so close now.

members - table
ID [text]
DOB [date]
Previous memberships [number]

Stages - table
STAGE [number]
DAYS [number]

member stage - table
arrrggghhhh not sure here
how can I get the fact that i want time spent on each stage by each member?
know i should be able to - which is the reason for the fury growing in me

ray:

 

[Andrew Fergus]

Hi, following is a sample layout of my previous post.

dbsetup.xls
	A	B	C	D	E	F	G
1	Database setup
2
3
4		members		member_stages
5		member id	1-----------M	member id		stages
6		member name		stage id	M--------------1	stage id
7		dob		start date		stage description
8		previous memberships		end date		other stage info
9		other member info
10
11
12
13
14		master file of member details				master file of stages
15
16				link file that allows each member to have multiple stages
17				and each stage to have multiple members
18
Sheet1

HTH, Andrew.