Database design help

[DaveyJ]

Hi Guys,



I'm hoping you can help me out,

i have a database with 5 tables

userinfo,softwareReq,HardwareReq,VoiceReq,ReqID's

each are a one to many replationship with unserinfo with the employee ID as a foriegn key in each table , also ReqID, a foriegn key in each,

a user can many requests of each category

can someone help and advise the best way to approach this, i thought i had my design right , but when writing queries, it's not pulling the data i require.

[Datagopherdan]

It's hard to say the correct way to set it up without having more information. As far as the queries, can be a number of reasons why you're not getting the results that you want. For example if you're using all the tables then it's only gong to display records where every single table has an instance of a record unless you changed the query relationships.

[DaveyJ]

Can i send you the database to look at ?

[Datagopherdan]

I wouldn't have the time to go through your database but if you can provide me a little background on it and the basic logic, I might be able to provide you some answers.

Can i send you the database to look at ?

[DaveyJ]

Hi Dan,

thanks for your response

basicly the database is for employee requests for software , hardware, and voice(phone numbers etc) requests

i have created 5 tables

Employee details
software requests
hardware requests
voice requests
request numbers

employee details uses their employee ID as the primary key which is a forgein key in each of the other tables

the request number tables uses the request number as a primary key which is a forgein key in each of the tables

software requests is a list of all the applications available to order for the employee all with a datatype yes/no

same for hardware and voice

so when i write a query to select everything where software , hardware , = yes

if they are not all true , no results are returned.

i am by no means good at Databases so any help is greatly appreciated.

i have a feeling that the design could be better with the help of a more experienced user.

thanks in advance

anymore information , please let me know.

[Datagopherdan]

If I'm understanding you correctly, the software, hardware and voice request tables have the same fields, which would be a desciption/type of either software, hardware, or voice. That being the case, a better way to set this up would be to have a table for your requests, which we'll call "tblRequests", which would have the below 3 fields.

ID - AutoNumber (Primary Key)
Type/Description - A description of the software, hardware or voice request.
Classification - Used to enter the classification type (Software, Hardware, Voice)

Create another table for your employees, which we'll call "tblEmployees" and have the basic employee type fields plus an ID (Auto Number field for your primary key)

Create a 3rd table to record the request transactions, which we'll call tblTransactions. You would have the below fields.
RequestID - Long Integer type field that would have a many to one relationship with the ID field in "tblRequests"
EmployeeID - Long Integer type field that would have a many to one relationship with the ID field in "tblEmployees"
Date of transaction, cost and any other fields that you would want.

That's the only tables that you would need. You would just create a form based on "tblTransactions" and create comboboxes for the RequestID and EmployeeID fields using the respective tables for the record sources. You would then just create your query on those 3 tables.

[DaveyJ]

Thanks Dan,

Will give this a shot

thanks very much for your time

[Datagopherdan]

Not a problem. Just let us know if you run into problems.

Thanks Dan,

Will give this a shot

thanks very much for your time