Microsoft Access Forums - Database Design http://www.accessforums.net/ Database theory, design and best practices. en Wed, 19 Jun 2013 21:32:34 GMT vBulletin 60 http://www.accessforums.net/images/misc/rss.png Microsoft Access Forums - Database Design http://www.accessforums.net/ Sequential Primary Key generation across multiple tables http://www.accessforums.net/database-design/sequential-primary-key-generation-across-multiple-tables-35794.html Wed, 19 Jun 2013 14:11:15 GMT Hey everyone, My design may be inherently flawed but I have a manufacturing process I am trying to document via an Access Database (2000). A... Hey everyone,

My design may be inherently flawed but I have a manufacturing process I am trying to document via an Access Database (2000). A simplified example of the issue I am facing: I have different raw component lots that show up on incoming invoices (ItemInID), all of which need to pass certain quality control tests before they can be used in the production process. There are 3 different tests but not all tests always apply to each type of item. Some require 1 out of the 3, others 2, some all three.

I could just make a table that contained all the tests (tblFullQCLogID) and link it to the lot number (ItemInID). However, that would leave a bunch of blank records for each test that was not used. In actuality there are more than 10 tests so the potential for empty values is rather large.

Instead, my thought was to have different tables for each test type, (tblMoistureTestID, tblpHTestID, tblSizeID), but somehow have all the primary keys (MoistureTestID, phTestID, SizeID) sequentially numbered across the three tables. In other words, while the primary key for the moisture test (tblMoistureTestID) would have numbers 1,4,6,8,9... the primary keys for the other two tables would have the remaining numbers so that each individual test is unique. The end goal is being able to correlate them with the lot number (ItemInID) in a junction table to later pull the unique tests required for each item eliminating the need to store a mess of irrelevant blank values for those items that don't need a pH test for instance.

Unfortunately I am at a loss as to how to achieve this and feel as though I may be trying to reinvent the wheel -either that or I am improperly trying to normalize my data.

If you have any suggestions I would be very grateful. Also if I can do anything to make my explanation more clear I would be happy to do so.

Thanks a bunch,
-J.P. ]]> Database Design EdaxFlamma http://www.accessforums.net/database-design/sequential-primary-key-generation-across-multiple-tables-35794.html Design help for item location and testing date database http://www.accessforums.net/database-design/design-help-item-location-testing-date-database-35777.html Tue, 18 Jun 2013 20:06:12 GMT Hello all im new to the forum, hoping for some advise about designing a database in access 2007 to use at work on access 2010. hope this is the... Hello all im new to the forum, hoping for some advise about designing a database in access 2007 to use at work on access 2010.

hope this is the right thread.

I have been reading a watching reviews on the subject but cant seem to get it to work how I want it to with what I have tried so far.

I work for a constriction company, and my job is to keep track of safety equipment. this involves insuring that all equipment is up to date on the safety inspections and certificated this is done 6 monthly or yearly depending on item.

I also hire out the same equipment to users so I have to keep record of who has hired and the date it went out and came back.

at present this is all recorded on an excel sheet, with the shear number of items i believe a data base would be better suited.

i have just tried working out table relations again on paper as this seems to be my main problem right now.

the information that i need are as follows,
fleet name
fleet number
serial number
last test date
next test date
current location-on hire, in store, at calibration
user collected
date out
date in
supervisor details- name, email, phone number
job number
600 number

the idea behind it would be so that, i can quickly search fleet, see its location and book out if required. then update its location if it out for calibration and see if it requires calibration.

Also i would like to be able to quickly run reports to sent to supervisors that currently have fleet booked out to them so it can be returned for inspection.

at present this is how my tables look and the relations ships.

fleet table
fleet name
PK - fleet number - linked to fleet location table, fleet name
serial number
last calibration date
next calibration date
Calibrators. - linked to calibrators table, calibrators ID

Calibrators table
PK-ID
company name
date calibrated
date returned

Supervisors table
PK-ID - linked to fleet location table, supervisor
name
email
phone number
job number
600 number

Fleet location table
Fleet Name
fleet number
on hire
off hire
date out
date in
collected
supervisor

Can any one help or advise, i would be really grateful thanks

see attached copy of sheet im trying to convert to access. ]]> Database Design asoe209 http://www.accessforums.net/database-design/design-help-item-location-testing-date-database-35777.html <![CDATA[Where have my "Field Properties" gone?]]> http://www.accessforums.net/database-design/where-have-my-field-properties-gone-35754.html Tue, 18 Jun 2013 07:23:46 GMT I've been using Access 2003 for ages, but now since updating to 2007, the table's Form Properties are nowhere to be found. I've got a huge blank page with only the table and its fields showing, and I need to put in a phone numbering format.

