Creating a Database from scratch, need assistance

[Jase81]

Over Christmas I've taken on the task of putting together a database for work; I don't have a deadline but it would be nice to have something to show in the new year.

The database I am intending to build is to track the location of wheelchairs in the hospital where I work. Its a fairly simple system. But I'm a fairly simple person when it comes to databases - but within our department I am probably the most computer literate person so I seem to be the ideal candidate.

The chairs have unique numbers which will serve as the primary key. Where I am having difficulty at the moment is when I get to the location of the chairs. There are four locations where the chairs can be - 'On Site', 'Off Site', 'Out of Service' or 'In Storage' and each location requires different information to be entered.

I will have a table of all the chair numbers which will include all relevant/required information regarding each chair. This is more so the database can be searched to see what is currently available.

Ideally I want to create a database that serves two purposes. Tracks current locations, and enables everyone to see whats available. For tracking purposes, I imagine that someone would enter the chair number and then chooses the location where it is. My problem is choosing the location, Ie; 'On Site', I have no idea how to get the 'On Site' table to load for the information to be entered?

I have based this on one table for 'chair information' and another for each of the locations.

Any help would be greatly appreciated because quite frankly, I need it! I'm slowly wrapping my mind around the task but at the same time, lack the knowledge with Access to do it on my own.

[1o0oBhP]

what are you using to create the database? if you store the chair number as the first key, then when someone asks for the location of the chair you can perform a modified binary search to get the chair entry, consequently the chair's location.
To see what is available you can search the location key for "On Site" and list the results sorted by chair number.

The easiest way to make the location key is to enumerate the locations ie:

0 = On Site
1 = Off Site
2 = Out Of Service
3 = In Storage

then search the corrseponding numerical equivalent value. Hope this helps :)

[autocrat]

OK... not to sure on this.......

You have table 1
...... CHAIRS..... Chair ID (Autonumber), Chair details (anything you like)

You have table 2
...... LOCATIONS.... Location ID (autonumber), Location details (anything you like)

You create a 3rd table
Cross check.... Location ID and Chair ID (both = number)

THEN you link table 1 and table 2 up with table 3 (make sense?)
Then you create a querry based on Table 1 & 2..... make the querry a create table querry or something.... ?

Now create forms for Table 1 and table 2..... must make it so that new records cannot be entered... only current records can be editted... this means that people can look up the chair number they are taking, and enter where it will be going!

now, when people run the querry, they can see the latest results ???

Of the top of my head... that should be right! No promises though!

[autocrat]

DAMN... thats not right.............

Sorry... less complicated!

TRY THIS... I just did, and it worked!

You have table 1 - Wheel Chair Locations.
You Have an ID field - Autonumber
You have a Chair Number Field - Number
You have a Location Field - Text

You have table 2 - Locations List
You hae an ID field - Autonumber
You have Locations Field - Txt

THEN you link table 1 and table 2.. linking the Location fields!

Now you enter your data into the tables... in Table 1 (chair location) you enter each chair number, but leave the location blank!)... in table 2 (location list) you enter the locations you want to list.

Now you generate a form based on table 1 (chair locations). In design mode, select the field Locations.... then go to the menu FORMAT, select CHANGE TO, and selsect COMBO BOX. In the properties panel, you need to go to the DATA TAB, ensure ROW SOURCE = T/Q, ROW SOURCE = LOCATIONS, BOUND COLOUMN = 2, LIMIT TO LIST = YES, then you need to switch to the FORMAT TAB and ensure that the COLOUMN COUNT = 2 and COLOUMN WIDTHS = 0cd;2cm

This means that people can select a chair by the record indicators at the bottom, (record 1 = chair 1 etc.), then allocate the relevant location using the dropdown box!

Hope that works!

(Sorry about the first post being wrong... wasn't sure, so tried building it! This works though!)

[jtn916]

I assume that you have added a foreign key relationship from your Location Table to your Chair table, thus a one to many relationship. Then you could search on Location Name.

Simple Example

Select chairID
from ChairTbl
Join Location
on LocationID = ChairLocationID
where LocationName='OffSite'

The result set being your cartesian product.

In short, your Location table is your parent table to your Chair table, a one to many relationship.

Hope this helps

Over Christmas I've taken on the task of putting together a database for work; I don't have a deadline but it would be nice to have something to show in the new year.

The database I am intending to build is to track the location of wheelchairs in the hospital where I work. Its a fairly simple system. But I'm a fairly simple person when it comes to databases - but within our department I am probably the most computer literate person so I seem to be the ideal candidate.

The chairs have unique numbers which will serve as the primary key. Where I am having difficulty at the moment is when I get to the location of the chairs. There are four locations where the chairs can be - 'On Site', 'Off Site', 'Out of Service' or 'In Storage' and each location requires different information to be entered.

I will have a table of all the chair numbers which will include all relevant/required information regarding each chair. This is more so the database can be searched to see what is currently available.

Ideally I want to create a database that serves two purposes. Tracks current locations, and enables everyone to see whats available. For tracking purposes, I imagine that someone would enter the chair number and then chooses the location where it is. My problem is choosing the location, Ie; 'On Site', I have no idea how to get the 'On Site' table to load for the information to be entered?

I have based this on one table for 'chair information' and another for each of the locations.

Any help would be greatly appreciated because quite frankly, I need it! I'm slowly wrapping my mind around the task but at the same time, lack the knowledge with Access to do it on my own.

[new comer]

hi this is a Q.
how do you cross check as i am very now to this.
i am trying to make a DB that shows incoming and out going stock and stock in stock totels

[autocrat]

You wouldn't be hijacking a thread would you?
of course not.... you n=meant to post a new thread and hit the wrong button, yah?
:grin:

start your thread, and you'll get more responses!

[new comer]

You wouldn't be hijacking a thread would you?
of course not.... you n=meant to post a new thread and hit the wrong button, yah?
:grin:

start your thread, and you'll get more responses!

sorry i am a bit lost i am not hijacking