Tracking 250 Pieces Of Data Per Client. Table Structure Help Needed.
Mar 12, 2006
hi everybody,
great resource you have here! my employer is tracking around 250 pieces of data for each client at our facility. i am making a new access system based on their existing mysql database and web front end. before i start messing around with forms and reports, i want to see how well this existing structure will work in access, and what kind of approach i should take. i am a newbie with access, but lots of experience with asp/mssql/php/mysql. making web forms is so time consuming that i figured i would be best off moving the whole thing to access and starting from scratch.
client information is stored in eight tables. each table has around 30 fields in it. the first table has a primary key autonumber, and the other seven tables have foreign keys with unique constraints that point back to the first table. that is, for each client record in the first table, there can only be exactly one corresponding record in the other tables.
i did some data massaging, and got the eight client tables into one big table, but the resultant table has almost 250 fields in it, and access doesn't seem to like working with tables that big. so i am thinking that it is best to leave the eight tables separate, but linked in one to one relationships.
i was kind of ideally visualizing a form with eight tabs so that i could edit/update all of the information from the eight tables rather seamlessly.
my question is: what approach to table structure will best suit my needs, and what approach should i take to add/update/delete the info with forms? will i need to do vb for this? any good one-to-one example databases anybody could point me at?
thanks a million,
harry doyle
View Replies
ADVERTISEMENT
Nov 16, 2004
Hi everyone,
Could someone please share their opinion with me on the following:
I have a database with a Table which stores People's details, e.g. ID, Name, Surname, ...., etc (e.g. tblPeople) .
Now I have people filling out a questionnaire, of which the results I want to save in an Access table. Each person will fill out this questionnaire only once. The number of fields I will need to accomodate the answers to questions asked, is around 120. For example, an answer to Question1 will appear in a field called Q1.
I know that the limit for fields in a table are 255, and my total fields in tblPeople so far are about 20.
Should I keep Q1 and all other fields in the same table as tblPeople, or create another table to hold all answers and create a one-to-one relationship between tblPeople and tblQuestions&Answers ?
Can someone give me a tip, in particular if this will cause performance problems?
I appreciate your effort for reading this.
Kind Regards,
Jean
View 2 Replies
View Related
Dec 29, 2004
I manage academic papers whom are written by multiple authors and reviewed by multiple reviewers. Currently, all the data is stored in one big table and I'm not able to get any statistics or real tracking out of it.
I have set up a test DB with 3 tables so far:
Main
Table_ID -auto sequential key
CORP_ID -internal paper id
STATUS -open, overdue, accepted, rejected
REC_DATE -received date
MOD_DATE -record last modified date
TITLE
AUTH1_ID -linked to Author.ID
AUTH2_ID -linked to Author.ID
REVR1_ID -linked to Reviewer.ID
REVR2_ID -linked to Reviewer.ID
Author
ID -key field
NAME
DESIG -designation
EMAIL
PHONE
Reviewer
ID -key field
NAME
DESIG
EMAIL
PHONE
RATING
My goals are these:
Create a module that manages the authors. They are usually the same group of people. Each author should only appear once.
Create a module that manages the reviewers. Also, usually the same people (but different than authors). Each reviewer should only appear once.
Create a main form that allows the end user to add a record (paper), assign author(s), assign reviewer(s).
Create Query/Report that would then be able to:
- track authors and their papers
- track reviewers and their reviews
- as well as track papers and their authors/reviewers
When I create subforms, they appear to be 'backwards' meaning that the Author form tends to be the main form and the Main form tends to be the subform. I think this is b/c the Author.ID field is the key field, whereas the Main.TABLE_ID is the key field, but not relevant for other than keeping things straight, nor is it related.
Can someone point me in the right direction?
Alternatively, has someone created a database like this already that I can buy? :)
Thanks!
View 1 Replies
View Related
Oct 31, 2005
Firstly, I want to admit that my knowledge about data base construction is fairly basic.
I am trying to build a data base for humanitarian projects that do not at all resemple of the usual examples including customers, invoices, suppliers and what have you. My problem in constructing the DB is that most of the projects will address more than one subject and also include more than one target group and even operate in more than one country.
In my first attempt I filled the relevant fields (subjects, target groups and country) with more than one value using a form with multi select lists. The problem was that it was very difficult to query these multi-value fields and kind people in this forum strongly advised my to reconstruct the DB avoiding such fields.
What I need is to be able to view/print various selections based on precise criteria that include a region (or country), a subject and a target group in all kind of combinations.
The problem is to decide on what tables to establish and their interrelation. I guess that when entering a project record (using a form) I will have to store somewhere what subjects (could be more than five) and target groups the project include. It is, of course, not a problem to establish special tables or value lists for the subjects and target groups and countries, but where/how do I store the basic project information for a project and the multi-values that are related to the individual project?
I hope that this is not too confusing and I would appreciate any suggestion for a simple table structure and the interrelation between the tables.
Many thanks in advance.
Niels
View 1 Replies
View Related
Feb 14, 2014
I've been working on creating a database for a Physical Therapist that will store patients information and information on past appointments (including the amount of money paid per appointment). I created a form that he can then easily select the patient and their D.O.B. and insurance carrier will show up.
But I need to create a way that when he then selects the treatment received for the appointment that the correct payment amount will show up and be recorded as well. The reason this is an issue is because for each insurance there is a different cost per treatment.
View 3 Replies
View Related
Feb 28, 2013
I am developing a claim tracking database that tracks dates of events that occur in the course of processing a claim; such as, Loss Date, Report Date, Estimate Date, Payment Date, etc.
I currently have the following tables set up:
tblClaim
ClaimID
ClaimNumber
tblEvents
EventID
EventName
tblClaimEvents
ClaimEventID
fkClaimID
fkEventID
EventDate
With this table structure there is a many-to-many relationship between Claims and Events, but, there will only be one of each event per claim. Is there a better way to set up the tables to enforce a 1-to-1 relationship?
I was planning on creating a form for the Claim table with a subform for the Events joined on ClaimID and fkClaimID, but that would allow users to create more than one set of Events per claim, if that makes sense.
View 4 Replies
View Related
Nov 15, 2012
I am trying to create a table which would include financial data, by year, by client. For example:
Client #1
2009 2010 2011
Revenue 5000 10000 1200
Expenses 2000 1000 700
Net Income 3000 9000 500
Client #2
2009 2010 2011
Revenue 5000 10000 1200
Expenses 2000 1000 700
Net Income 3000 9000 500
Etc.
Should I have headers as such:
Client #, 2009Revenue, 2009Expenses, 2009NetIncome, 2010Revenue, 2010Expenses, 2010NetIncome, 2011Revenue, 2011Expenses, 2011NetIncome
View 6 Replies
View Related
Jan 17, 2014
I have a table with duplicate records (which is ok) and I want to return distinct data for each client.
It works fine when there is only two fields returned however, when I add a third field to the query it no longer returns only the Distinct records - I am getting Duplicates returned.
I.E
SELECT DISTINCT tblClient.ClientNo, tblClient.Name
FROM tblClient
Works fine with only the Distinct records for each client returned
However
SELECT DISTINCT tblClient.ClientNo, tblClient.Name, tblClient.Address, tblClient.OrderValue
FROM tblClient
Now returns Duplicates!
Is there a limit to the number of fields to be returned using DISTINCT or what else could be the problem? Should I be doing this some other way?
View 5 Replies
View Related
Mar 25, 2006
Hello,
Is it possible please to extract a tables structure and data into SQL in Access. I know how to do this in phpmyadmin (an internet based DBMS) but not so sure in Access.
What I want to do is to create an identical version of the table in SQL Server, so therefore require the structure and data in SQL format if possible.
Any help is much appreciated, thank you.
View 3 Replies
View Related
Nov 9, 2005
Hi all
Brand new on here and desparate for some help and guidence.
So far with Access I have just used it as a store of addresses to mailshot prospective clients.
However, I now need a more complex database and this is where you might be able to help.
First things first, most of my clients are in universities. This means that I can be used by more than one person in more than one department at a university.
Does this mean I need to do three tables:
1/ "University Details" which gives the address details
2/ "Department" storing the departments of the unis we work for
3/ "Client" Name of the client(s) in that department.
After this how do I link them?
Thanks
Razieluk
View 4 Replies
View Related
Jun 1, 2005
Hope the thread title wasn't too confusing.
I have a database that tracks emissions from painting. Bear with me since this is going to be a long post.
:o
Some background info.
- a paint can consists of many parts mixed in a specific ratio.
- a part cosists of many chemicals
- a part may be used is many different paints
Here is how I have the existing database structured now. I’ve simplified it somewhat.
tblPaint
PaintID (PK)
PaintName - String
PaintDensity - Double
PaintVOCContent - Double
tblPart
PartID (PK)
PartName - String
PartDensity - Double
PartVOCContent - Double
tblRatio
RatioID (PK)
PaintID (FK)
PartID (FK)
Ratio - Integer
tblChemicalWt
ChemicalWtID (PK)
PartID (FK)
ChemicalID (FK)
WeightPercent - Double (Percent)
tblChemical
ChemicalID (PK)
strChemicalNumber - Long
strChemicalName - String
tblUsage
UsageID (PK)
PaintID (FK)
UsageDate - Date
UsageAmount - Double
PK = Primary Key (Autonumber)
FK = Foreign Key (Autonumber)
The Density or VOC Content (VOC = Volatile Organic Compound) for a paint can either be given OR it can be calculated by the mix ratio of parts and their respective Density or VOC Content values. One or the other must be complete.
What I did not account for was that there may be changes due to the paint manufacturer revising their paint composition, such as;
the parts that make up a paint may change
chemical make-up of a part changes (can be a change in Weight Percentages or the addition or deletion of a chemical).
ratio in which parts are mixed for a paint changes
Density/VOC Content values may change for a Paint or Part
The problem is that I cannot simply change the existing records as the emissions are calculated using all the data from each table and emissions need to be calculated using the paint/part/ratio/chemical weight percent info that was valid at the time of usage.
Another thing is that the Paint Name will not change, it’ll always be something like “BrandX Acrylic Blue”.
The person entering usage data only knows how much of what paint was used for a given day.
The person who enters paint usage has nothing to with entering the chemical make-up for parts and information for the paints and vice versa.
At any rate, my new draft table design is as follows. Two of the tables (tblChemical & tblUsage) will remain the same.
tblPaint
PaintID (PK)
PaintName - String
tblPaintVersion
PaintVersionID (PK)
PaintID (FK)
PaintDensity - Double
PaintVOCContent - Double
PaintVersionDateIN - Date
PaintVersionDateOUT - Date
tblPart
PartID (PK)
PartName - String
tblPartVersion
PartVersionID (PK)
PartID (FK)
PartDensity - Double
PartVOCContent - Double
PartVersionDateIN - Date
PartVersionDateOUT - Date
tblChemicalWt
ChemicalWtID (PK)
PartVersionID (FK)
ChemicalID (FK)
WeightPercent - Double (Percent)
I might be able to do away with tblRatioVersion and just have one table to store the mix ratios. It should be the case that a change in mix ratios (either a change in mix ratios and/or what parts make up a paint) means a change in the Paint Density & VOC Content. But I am presenting both versions of the Ratio tables here for completeness.
Version 1
tblRatioVersion
RatioVersionID (PK)
PaintVersionID (FK)
RatioVersionDateIN - Date
RatioVersionDateOUT - Date
tblRatio
RatioID (PK)
RatioVersionID (FK)
PartVersionID (FK)
Ratio - Integer
Version 2
tblRatio
RatioID (PK)
PaintVersionID (FK)
PartVersionID (FK)
RatioVersionDateIN - Date
RatioVersionDateOUT - Date
Ratio - Integer
I plan on having the DateOUT fields be populated automatically to match the DateIN for the new version. That way I can use “BETWEEN DateIN and DateOUT” to select the appropriate info for calculating emissions. The idea came from an old thread I started (http://www.access-programmers.co.uk/forums/showthread.php?t=31677&highlight=historical+data). I think this is the way to go, but with all the relationships going on, I'm having a hard time wrapping my head around it all. Am hoping someone here can help me with this.
Anyone see any problems with the new table design?
Anyone know a better way?
:confused:
Some potential issues that I see
If only the Density/VOC Content changes for a Paint, then the old set of records in tblRatio must be duplicated.
If only the Density/VOC Content changes for a Part, then the old set of records in tblRatio & tblChemicalWt must be duplicated.
Thanks for reading this post all the way to the end!
:D
EDIT: Thought about it some more.
A new version of a Part, should trigger a new version of Mix Ratios which in turn should trigger a new version of a paint.
Part --> Ratio --> Paint
Ratio --> Paint
Also, a change in a Part must trigger a New Paint version for ALL Paints that currently use it!
:eek:
View 3 Replies
View Related
Mar 4, 2012
I'm creating a Dating Database on MS access, Now I've been given the attributes for all the tables. They are five (Client, Hobby, Meeting, ClientHobby, ClientMeeting)
Now I'm needed to create a form which allows me to enter the client data + at least two interests and their age calculated from their DOB. Seeing as I was only asked to put DOB in the actual Client table, I thought I'd create an Age query and then make that form out of the Age Query. (I already successfully managed to get the Age out of DOB in that query in a field of its own)
However, how can I add the hobbies field to that age query so I can finally create the form that I am required to do?
Second, I am to create a query which will be used for ‘matching’ clients. The criteria that you will use are: gender, age (using a range e.g. 20-25), interests/hobbies and city/town, this query ill be used to create a report which lists all the clients who match the entered criteria. How am I to do that?
View 4 Replies
View Related
May 20, 2015
I have created 2 tables: Client and Routing.
Each client record is unique. A client can have 'several' routing records.
Key field in Client is 'Client ID'.
Routing table has foreign key of: Client IDFK
I created a relationship of one to many from Client table 'Client ID' to Routing table 'Client IDFK'.
I created a form for the Client table and works.
Where I am having issue is: Client data is not populating into the following Routing form.
* I want a 'Routing form' that you can lookup client info and place it into that form.
* The bottom of the form will be all the routing table fields. The new routing info will be entered into it.
My client needs that form printed for the driver. Client will have many routing forms(records) but only one client record. The driver will have one completed form for each time he picks up client.
View 3 Replies
View Related
Mar 27, 2014
I have two tables(see below). I want to set up a query, link these 2 tables together. I set a one-to-one relationship between Client ID in two table. But got error message :"Type mismatch in expression".
I tried to change Client ID data type from "Text" to "Number", then Access deleted some data under Client ID in Order table.How can I make this work, but not having to re-type in all data?
Client Table:
Client ID(Autonumber)
Client Name (Text)
Client Address (Text)
Order Table:
Order ID(Autonumber)
Client ID(Short Text)
Unit Order(Number)
Unit Price
View 3 Replies
View Related
Jul 31, 2013
I'm not sure if Access is the right program to use for this scenario as I only know the basics on creating the database.
I want to be able to create a client database and then be able to generate a number of standard template letters at any particular time for selected clients.
I don't want a mail merge!!
Can access be used for this?
Ideally I would like to create a form for administrators to use to input new client info and update existing clients info and then just be able to click a button (or the like) to generate the standardized letter for a particular scenario.
View 1 Replies
View Related
May 10, 2013
I'm looking for some input on table design.
I work in a lab and am looking to track data associated with samples on PCR plates usually in 2 formats
96 well (8x12) rows A-H and Columns 1-12
384 well (16-24) row A-P and Columns 1-24
each well will have at least 3 pieces of information associated with it:Sample
Assay
Qulaity Score
but more may be added as I progress with the design.
field such as run name run date and operator for each plate would also be needed
What would be the best way to structure the table?
Obviously listing well A1, sample in A1, QA for A1 etc etc as fields wouldn't work as theres a 255 field limit and 3*96 = 288 and thats before we even get to 384
I could have a table for each run but that feels like bad design to me, as if you wanted to look at quality trends across time you would need to query multiple tables and query the date associated with each to even knwo which table to look in
A table for each well along with its QA score, assay and a look up field linking it to a list of plates, which would contain the run date etc. (this table listing runs is already present in my database)
Some background on current structure
Tables
Booking - contains a list of worksheet numbers along with info such as submitter, and conditions for the test such as assay
Experiment - expirment number conditions and associated DNAs
DNA - a list of dna samples
Plate - name of plate and info such as run date and associated worksheets
View 4 Replies
View Related
Oct 26, 2012
I am looking to track the salary information for my small firm over a number of years. Does it make sense to create a table with multiple fields that would be structured like this (2011401(k), 2011Medical, 2011 LifeInsurance, 2012401(k), 2012Medical, 2012LifeInsurance, etc) or is there a better way to tackle this?
View 14 Replies
View Related
Jan 25, 2008
Hi,
Firstly, I apologise if this has been posted before. I have done a couple of searches of the forums, but can't find what I'm after... and being new to access, I'm finding it hard to know what exactly to look for. How to even explain what I want is difficult!
I am designing a database for the clients we deal with a work. It started out as being a database of 'post' and correspondence, so the Idea was, we'd have a databse of all the clients... and by selecting their names, it would bring up a list of all the letters that have come in for them. I have managed this part fine - probably basic, but fine.
What I would like to do, is expand it to more than just 'letters'. Each client has done a particular 'work' with us, and each client has particular details that are unique. For example, a client will have made certain transactions through a bank account.
I hope to be able to click on a client, and have a screen or page pop up with details and comments about that particular client. I have tried hyperlinking but that doesn't really produce what I'm after. For example, I tried creating a "report" with a page for each client, so that I could have comments for each client - hyperlinked to the table. But all it does is link to the "whole report" and not a particular page for a particular client.
I'm sure this is sounding really confusing:
I have a client named "Peter Litman". His name and details are within a "client" table (listing all clients). I hope to be able to click on his name (or row) and bring information (comments, details etc.) about him. I thought this could be some sort of "pop up", or link to a special page. ??
The same would then apply with all other clients.
Any ideas? questions?
thanx.
View 2 Replies
View Related
May 13, 2012
How to use VBA..... I have set up a simple database to enter data into then upload to a client web portal.
I have a form to enter the data and the table this data goes into has a check box at the end which I want to have ticked/checked/True after I have exported.
I have created a query that shows me all data that is unchecked (not yet exported to an xls)
I have created a simple export macro that sends the query result to an .xls
I want the macro to finish by checking the "export" checkbox once I have done this....
I am using Access 2010 attached is a screen dump of what I have so far....
View 1 Replies
View Related
May 28, 2013
Using VBA (in Access), what code should I use to check if a client name already exists in a table.
Say, client name is "Smith", my table is called tblClient and the field name in the table is called fldClientName.
I just need to do the check, that's all.
View 3 Replies
View Related
Nov 23, 2013
The Setup: There is a clients table, a property table and a jobs table. Each client can have multiple properties and there can be multiple jobs done on each property.
The form: I created a form that uses a drop down to select the client, once the client is selected the subform shows the properties for that client. I can navigate through the properties to select any of the required properties and on this same subform, there is a button to open the jobs related to the property selected.
The problem: The form and subform work fine. The last form (the button that opens up the jobs) works ONLY IF there are records available. If there are no jobs for that property, it doesn't allow me to enter a new record. I'm pretty sure that the issue is because the jobs form has a query record source that uses all three tables linked (I'm pulling the clients name from the clients table and the property name from property table).
What I need is some vba coding to check if the recordset is empty and if it is then add this entry. Also, I'm not sure if this should add this code to the button or on the on_load of the jobs form.
View 8 Replies
View Related
Mar 10, 2015
For my database the business has Clients who they conduct Needs Assessments for, but they only conduct 3 needs assessments. I have a Needs Assessment table with Client ID as the foreign key. how I can limit the number of times a Client ID shows up in the foreign key field to three?
View 2 Replies
View Related
Jun 14, 2013
I'm doing a database that tracks customer projects and orders.
The Header table contains all the Customer and Project details like customer name, contact details, project leader, etc.
A second table BOM contains the customer orders for a particular project.
I need a way to track changes or revisions to the BOM. It happens when a customer makes changes on their orders like quantities, etc.
View 7 Replies
View Related
Nov 4, 2013
I have my database and my form all created and working. Reports and everything are in place. But we need to have a record of what changes have been made. Essentially what I want to make is a table that is essentially a log of all the changes that have occurred.
What I have is essentially a time clock database. So when someone enters our facility the guard will check them in and out. What I want is a list of all the ins and out for a day. So it will be a running list. Then I will create a query to return results for a report. But where I am having problems is creating a table that will house this data. So out of my main table I want a child table that will have certain data fromt he parent table. Mainly the time in and out along with the name of the person. There will be multiple repeat data entries in this table and I am ok with that.
View 14 Replies
View Related
May 27, 2012
I receive monthly project tracking reports and I'm looking to compare a few things between these monthly updates. I should add I'm new to Access and have been using Excel and dual monitors to manually compare data sets!!! My life might be getting a LOT easier.
Here's what I'm tracking:
I get monthly excel reports that have unique values from a "Project ID"each "Project ID" stays the same every month, but the project status changes from "Installed", "Commitment", "Pre-Commitment", etcSometimes new projects are addedSometimes old projects disappear from the reportA few other columns have numerical data that changes every month as well "Project Cost", "$ Incentive", etc.
Here's what I'm trying to automate:
Find out what Project IDs are newFind out if old Project IDs are missing from the new reportSomehow "flag" or identify projects that have status changesSomehow "flag" or identify which numerical values increased or decreased i.e. "Project Cost", "$ Incentive", etc
Do I create two tables? Do I append the new monthly report to the old one when I import my data, then somehow work with the new information?
View 1 Replies
View Related
Sep 23, 2013
I need to update a table at a client's site to change a couple of fields, and would like to be able to send them something that they can run on their end to perform an insert query.
I cant update the tables via RDP etc. because the database needs to be in Access '97 format, and they all run access 2003.
I also cannot get them to send me the file for updates since it needs to be online basically 24/7.
Is there any way for this to happen?
View 5 Replies
View Related