I've read just about everything on the web and Access's help files, but can find nothing on revealing the Field Properties or a way of creating an input mask for telephone numbers. I should have stuck to the 2003 version.

Is anybody able to help me, please? ]]> Database Design BaldFox http://www.accessforums.net/database-design/where-have-my-field-properties-gone-35754.html relationship diagrams help http://www.accessforums.net/database-design/relationship-diagrams-help-35718.html Sun, 16 Jun 2013 14:05:29 GMT Hi i am new to ms access databases, really struggling with my tables relationships my database has more than one many-to-many relationships, for... Hi i am new to ms access databases, really struggling with my tables relationships


my database has more than one many-to-many relationships, for example one instructor can have many classes, one class can have different instructors, one client could have different instuctors, one instructor could have many clients and all at different times, on different days


eg: 18/06/2013 Time: 8:00, Instructor: Steven, Class :Pilates, Clients: Bob,
Andrew,
Jess,
Alex


Time: 9:30, Instructor: Thomas, Class :Yoga, Clients: Alexis,
Kerry,
Alex,
Bob

22/06/2013 Time: 10:00, Instructor: Greg, Class :Reformer, Clients: Bob,
Harry,
Jess,
Alex


Time: 16:30, Instructor: Fred, Class :Yoga, Clients: Alexis,
Kerry,
Alex,
Alexandra
(Each day can have many of these at any times)


really struggling on this one, its probably really though any help on relationship diagram would be greatly appreciated ]]> Database Design trexsteve http://www.accessforums.net/database-design/relationship-diagrams-help-35718.html Converting basic stock system from Excel to Access http://www.accessforums.net/database-design/converting-basic-stock-system-excel-access-35556.html Tue, 11 Jun 2013 14:57:36 GMT Hi,

I've created this Excel spreadsheet as a rudimentary way of tracking clothing samples that we receive. Although this will be OK for a short time the security is not good enough and it will soon be too much data to store in Excel.

I've only a basic knowledge of Access and although I feel I could create a product master table I do not think that I could design a table capable of tracking the movements of the items.

I have got as far as converting my named ranges on the data tab to individual tables and am planning to link them to the product master table. After this I am not sure how to proceed. As you will see on my Excel I have used a basic lookup to track any movements but I do not think this will be fool-proof enough to be the method used in the database.

Any help is very much appreciated.

Count.

Excel Log (Sample).zip
Attached Files
]]> Database Design Count Duckula http://www.accessforums.net/database-design/converting-basic-stock-system-excel-access-35556.html Autonumbering within Tables starting from a specified number http://www.accessforums.net/database-design/autonumbering-within-tables-starting-specified-number-35553.html Tue, 11 Jun 2013 14:11:36 GMT Hi All

[This is my first ever thread so feeling a little nervous! Please be kind to me :p]

Ok I am in the process of setting up a tracking database in Access 2013 to track the request, sent and returned number of soil sample kits for a state wise experiment, involving the general public. Each kit sent out will have a unique Kit ID number. It is possible for an individual to order more than one kit. In such instances, ideally I would somehow like Access to assign sequential Kit ID number from a specified initial Kit ID number without having to manually enter each Kit ID number; it is possible I will have farmers that could provide 100's of samples, as an example.

I was thinking of using something similar to what I've seen in OC (creating patient positions), for Access forms set up - e.g.

Customer ID [ABC1234]
Number of kits ordered [3]
First Kit ID Number Assigned [A0014]

Then in the corresponding table, using the IDCustomer unique field to link the info, in the associated datafields , Kit ID number A0014 will appear next to Customer 'ABC1234' and then Access automatically assigns the next sequential Kit Numbers 'A0015' and 'A0016' against the same customer ID.

Is this possible? (I hope this all makes sense!)

Many thanks in advance to anyone who can help!:):):) ]]> Database Design CJ40 http://www.accessforums.net/database-design/autonumbering-within-tables-starting-specified-number-35553.html Many-to-Many Relationships http://www.accessforums.net/database-design/many-many-relationships-35511.html Sun, 09 Jun 2013 20:53:25 GMT I am in the process of designing a database which would allow for easy collection and storage of publications by various Authors. One of the... I am in the process of designing a database which would allow for easy collection and storage of publications by various Authors. One of the requirements is that they would like to be able to email the form, have the users complete the form and send back, Access allows the option to email the form so that the user can complete. I created a table with all the required fields to use for the form. I emailed one of the form to myself for testing, I completed it and sent it back, it said the data was successfully added to the database, but when I checked the database, no information was added.

