Is My Table Setup Correctly...how Would You Do It?
Dec 1, 2006
Hello,
I am rather new to Access and looking for some help with my table. I have a report that I do, that I used to do in Excel and was wondering if it was setup right or if there was a better way of doing it.
From looking around I noticed that people usually have mulitple tables and such, while mine is all in one. I see there is a table analyzer but am not sure if I should use that, or what the importance of having it split up.
I have attached my table in XML if anyone wants to share opinions on how they would set it up or what they would do.
Is there a bad thing to having it all in one table?
Thanks for any insight or help.
View Replies
ADVERTISEMENT
Sep 7, 2005
I have ta table that has the following fields:
factorID (Primary key, AutoNumber)
supplierID (Foreign key to suppliers table)
categoryID (Foreign key to Categories)
Factor
I'd like to ensure that there is only one Supplier/Category pair in the table, but I can't figure out how to set it up to achieve this. Any help will be much appreciated. Thanks.
View 5 Replies
View Related
Feb 3, 2006
I've thought I had a decent understanding of database normalization but I've come across a fairly simple problem that I'm having difficulty resolving.
I have a database which I use to track submittals from companies.
Originally it was set up as follows:
tblCompany
Company_ID (PK)
Company (No duplicates allowed)
tblSubmittals
Submittals_ID (PK)
Description
Date
Company_ID (FK)
Now I've decided that I want to add a contacts table with fields such as first name, last name, company, etc. My dilemma is that in the Submittals table I need to keep track of which company the submittal is from but I don't care WHO it is from.
So I created another table as follows:
tblContacts
Contact_ID (PK)
Company_ID (FK)
First_Name
Last_Name
This will work except that when I add a new contact I will have to add code to see if the company exists in the tblCompany before I add it.
It doesn't seem like this would be the correct way to set this up because I'm adding the same data to 2 different tables.
I'm sorry if this seems like a stupid question. Thanks for your input.
View 5 Replies
View Related
Mar 29, 2006
HiNew to the forum and pretty new to MS Access.Here's what I intend to do.I want a table for Special Tasks. This will include TaskID, Date, Time, Reason, Description and Dependencies.I want a table of staffFor each task each member of staff will have a tick box for "Needs to Read" and "Has Read".In a form I want to be able to enter new tasks using the task table, I also want to use a subform that allows me access to the list of staff and if they have read the task or not.There is probably an easy way round this but at the moment it's just not clicking into place.Can someone point me in the right direction for table setup and relationships ??
View 1 Replies
View Related
Dec 27, 2007
Hello, I am having difficulties normalizing my tables. So far, i have 4 tablestblODF:- ODFNumber (PK, manually enter ID)- UserName (txt)- QueueID (number)- ScanDate (date)- Status (text)tblEmployee- EmployeeID (pk, autonumber)- UserName (txt)- LastName(txt)- FirstName(txt)- Extension(number)tblQueue- QueueID(Pk, autonumber)- Queue (txt)tblStatus- StatusID(pk, autonumber)- Status(txt)I am also having problems setting up the relationshipsI want to setup a query and i want all these data to appear:- ODFNumber (txt)- UserName (txt)- ScanDate (date)- Queue (txt)- Status (txt)I am really stuck in setting up the tables and their relationship. So in short, help:DThank you, (some of you might recognize this project)
View 4 Replies
View Related
Mar 17, 2008
I've approached this once before but I never did figure out how to go about setting up this table. If anyone can steer me in the right direction I'd appriciate it!
Overview: I have 25 products that I track repairs on. I am given quotas every month for some or all of those products. All this data needs to be kept vs overrighting last months quota information with this month's.
What I have done was created a table with columns of: ProductID, 1/08, 2/08, 3/08, 4/08, etc... and ran that out for a years time. So, that is how my table is currently formatted...
Now when I need to pull out information I have a query that looks at a particular month column and compairs that to my work table. However, every month I need to go into that query and change the column name it looks at in my quota table. This again works, but is it the best way? I also need to pull out history information month by month for a production graph. The query I had to build again requires me to manually go in and enter new month information every time it's needed.
It just seems to me that there is probably a better way to format this quota table. If I remember correctly someone suggested to me that I needed to normalize this table; and the format suggested was more like:
productID; date; quota
But instead of just having 25 lines I will now have 25 each month. That just seems like it'll get excessive real quick...
SO; if there are any great ideas for setting up this quota table vs what I am currently using I would really like to know!
Thanks!!
View 14 Replies
View Related
Jun 10, 2005
any ideas on how I go about this:
I want to keep track of projects and people on projects but most of all money that people will get.
1 project my have many individuals on it
1 individual may be in many projects
so i guess i need 2 tables with a junction table linking them both up (is that right?)
thats the easy bit....
On each project there will be a deposit (e.g. Project A is left with deposit of £100). i need to work out firstly 20% of that deposit and then split that 20% between the individuals who are involved with the project. (e.g deposit for Project A is £100, so £20 gets split between the individulas who are assigned to that project).
So I want my database to firstly look to see how many indviduals are assigned to a project and then split the money accordingly.
firstly where do i record the individual split amounts and how do i work it out?
to make things a bit more complicated i also need another table with expense claims. this i guess is a One to Many relationship with the individual table. but what i want to do is be able to record the total balance between expenses claimed and the deposits splits mentioned earlier.
all this and it needs to be in a form that makes it easy to input the data.
i have the general layout in my head just cant work out how to go about linking the tables up and how to do the calculations
thanks in advance for any help
KTJ
View 2 Replies
View Related
Mar 20, 2008
Alright, so here's my layout. I'm working on creating an inventory database with forms to enter new data and edit that data.
I already have a table for the new data, and a form to enter the new data into that table. Each new entry has it's own unique ID.
Now here's the tricky part.
What I would like to do is this:
Part 1
I want to create a sub-table of any EDITED entries. Each of these entries would be tied to it's original entry but it would have it's own unique ID number. So, for example, if Part 1 comes in, it would get an ID number like 134. Each time Part 1 is edited, those new entries would get ID numbers like 134 A, 134 B, 134 C, etc.
Part 2
I want to create a form based on a query that will search based upon part number, and once the entry is found that needs to be edited, it will do one of two things. If there are no edited entries yet, it will use the original entry as a base and create an edited entry based upon it. OR, if there IS an edited entry, it will pull up the most recently edited entry (by date) and use THAT as a base for another new edited entry.
It would then save the NEW edited entry to the Edited Entries table.
So, if Part 1 hasn't ever been edited, the form would pull up Part 1 and store any edits to it in the Edited Entries table with ID 134 A. Now, if Part 1 was edited AGAIN, it would pull up the edited entry 134 A instead of the original. It would then save any changes to that entry as 134 B.
I know what I want to do, and I'm pretty sure it can be done--I'm just at a loss as to how to structure it.
View 3 Replies
View Related
Sep 12, 2006
Hi All
I am having trouble setting up a table to contain this information and then putting it into a query and a report.
Members (many names)
Race Class (a member could be in 1 or more class)
Annual Membership (1 per year)
Insurance (1 per year)
My report I want to look something like this.
Race Class____Member Name____Current Membership Y/N___Current Insurance Y/N
Motoard:______Billy Brown_______________Y______________________Y
_____________Tommy Turtle______________Y______________________N
Road Racer:___Billy Brown_______________Y______________________Y
_____________Fred Bloggs_______________N______________________Y
Any suggestions would be really great.
Many thanks Tee
View 1 Replies
View Related
Jul 25, 2005
I am trying to build a very simple but effective database that I can use to monitor who is working where within my factory.
I have set up a series of tables have linked them up the best way I know how, I dare say I have done this wrong but I have done it anyway (open to suggestions on improvements).
What I am trying to do ultimately is to be able to calculate how many people I have in each area etc… to calculate if I have any vacancies…
The set up is as follows:
Each employee has a job role
Each employee has a shift
There are three shifts associated with each Production line
Production lines are based in a specific Dept
Dept are based in specific areas
The only things that will really change is the employee, they may get moved from one role, line, dept etc…
As I said I have put some tables together, but am not sure If I need another table to store all the info for each person, by this I mean…..
Do I need to create a table to store each persons info, dept, area, line role etc…
I want to have a form that I can enter each populate, which will let me make the relevant links between people, lines etc…
Can anyone help me with this please…….if someone would prefer to do this as a freelance project I am happy to listen to offers
Cheers
Andy
View 1 Replies
View Related
Jun 25, 2006
The system is about a shop which gives credit facilities to its customers and so has to keep track of payments effected by them.
I have 3 tables: Customer, Payment, Balance. The main table is Customer, with primary key CustCode which is an autonumber. It keeps personal details of the customers. The Payment table keeps details of payments effected by the customers and the Balance table keeps the overall balance for each customer. The relationships are as follows:
Customer to Payment, 1-to-many
Customer to Balance, 1-to-1
Fields in Payment table: CustCode, PaymentDate, AmtPaid
Fields in Balance table: CustCode, Balance
Problem 1
I have a data entry form for Customer. When I create a new customer record, I want a new record automatically created as well for the customer in the Balance table. How can I do that?
Problem 2
I need to produce a report on customers who have not made any payments during the previous month. I’d like to know how to do that as well.
I hope I’ve made myself clear enough. Thank you for all help.
View 5 Replies
View Related
Feb 20, 2008
Can I setup a table to have 365 columns for dates &days of the week for the entire year? I would like to relate this table to another to show how material was delivered over the course of the year, including those days and weeks when material was not delivered to all my storerooms:confused:
View 4 Replies
View Related
Oct 11, 2004
When I open one of my tables or queries and look at the customer Id, the displayed info is a single digit. On my customers table my primary key is the customer Id with auto number with the format like this: "ID"000. This is great however, when you look up the customer id in other tables it only displayes the single digit. I want it to show up like ID001 or ID002. In the customers tables it looks just like that, but if you click in that cell/field it takes away the ID and just shows the 001 or 002. I changed the format in my other tables under customer id to "ID000" but that didn't seem to make a difference. I would like to be able to do a search lets say by customer ID in one of my products table and when I type ID005 or ID012 it takes me to those records. But right now I would have to enter 5 or 12. Datatype in my other tables on field customer ID is set to text. Do I have to do a validation rule? How can I get it to show up the way I want it?
View 7 Replies
View Related
Nov 7, 2007
Hello,
ACCOUNT_NUMBER
SHORT_ACCOUNT_TITLE
CONTACT_COMMENTS
CONTACT_TYPE_TEXT
ENTERED_BY
INITIAL_CONTACT_DATE
DATE_ENTERED
These are fields that are imported from Excel. I have to import 500-1500 rows each week which contain the data I need. Then I must, by using a form, go through these "Service Calls" and mark some that give the employee credit for the call and some that are unique calls. Meaning if they have serviced the same person 5x in the quarter I can only count that once.
There can be muliple people on a service call so I have these:
EMPLOYEE1
EMPLOYEE2
EMPLOYEE3
(Entered_By field contains the employee who recorded the call and they would have gone on the service call)
Then I have to record who they contacted regarding the account. The SHORT_ACCOUNT_TITLE will often differ from who they have contact regarding this account. So I have this field:
CONTACT
Then since muliple employees go on service calls together some may have already contacted this customer aleady. I have to keep track of employees unique customer contacts. So I have these fields:
CREDIT1 (checkbox for employee listed in the ENTERED_BY field)
CREDIT2 (checkbox for EMPLOYEE1)
CREDIT3 (checkbox for EMPLOYEE2)
CREDIT4 (checkbox for EMPLOYEE3)
Then I have to keep track of service calls regardless if they where unique ones or not. So I have this field:
ELIGIBLE
Then I put a notes section in so when I am filtering the data and finding what calls where good calls and need to make changes or whatever I can have a place to put that:
NOTES
Finally since some of these customer calls involve large amounts of customers (group meetings, etc) I have this field to type in a number of additional credit:
ADDITIONAL_CREDIT1
ADDITIONAL_CREDIT2
ADDITIONAL_CREDIT3
ADDITIONAL_CREDIT3
------------------------------------
So since I have to import Excel rows that contain the data I start with I am unsure of how to set this up and normalize it. I have attached my form I have used to find what fields I would need to show you more of what I am trying to do.
This form allow me to filter data that shows up in the subform. Then I can just double click it and bring up the record. Then mark the ones that count.
I am looking for any suggestions. Thanks.
View 12 Replies
View Related
Nov 24, 2014
I have a form "FrmMaintenance" based on the query "QryMaintenance" that is taken information from the table "TblMaintenance".
In the form, I have to pull the serial number of a machine from a drop down list which works perfectly fine in the form and it also updates other associated records.
When I go to the table, it puts associated number instead of the serial number that I've pulled.
Same thing happening for the pull down list "Engineer".
I've tried to look at the format of the "SerialNo" and changed to text, then to Combo Box but it didn't work, and the same for "Engineer"
View 8 Replies
View Related
Sep 19, 2005
Good morning,
I am having problems figuring out how to set up my table/query structure for a series of surveys that are based on specific user groups. In a nutshell how do I set up the relationships for the Questions, Employees, and Answers if I have the following tables (simplified):
tblQuestions
--------------------
QuestionID (P)
UserGroupID
Question
tblEmployees
--------------------
EmployeeID (P)
Name
UserGroupID
tblAnswers
--------------------
AnswerID (P)
QuestionID
EmployeeID
Answer
The functionality requirements are:
1. Create a set of questions for two or more user groups (each set contain different questions).
2. Assign Employees to a specific user group.
3. Employees are able to enter the DB to answer the questions within their user group.
What should happen is the employee accesses their set of usergroup questions and enter the corresponding answers. Each time I try to set up the relationships however, the answer field ends up being uneditable.
Any help you can give would save what little hair I have left.
Thanks!
Bob
View 2 Replies
View Related
Apr 22, 2013
I have a database that I use to put in orders for our shop and keep track of our part informations as well as paint and packing materials. Everything works good on this but I am trying to create a table for adjusting quantities on hand for packing material based on the part quantities and for adjusting paint in stock based on information given to me after the job is run.
There is already a relationship between parts and packing material as well as parts and paint. When I put an order in I would like to have it adjust out that many packing materials that are related to that part. Once a job is run I need to be able to adjust out the amount of paint used.
For the paint side of this I want it to track the paint used by order, we are trying to get a grasp on how much paint we are using for parts so it is important for me to know how much and when.
I am thinking I need a table that connects paint to orders and has quantities in it, then create a query and do the calculations from there... I do not know how to accomplish that but it sounds like it could be right...
View 1 Replies
View Related
Nov 5, 2013
I want a List Box on a Form that takes its available drop-down values from tblDiscipline and puts the value selected into the Discipline Field of tblEquipment.
The problem is I set the Control Source of the List Box to tblDiscipline (so the List Box can use these values for the drop-down selections) and when I make a selection it tries to put it back into tblDiscipline instead of tblEquipment, because I can't find anywhere to set the table that the result should be Bound to.
How can I set up a control, such as a List Box, so that it gets its values from one table but returns them to another?
View 1 Replies
View Related
Nov 26, 2006
Hey guys, i have an access project for my computer class, and considering i dont know much about access, i think i did ok, but i want to make sure everything is the way it is suppose to and do it myself than paying someone else to do it for me.(its some pretty interesting stuff actually) so can you gurus, let me know if what the email from my teacher said is what i have done? i think its just the relationships that might not be done right if anything. i attached the pdf file that says what i must do, and the access file that i made. thanks for all your help in advance guys,
Quote: Originally Posted by Kush's Prof there is nothing to make the relationships work.
you are simply confusing the operational aspect of the database adn the design aspect of it.
When you program an interface to use it then you worry about usade of the relationships.
Setting them up at the design level where you are now, will simply ensure that the relationship is not violated when it is programmed. That is it.
Access Project (rared)
View 14 Replies
View Related
Mar 7, 2005
Trying to set up a database in access to be used for customers to track payment history problems. how does this sound???? ...
Table 1 would have fields: account # ; account name; region
Table 2 (would need a separate table? ) With fields: account #; date entered; entered by; Comments:
I Need to be able to enter multiple entries for payment history comments as this will be info entered by date to track
So data would look like:
Account #: 111-11111 Account name: test company Region: new york
Entry date: 3/5/05 Entered by: Joe Shmoe Comments: Company short paid, sent letter 3/5/05 requesting additional $$
Entry date 3/10/05 Entered by: Sue Smith Comments: Sales office says there is a problem with the bill, extend grace period.
I can’t put this in one table right?
I will have many entry dates & comments for an account that I would like to print out in a report.
Wouldn’t I need 2 tables with a relationship linked by account #?
Thanks... I will be around the rest of the day to respond to any commments..
please help me with setting this up..
View 9 Replies
View Related
Nov 13, 2007
Hi
I am trying to get a blank ribbon to be in place when a user opens my application. I have an Adp linked to SQL Server. If I create a table in SQL Server named ribbons and have the app read the data in that table in the AutoExec macro (this runs the code that reads the data) then assign the new ribbon to Application.LoadCustomUI RS("RibbonName").Value, RS("RibbonXml").Value it works and the blank ribbon is displayed. The problem is that this is no use because if the adp is not connected to the database on startup it causes an error as no connection has been established.
Instead I have put exactly the same xml file into a module but this does not have the same affect even though I call the same Application.LoadCustomUI method. I havent really done anything different except change how the xml is found. The problem with this method is it just pops the new ribbon into the list of available ribbons so then the user would have to choose the ribbon, shut down and restart, that is no use.
Has anybody got any idea what can be done about this? I will post the code below.
Thank you for any advice on what can be done.
Paul
First method (which works but is no good for distributing the app)
Autoexec calls the following
Public Function LoadRibbons()
Dim RS As Recordset
Set RS = CurrentProject.Connection.Execute("SELECT Ribbons.RibbonName, Ribbons.RibbonXML FROM RIBBONS")
If Not RS.BOF Then
Application.LoadCustomUI RS("RibbonName").Value, RS("RibbonXml").Value
End If
RS.Close
Set RS = Nothing
End Function
but using the following and calling it in autoexec just puts the ribbon in the list and doesnt use it
Function CreateRibbon()
Dim xml As String
xml = _
"<customUI xmlns=""http://schemas.microsoft.com/office/2006/01/customui"">" & vbCrLf & _
" <ribbon startFromScratch=""true"">" & vbCrLf & _
" <officeMenu>" & vbCrLf & _
" <button idMso=""FileCompactAndRepairDatabase"" visible=""false""/>" & vbCrLf & _
" <button idMso=""FileOpenDatabase"" visible=""false""/>" & vbCrLf & _
" <button idMso=""FileNewDatabase"" visible=""false""/>" & vbCrLf & _
" <splitButton idMso=""FileSaveAsMenuAccess"" visible=""false""/>" & vbCrLf & _
" </officeMenu>" & vbCrLf & _
" </ribbon>" & vbCrLf & _
"</customUI>"
Application.LoadCustomUI "BlankRibbon", xml
End Function
View 1 Replies
View Related
Jul 2, 2005
Hi guys,
I'm trying to create a simlpe database that is use to store time worked for clients (see attached).
If you run the form frmClientEntry i have two issues.
1 - you will see that the title is automatically selected from the combo box. However is i try and enter the form I'm told 'You cannot add or change a record because a related record is required in table 'tblTitle'. If I select one of the entries I get rid of this. I would like the default to be selected...
2 - There is no entry in the hourly rate column field even though there is an entry in the table. How do I get my hourly rate to show? Ideally this will only be a single text box and not a comboBox as the hourly rate will be the same for every client.
If this is a table design issue please let me know and I'll post there.
Thanks
View 11 Replies
View Related
Sep 1, 2006
I have a query based continuous form that looks up a serial number entered by the user and allows them to view the matching data.
When a user enters a serial number that is not in the database I want a message box to pop up to warn them there is no match.
The code I am using makes the msgbox pop up on every entry. I have tried to put this code in a variety of form and control events with no luck. Can someone tell me what I am doing wrong?
Private Sub Form_AfterUpdate()
Dim intRespone As Integer
If DCount("[packing station scan].[serial_number]", "match with last test results", _
"[drive test results].[serial_number] = ' & Forms![match with last test results].[Serial_Number] & '") < 1 _
Then
intresponse = MsgBox("Stop! Serial Number has no test data!", vbYesNo, "No data found")
Select Case intresponse
Case vbYes
DoCmd.Requery
Case vbNo
DoCmd.Requery
End Select
End If
End Sub
Any help is greatly appreciated!
Regards,
Kerry
View 5 Replies
View Related
Oct 1, 2004
I hope someone else has had this problem and can help with this.
I recently gave a user an update to the DB I developed for them. It is a split DB. He has been testing the New Interface DB against a copy of the Orginal Data DB (like our standard process entails). In the process of his testing when he tried running a process that had an Update SQl statement the following error was generated
"Function is not available in expression in table - level validations expression"
The error message only affect this ONE linked table (all other tables are fine) and I have no problem running the statement against the Actual table in the Data DB. It only booms out when ran in the Interface. I checked the rights and nothing is out a place. I can't change any data in this linked table, but can change anything I want in the actual table.
In the process of trying to troubleshoot this problem I stumbled across a equally baffling problem. None of the queries that use the built in functions work. They all error out, as if a library was missing. I checked the libraries and the are all present (none are missing).
The Orginal program, which uses the same libaries is stilling working fine, so I don't believe the actual dlls got corrupted. The program works fines for at my location, and appears to work fine everywhere else they have tested. They are going to test it on a few more workstations, but I anticipate the same results, it will work.
Suggestions??
S-
View 3 Replies
View Related
Jun 23, 2005
I'm creating a data access page that can be used over the internet to add information into the database. The database is password protected. The functionality currently is that when a user accesses the data access page, they need the database password in order to submit information. We don't have a small number of users and giving all these people the password to the database would comprimise the security of the system.
I am looking for a functionality such that the people that edit the database, either by the front end application or by opening the database directly to be password protected. But the data access page not to be.
Any ideas on how I can achieve this functionality?
View 8 Replies
View Related
Aug 24, 2006
I have a database for an event with 2 main tables.
Parents and Children of the parents with a one to many relationship.
I need to be able add the table number that each parent and child will be at (not the same table as parent). I could add another field for the table to each database but not all the entries are attending the event.
Could someone point me in the right direction as to the best way to design this.
WarrenB
View 1 Replies
View Related
