New Access Database Help Please

[josh.clare]

Hey all,

I studied IT at college a few years ago where i breifly covered access.
Having never used access since, I am quite rusty and cant remember much.

I have been asked to create an access database for my company to keep record of all jobs past and present.

I need a form for booking in the jobs, a form for the completion of jobs, a form for the billing of jobs, a form for the destuction of jobs and a form for the location of jobs (all being linked to eachother)

In the booking in form i would like to have:
1. Collection date (the date in which the job was collected)
2. Company (the company in which the job is for)
3. Department (the department within the company)
4. Contact (the person contactable for the job)
5. Reference (the job reference which can have duplicates)
6. Pickup number (the pickup number which is the primary key)
7. Amount (the amount of boxes collected for the job)
8. Returned/Destroyed (whether or not the boxes are returned or destroyed upon the completion of the job)
9. Additional info (additional information/instructions)

In the billing form i would like to have:
1. Company (the company in which the job is for) from the booking in form
2. Department (the department within the company) from the booking in form
3. Reference (the job reference which can have duplicates) from the booking in form
4. Pickup number (the pickup number which is the primary key) from the booking in form
5. Billed (whether or not it has been billed. a simple Yes/No lookup?)
6. Billed Date (the date in which it was billed)
7. Billed By (the person it was billed by)

In the destruction form I would like to have:
1. Company (the company in which the job is for) from the booking in form
2. Department (the department within the company) from the booking in form
3. Reference (the job reference which can have duplicates) from the booking in form
4. Pickup number (the pickup number which is the primary key) from the booking in form
5. Returned/Destroyed (whether or not the boxes are returned or destroyed upon the completion of the job) from the booking in form
6. Destruction Date (the date in which it is to be destroyed)
7. Authority Received Date (the date in which we received authority to destroy the boxes for that job)
8. Destroyed Date (the date in which the boxes were destroyed)
9. Invoiced (whether or not the clients company has been invoiced for the destruction of their boxes. a simple Yes/No lookup?)
10. Certificate (whether or not a destruction certificate has been issued. a simple Yes/No lookup?)

In the location form i would like:
1. Company (the company in which the job is for) from the booking in form
2. Department (the department within the company) from the booking in form
3. Reference (the job reference which can have duplicates) from the booking in form
4. Pickup number (the pickup number which is the primary key) from the booking in form
5. Location (the current location of the boxes for that job)

I can create the tables for these but do i make one big table for them all seperate ones which link together and if so how do i link them together?

I would also like to create searches in these forms so as you can find certain jobs depending on their current status.

I would greatly appreciate any help/assistance on this.

Thanks,
Josh

 

[Guus2005]

You might want to check here first. Have you read all the sticky's?

HTH

 

[josh.clare]

Hey Gus,

Thanks for the suggestions,

I just looked at the available data modules but am unsure as to how they help and what i am supposed to do with them?

I have also read the sticky's but they seemed to be more like rules etc.

Am still in need of help in creating this database.

I have created the tables but am unsure how they should link to eachother is it relationships :s.

Sorry, i am fairly new to this.

Thanks,
Josh

 

[Guus2005]

It looks like your starting point is from a form.
your starting point should be your data.
For that data you need to create a relational database.
When your done doing that, you can continue with the forms and their data.

That's why i pointed to the datamodels.

HTH

 

[josh.clare]

can i upload what i have done so far, because i have started off with tables, and created relationships but i dont think it is working correctly or i have done it correctly?

Thanks,
Josh

 

[CBrighton]

Guus is correct.

Forget what you want on each form for now, that comes later. They key is the relational table structure.

You need a table for each "thing". E.g. tblJobs which holds all job data, tblCompanies for company data, etc.

You need to look at the data you intend to store and work out how it should be grouped together. Will each job have multiple actions? If so I expect these actions should be in another table, linked to the jobs table. Using this example you would have many actions to a job, many jobs to a company. In this example this gives you a basis for your structure with 3 tables, each in a one to many relationship.

 

[josh.clare]

Hello,

i have uploaded a very basic access file:
http://www.scandocs.co.uk/josh/database6.zip
here is what i have so far which i think is correct.
Could someone please advise if i am doing it the best way and how i would link them together?

Thanks,
Josh

 

[CBrighton]

Unfortunately I do not have Access 2007, I can only look at .mdb files.

If you attach an .mdb file I'll have a look, otherwise I'm sure someone with Access 2007 will look in here at some point.

 

[DCrake]

First and foremost do not use table level lookups. Plenty of discusion on this site bout reasons why.

 

[josh.clare]

hey,

i have re uploaded it
http://www.scandocs.co.uk/josh/database.zip

what should i use instead of the lookup then?

thanks,
josh

 

[CBrighton]

How exactly will this data work.

Will a single company have multiple jobs? If so the PK in the company table should link to an FK in the job table.

The same with billing, is it multiple bills for a single job or 1 bill per job? The first would be another one to many relationship, the second would be one to one (or a merging of the tables).



At the moment your tables lack foreign keys which means they cannot be linked. For example, I would rename the field 'Reference' in the table COMPANY_REF to something like 'CompanyID'. I would then create a field in JOB_REF named 'CompanyID' and use those two fields to create a one to many link between the two tables.

 

[josh.clare]

yes there are mutiple jobs for single companies but each job will have a unique "pickup number". the unique primary key in the company table is the "reference".

There may be occasions where there are multiple bills for single jobs too.

how do i go about creating these links on the primary keys?

i want the tables to merge together so that the job displays what company its for etc

and then in the billing/location/destruction tables display what jobs there fore etc. so select a job and then fill out whether or not it has been billed etc.

thanks for the help,

josh

 

[DCrake]

What you really nead to do is to go to the accounts office and ask them for the mot complicated order recevied from a customer. Get all the paperwork you can and split it up into its components, Quotation, Order, Picking list, invoices, etc. Then lay them out on a big table and work out which goes in which pile.

For all the documents that are on their own would be parent tables, any that have more than one are child tables with relationsship between the two.

It may seem silly but this is the best way to work out your table requirements and the primary and foreign keys needed to build your relationships.

 

[josh.clare]

Hello all,

Sorry for the delayed responce,
I have been playing around and i think i am slowly getting there.
www.scandocs.co.uk/josh/test.zip
I have a couple of things i want to do but am unsure how.

1) I want to have lookup/drop down menu type things (sorry, do not know the correct term)

In the LOCATION table, Building Column; I want it to have the following options: Unit 1/Unit 4/Unit 47/Other

In the DELIVERY table, Delivered by Column; I want it to have the following options: MISL/Post/Courier/Other

In the JOBS table, Return/Destroy Column; I want it to have the following options: Returned/Destroyed/Not Applicable

2) I want the following columns in the JOBS Table:

DELIVERY TABLE - Delivered
DESTRUCTION TABLE - Destroyed
BILLING TABLE - Billed

I want these to only be edited in the individual tables (DELIVERY/DESTRUCTION/BILLING) but show/update in the JOBS table relevant to the pickup number

3) I want the DESTRUCTION table linked to the LOCATION table so that once there is a yes/tick in the destroyed column in the DESTRUCTION table, it removes it from the LOCATION table.

Would be greatful if someone could help/advise how i would go about doing this,

Thanks,
Josh