Another one on my approaches was to create separate tables to handle the different categories, since an individual could be in more than one category, then generate a query which would capture all the information. I need to know how to handle the many-to-many-relationship so that I can effectively capture everyone information. I am working with 8 tables. ]]> Database Design African http://www.accessforums.net/database-design/many-many-relationships-35511.html Design Help http://www.accessforums.net/database-design/design-help-35472.html Fri, 07 Jun 2013 12:06:44 GMT Hey I need some help designing my database. I have a lot of infomration and I don't know how many tables to make and have a hard time when I only have one primary key. My database is for customer surveys I sent out. Here's my idea of how to set it up.

-First table is called custmers: The fields are customer ID, Name, Phone number, address

-Second table: is their health plan, and line of business.

-Third table is survey information: Survey questions, and answers. Plus the date the survey was mailed and recieved.

I only have one primary key and don't know how to join the tables. The ID number is big so I had to make the datatype a double. ]]> Database Design MTSPEER http://www.accessforums.net/database-design/design-help-35472.html Simple stock keeping solution http://www.accessforums.net/database-design/simple-stock-keeping-solution-35470.html Fri, 07 Jun 2013 09:47:14 GMT Hi, I need to design a simple stock system within access. I want to create a table that will hold all of the products that we are supposed to... Hi,

I need to design a simple stock system within access. I want to create a table that will hold all of the products that we are supposed to receive and another to track the movements of those items (received, transferred, written off etc.) The main purpose is to be able to see how much stock is held in any given location and to be able to track the location of a particular item of stock.

I have designed something similar in excel but it is not quite as sophisticated or secure as I would like it to be and it is not easy to incorporate pictures.

Any advice would be greatly appreciated. ]]> Database Design Count Duckula http://www.accessforums.net/database-design/simple-stock-keeping-solution-35470.html starting a DB from excel spreadsheet, lots of calculations http://www.accessforums.net/database-design/starting-db-excel-spreadsheet-lots-calculations-35446.html Thu, 06 Jun 2013 14:22:44 GMT Hey, Im starting to build a Database to keep track of managing clients. I have setup a spread sheet that works well, the issue is that it takes to... Hey,

Im starting to build a Database to keep track of managing clients. I have setup a spread sheet that works well, the issue is that it takes to long to add a new client, and the data isnt in the most appealing way. I am hoping to set it up, to allow forms and reports to display information. my biggest road block is how to start since A lot of the columns are calculated. from my experience, and research you want to stay away from calculated fields. So I was planning on using queries to do this. No I have attach a sample spread sheet, With some of my notes, I am stuck on how to construct tables from this. Obviously We need a clients table, but from there im stuck.Attachment 12649 ]]> Database Design mike02 http://www.accessforums.net/database-design/starting-db-excel-spreadsheet-lots-calculations-35446.html Access Database Design Expert Required ASAP! http://www.accessforums.net/database-design/access-database-design-expert-required-asap-35438.html Thu, 06 Jun 2013 06:48:08 GMT I work for a construction company that needs to manage the maintenance for over 3,000 government properties. We require a system that will allow us... I work for a construction company that needs to manage the maintenance for over 3,000 government properties. We require a system that will allow us to effectively manage these assets, generate work orders with automatic task item codes (drop down menu's), generate reports regarding work order progress, priorities, etc.

Tips on how we would create this type of system would be great.

Thank you. :) ]]> Database Design devweb http://www.accessforums.net/database-design/access-database-design-expert-required-asap-35438.html Using Tables and Forms http://www.accessforums.net/database-design/using-tables-forms-35431.html Wed, 05 Jun 2013 17:32:01 GMT I am creating a database that will store analytical data from a variety of sites. Each site has different monitoring requirements. Each file number... I am creating a database that will store analytical data from a variety of sites. Each site has different monitoring requirements. Each file number will have many sample locations. I would like to have a macro to look up the file number and then display the form with required monitoring. Each form would represent one sample event. After filling in the data, I would like to be able to go to the next record and have the same information come up on the form (preferably without doing another search).

My initial thought was to have a table with each file number and required monitoring and use this to look up the information for the form. Then have the form populate a master table with all of the data from each file number's multiple monitoring locations. However, a brief search online looks like there may be a better way to do this.

