Correct Relationship Setup?
Jun 14, 2006Hi all..
I have a simple database setup...
When I do a test query on the tblMain.. all I get are numbers.. How do I change the numbers to text?
Thanks again
R~
ADVERTISEMENT
Tables :: Correct Table Relationship?
Nov 13, 2013I am in the beginning of setting up a database and have NOT messed with multiple tables. I just want to make sure that I am setting up the tables as well as the relationships correctly.Here is what I currently have, 4 tables and they are named. Employee, phone, radio and spotter. Employee table is where my primary key is located and I currently have a 1-to-many relationship between Employee and the others.
What I want to be able to do is to store information in the multiple tables from 1 form, which I don't think will be an issue and then at a later date be able to pull up information. EX.Thus being able to skip anything being put into the spotter table. Then at a later date be able to pull up only the select information I originally put it?
Subform Correct Answer Adds To Number Correct In Main Form
Mar 2, 2012The code I have is.
Code:
Private Sub Command26_Click()
If Forms![test site]![prp test].Form.[A Right Answer] = -1 Then
Forms![test site]![number correct] = Forms![test site]![number correct] + 1
End If
DoCmd.FindNext
End Sub
Then when clicked it checks a yes/no box to see if "A right Answer" is the correct yes. Then it should pop to the main form and take the number correct cell and add one to it. I am trying to get the record to go to the next record inside the sub-form but docmd.findnext seems to be wrong too.
Need Some Help With Setup
Mar 7, 2005Trying 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..
Password Setup
Jun 23, 2005I'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?
Help With Database Setup
Aug 24, 2006I 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
Advice On Setup
Jul 23, 2007I have a database that contains information on football games. There are many
different games from many different seasons, teams, opponents.
I will eventually have a fairly large number of different reports and forms
that will want to be viewed but filtered by the above three catagories.
Usually all of the reports at any given period of time while running the
reports will want to be viewed off the same criteria as the one before. (i
don't think you would want to have to fill out the parameter manually for
each report or you will be entering redundent data as the parameter report
after report.)
I was thinking of having a form with 3 multi select list boxes, [seasonlst]
, [teamlst] , [opponentlst] and having each form filter information based of
the values entered in each box.
Is this the best way to do it or is there a better way?
Table Setup
Sep 7, 2005I 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.
Table Setup
Feb 3, 2006I'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.
Table Setup
Mar 29, 2006HiNew 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 RelatedHelp With Table Setup
Dec 27, 2007Hello, 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 RelatedTable Setup Help...
Mar 17, 2008I'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!!
Table Setup
Jun 10, 2005any 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
Best Setup For Custom Week?
Nov 14, 2005Hi
I have a client whose working week runs from 0700h on Monday. They do a lot of overnight stuff and so have this cutoff. I am doing a time sheet db for them. All the time sheet records have a starting date and time, and an ending date and time. The end date defaults to the start date but can be updated if an overnight shift. I separated the date and time since I remember issues using the general date format.
I am puzzling over how best to organise my queries so that I can pull up weekly reports that tie in with their week criteria.
Do I build my own table with the date for Mondays over the next few years or is there some smarter way to identify Mondays and then say I want everything between 0700h on that Monday and 0659h the following Monday?
Any thoughts or pointers would be most appreciated.
Thanks and best wishes
Hardcoding Page Setup
Mar 28, 2007Hi all,
I opened my database on a newly installed access programme (same version) to notice that the design control page settings had reverted to default for all my forms and reports. I need them to be A4 and not letter when it is opened on any access system. How could I go about doing this? In Code?
Cheers
Tania
Appointment Setup (Calendar)
Jan 11, 2008Hello,
I have a problem to solve using ACCESS 2007. I have an 'appointments' table and a calendar. So far as I click on any date on the calendar it is supposed to show me the appointments for that specific date on the form (this info is gotten through the 'appointments' table which has the date for each appointment). Appointments have to be separated by a 20 minute difference (8:00am, 8:20am, 8:40am, and so on....). I have to have about 30 spaces or appointments to be filled up per day.
So overall, the customer wants to have 30 spaces (already filled up with the times) so he could fill out the rest of the appointments info in each needed space as he sets up appointments.
Do I have to create 30 records for each day? How would I do this? What happens with the paces (records) that are not filled? Wouldn't that be a record that is not used (wasted record in the table) ? Is there a more efficient way to do this?
Thank you very much everybody.
Table/Form Setup
Mar 20, 2008Alright, 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.
Help With Table/report Setup Please
Sep 12, 2006Hi 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
Is My Table Setup Correctly...how Would You Do It?
Dec 1, 2006Hello,
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.
Comparing 2 Lists - How To Setup
Jun 14, 2007I would really appreciate if someone could help me with the following.
I have one list (3 million records)
Table = DNC
Field 1 - Area code
Field 2 - Phone number
I have another list that has say 1000 records
Table = Phone list
field 1 - area code
field 2 - phone number
What type of relationship do I need to setup in order for me to see which records in the Phone list table are not in the DNC table?
thanks for any help
Josh
Page Setup In Report
Jun 1, 2005Hi
I have created some reports but find difficult to keep permanganate page set up for that reports. I go to design view and do page setup with required margin and Portrait or Landscape set up and preview that reports looks fine. After saving again I check still its fine but once I close that database and reopen again some of reports page setup becomes Portrait with 1" margin on all sides (Not All reports). I cannot solve this. Can any one help??
Funny part is even I copy that report and change data source of that report it changes page setup
:confused:
Form To Table Setup
Jul 25, 2005I 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
Learning About An Unfamiliar Access Setup?
Aug 8, 2006I've just been given the task of "fixing" a too-big Access database. It's actually about 250 tables in 29 .mdb files scattered over a filesystem. But I'm very green at Access (although I'm pretty OK at SQL in general). So here's my question: how the heck would you all explore/learn about this Access setup? Is there an automated way to cruise through all the .mdb files, get lists of all the tables, and even lists of field names? What about getting a list of the type & relationship of each field?I know I can get some of this by opening a file, right-clicking on a table, and selecting "Design View." But doing this 250 times sounds painful, and the properties window cannot be printed, anyway.Any tips about this?-Tony
View 4 Replies View RelatedDatabase Setup: Use Access Or Excel
Sep 5, 2006I have just begun learning MS Access and have some information already in an excel spreadsheet that is getting too big and I am trying to decide if converting the whole thing would be beneficial or jsut a waste of time.
The issue is the current excel file is a workbook of about 150 separate worksheets that are all hyperlinked to each other. It does everything I want, but I feel like it is getting to big and cumbersome and really all I want is to add one sheet/record and have all the totals/averages/"reports" update themselves. That is why I think Access might be better. Currently it takes a good minute to update the file when saving. The Excel file is about 13mb.
The data is basically all times from a mens & womens cross country team. I have one form I take to each meet and record mile splits, places, final time, etc. Then I return and currently put those times into excel. I have 4 years of data for each runner. I also have 10 years of team data that needs to be able to be recalled usefully. My spreadsheet also looks through all past meets and keeps a list of the top 50 times for both men and women. And compiles every practice/workouts for the past 8 years, etc. Plus a lot of other sheets/forms/reports/.
I started a database and have one table for Athletes, one for MeetEntry. I also created a report for the meet entry to send to media. I then started working with relationships between different tables and became overwhelmed with how to best set up the entire database. I came up with a list of things and organized them into what I thought would be best suited for Tables vs. Forms vs. Queries vs. Reports. With the limited info, any ideas on setup would be appreciated. Maybe I just leave it in Excel and forget Access.
I think with the complexity, this is much better suited to my needs but it may be just a bit over my head. I do not know an VB so that is also an issue.
Suggestions
How To Create A MS Windows Setup Package
Aug 13, 2007Hi All,
I would like to make a setup package, which is setup.exe to install my MS Access and help file. However, I don't know how to create the setup.exe.
Anyone can kindly suggest or guide me?!!
Thanks a lots!!!
Victor
Table Setup For Every Date In Year
Feb 20, 2008Can 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