Thank you for any help! ]]> Database Design Menglish http://www.accessforums.net/database-design/using-tables-forms-35431.html Research Study http://www.accessforums.net/database-design/research-study-35346.html Sun, 02 Jun 2013 22:49:16 GMT Hi, I am a db newbie and am trying to design a db for a large study that we are trying to implement. I have read up on the materials and although... Hi,

I am a db newbie and am trying to design a db for a large study that we are trying to implement. I have read up on the materials and although I do understand most of the single concepts, I am utterly at a loss with some of the interactions while putting everything together in order to create a functioning database.

Here is the overarching design:
Patients (consisting of many families) each with a unique identifier return to the clinic for a variety of measures completed not necessarily on the same of their repeating appointments. Thus most, if not all patients, eventually will have many records in the db due to their repeating appointments.

I don't remember how many different which ways I have tried to make this work, but somehow I have never been able to. I had the variables split into up to three tables and I have tired one-to-one, one-to-many, and many-to-many relationships, the latter with a junction table. Nothing seems to work and I am obviously something not understanding. I am hopeful that someone in this forum more experienced than I am would be able to give me a hand and pointers:

Here are the variables:

The next 7 are currently contained in tbl_Demographic:

PatientID (this is a only truly unique identifier)
FirstName
LastName
Locator Code
ZipCode
Birthdate
StudyReleaseDate

The next 17 variables all represent medical and psychological measures. Initially, the first 5 variables were contained in tbl_Psychiatric

LstMD
MedExp
RN
RTC
AIMS

whereas the remaining 12 were contained in tbl_Psychological

LstMH
MH
TxPlan
SMI/A
SMI/C
SA
Consent
Dx1
Dx1t
Dx2
Dx2t
Last DI85

Because I could not get that to work, I since have stored all 17 variables into one tbl_ClinicalData.
All clinical data is unique but I would prefer to handle the design by breaking the clinical data into the aforementioned two tables.

Because I only had one truly unique variable for the PrimaryKey (i.e., PatientID), I relied on the Autonumber function in Access to create Foreign and additional Primary Keys when I dealt with two or more tables in the one-to-many or many-to-many design attempts. The Relationships alone gave me nightmares since I do not understand for example if I should be indexing or not, which obviously has significant meaning on the overall design of the db.

The more I was trying the more I was confusing myself I suppose. I am however at my wits end and really would appreciate if someone could help me in my confusion.

Thanks

D ]]> Database Design Desastor http://www.accessforums.net/database-design/research-study-35346.html spreadsheet to DB http://www.accessforums.net/database-design/spreadsheet-db-35336.html Sun, 02 Jun 2013 06:05:55 GMT hey all,
I am having a 'time' trying to figure this out.
I m trying to convert a series of spreadsheets into a DB.
Please see attached spreadsheet.
They will start out choosing a SERN or Serial number and that has one to many relationship with item.
At most it might be one SERN's for tow Items but that's not the issue.
It then goes into a classification: O6, O5, E9, etc.
You see those at the top.
you could have 1 digit for say O6 (highlighted in yellow) and then at the bottom the associated cost for that column. ($223,578.62).
But look at the E6 column, also highlighted in yellow and it has more.
The only thing I can think of is to 'feed' or actually pull the SERN and ITEM into a separate table with look-ups with them being the first two fields.
Then create a field for each column name, O6, O5, O4, E9, etc.
The problem is that associated cost at the bottom.
For my O6 column highlighted in Yellow this is no problem as the associated costs is associated only with O6, and in one cell, for ITEM 10.
But go back over to E6, I can't associated the cost one time for 4, 6, 2 and 2.
Any ideas on this?
Attached Files
]]> Database Design REBBROWN http://www.accessforums.net/database-design/spreadsheet-db-35336.html Convert a positive number to negative based on another field in a Table http://www.accessforums.net/database-design/convert-positive-number-negative-based-another-field-35333.html Sun, 02 Jun 2013 01:21:03 GMT Dear Friends,

I am trying to create a table for income and expense [catergory] and would like the amount being entered into the [amount] field to have a negative or positive value on entering based on the category chosen.

so in my table I have

[catergory] which is chosen from a look up table ( which is either an INCOME or EXPENSE )
[amount] which is entered in the next field ( which has to return a positive or negative value based on the catergory choosen upon entering the data)

I would like to go one step further and indicate this negative value in red is possible in my FORM.


Hope you can help me- as I am unable to proceed at this point.

Thank you very much

]]> Database Design wazniw http://www.accessforums.net/database-design/convert-positive-number-negative-based-another-field-35333.html