Guidence/advice For Tables
Apr 9, 2006
Hi,
I am trying to create a database but I am a little stuck, could do with your input.
Here is the table structure so far:
member_tbl
id AutoNo Pk
memberid speacial number given to each member
title fk - linked to title table ?
fname
mname
lname
addid fk - linked to a address table ?
datejoined
phone
mobile
email
dob
role fk - linked to role table
address_tbl
id pk
house no/name
streetadd1
streetadd2
area
postcode
town/city
postcode
address_r_tbl
id pk
addid pk
role_tbl
id pk
name
role_r_tble
rid pk
mid pk
sdate
edate
family_tbl
fid pk
phone
date joined
email
default contact - not sure if this should be here or fmembers_tbl.
fmembers_tbl
mid pk fk - using info from members table ?
default contact - not sure if this should be here or family_tbl.
My issues are this
title - Should this be a separate table.
address - should this be a separate table.
role - not sure how to set the id field for this, I have read that autonumber for a small number of items in the table is not worth it.
Would be greatful for your input.
View Replies
ADVERTISEMENT
Mar 23, 2005
I have an existing Members Database Table fully populated with over 1,000 companies detailed. Within the database each company has a nominated person, normally the M.D. who has access rights to the member’s area of a website. We would now like to give the companies’ employee’s access as well. I would like to create a Parent / Child relationship between the two. I have a yes/no check box with the main table which signifies whether or not the company is still in membership. If the company was to cease membership by un-checking the box, access is denied to the employer and the employees. The employees table would only need to consist of First Name, Last Name, email address and password, and I presume a ID field to link the relationship.
Have I got that right or is there a better way?
I must admit, I'm confused as to the differences between Queries and Relationships, is that not the same thing?
Regards
Alan
View 1 Replies
View Related
Nov 14, 2007
Hi there.
I just wanted to ask peoples opinion on something.
In the database I'm working on I have to create two new tables that will be joined to the Main table. each of the new tables has only one field (as well as the FK) with a one to many relationship with the Main table as there will up to 4 records per main record. Note, the fields will only be set at 15 text characters.
I'm trying to decide if it's better to only make one table and have the two fields together even though one might have 3 records, another might have 2 records and so on (of course with the same FK), making some blank space in the opposite.
Normally I wouldn't do this, however, it's a table with only two fields.
Or would it be better just to keep the two fields within seperate tables?
Also, I'll be developing a form with the two fields together, linked to the main form (This is also because there isn't enough room within the Main form).
Each will be in datasheet view.
Thanks in advance for any advice.
View 2 Replies
View Related
May 12, 2005
Im in the process of building a database for a friends business, and im a bit of a newbie with access.
Id like to get some opinions on structure and overall how i should build the Database.
My goal is to have two types of clients ... donors and buyers.
A client can be both a donor, a buyer or both.
When a client is a donor, they get a certain amount of credits added to their account.
When a client is a buyer, they will be purchasing those credits from a donor.
heres an example of what i want to accomplish;
John smith donates 500 credits; I enter John Smiths info and credits into his profile;
Jim Doe buys 100 of John Smiths credits; I want the DB to automatically update Mr. Smiths Credits, and then add 100 credits to Jim Does profile.
Also, I want John Smith to be able to purchase credits from Jane Johnson, and again, have the credits added to John Smith and deducted from Jane Johnson automatically.
Get my meaning here?
The tables will also contain the typical client info ...ie; Name, Address, Phone, SSN etc...
Can i/Should i do a seperate table just for credits and link it to the client tables? Should i create seperate tables for Buyers and Donors?
Also, I have an excel spreadsheet with formulas to do credits already, but when i tried to import it into a table in access, it didnt work so well.
Any opinions on table structure, design etc would be greatly appreciated
Thanks all for lookin in
View 3 Replies
View Related
Jun 4, 2005
Hello to all,
i have a non-windows application and i would like to create a vb program to print invoices.
I would like to send to this program a txt file with all the values (qty, vat, customer name etc with vertical & horizontal positions in the form etc..) and then superpose all i need to print with an image (gif or jpg wich is the my customer invoice presentation.
In fact i have 2 layers , one with all the value i print and another with the invoice image background.
I'm a beginer with VB, so i need advices to create this program, maybe someone did this already.
Thx in advance
VINCENT
View 2 Replies
View Related
Sep 3, 2005
Hi all,
Im fairly new to access and im having trouble constructing a stock control system that can create sales orders and adjust stock levels accordingly, hold customer details linked to sales orders. Ive spent about 20 hours trying to do this and its just pickled my brain, ive searched everywhere but sometimes im uncertain what exactly it is im looking for. Can anyone give me some pointers?
I have 7 tables at the mo but its 4 of the tables i need for the sales order:
tblcustomerdetails
customerID,first name, last name (general customer details)
tblorder
orderID, customerID, delilvery address fields..., subtotal, total, delivery
tblorderdetails
orderID, productID, productname, description, listprice, quantity, discount, linetotal
tblproducts
productID, catagoryname, productname, description, costprice, listprice, profitmargin, suppliername, instock, reorderlevel, quantityperunit.
what im trying to do at the minute is contruct a subform for a form that would require entering the products into through a combo box selected by productname and then autofill the product description and listprice. Ive ended up deleting all my forms and queries because nothing seemed to work right. I will then add this sub form to a form containing all the customer information and the total price for the subform this then needs to be output to a report for printing, but i can figure that out later. Ive attached my database if anyone wants a look if you dont understand my jibberish.
thanks
View 6 Replies
View Related
Oct 29, 2005
Hi all
First post.
I was wondering if this could be done in Access. Let me explain
I work at a candies manufacturer in Puerto Rico. Right now we are not tracking any kind of inventory. Is it possible to efficiently track our kind of inventory ( raw materials, work in process and Finished Goods) in Access?? Maybe using a bar code system??
Is it possible??
Please advice
Thank you
View 2 Replies
View Related
Nov 2, 2005
Hi
I would like some advice or opinions from people who have worked with access and mysql.
Currently we run a large database in access which holds around 3500 records. It is actually running quite slow at the moment. What would you suggest to speed it up? ive heard running it on a sql server but i dont have the info to know if this would be correct.
Also i was thinking or changing the access database and getting it fully redone in mysql why would this be more advantageous?
Also i havent any knowledge on MySql is it easy to learn for a beginner? Do you have any information such as websites i could visit to learn or sample databases? Or would it not be worth me learning it? What would you see at the front end and back end?
Sorry to bombard you with questions
I value your opinions
Thanks
View 4 Replies
View Related
Sep 14, 2006
Hi,
My client wants me to make fields from different tables on the same form which he wants to use for input. This has made it very difficult for me as my queries have to involve a lot of outer joins and in some cases full joins.
Any suggestion please?
View 14 Replies
View Related
Jul 5, 2007
Hi all,
I'm trying to set up a database, which I've done before on different programs, but I'm new to Access. I have a rather elaborate plan but am not sure it's actually possible.
I would like to set up a system that will effectively take input from the user within a record on the database. In simplest terms I'd like to set up a form on which the selection of a value for one field for a record affects the list of options available for a second field. As a basic example, say there are two fields: Input with possible values Red and Blue; Options, with possible values Red1, Red2, Blue1, Blue2. Ideally I would like to set up a form on which if Red is selected in Input, the options Blue1 and Blue2 don't appear in the Options box. Crucially you can also then select Red1 or Red2 as the value for 'Options' for that record (as opposed to just having a text box with the options written in it), as this provides the potential for a string, with the selection of a value for Options affecting another field.
Obviously in reality there will be many potential values for Options, and it won’t be obvious to the user which are compatible with each value for Input.
I wanted to use Program Flow functions with a combo box - say for the Record Source: IIf ( [Input]="red" , "red1;red2" , "blue1;blue2" ), though this would probably need to become a Switch/Case/Break command in the real database - but I don't think you can input equations into the Record Source.
I've also thought about trying to use queries, but can't see how it would work either, (the form for every record is the same, so the combo/list box for Options will always have the same properties. Switching between forms based on the value of Input seems impossible).
Then again perhaps I'm trying to make a database do something it wasn't really designed for, and should go back to basics and just display the possible options in a text box that is dependant on Input (but this way I won't be able to use the value of 'Options' in a further process).
I'd really appreciate any suggestions, especially since I'm pretty clumsy with the system still (first day using it, oh joy) and so could well be missing an obvious solution.
Thanks for your time.
Kate
View 2 Replies
View Related
Mar 8, 2008
Hi, I'm still an amateur at using Access and have just recently been introduced to normalization.
I'm looking for some advice on how to proceed with a database I'm trying to create.
I need the database to store vehicle information (name, make, model, color, license plate), along with parking information (date, time, place, who issued the notice)
My biggest question so far, is finding an efficient way to list a vehicle with what would be an undetermined number of parking slips. and then of course being able to retrieve that information on one form.
I tried using a from for VehicleInfo with a subform for ParkingInfo but I'm not getting the relationships right, the parkinginfo form is not displaying all the information connected to the license plate when the main form shows the vehicle information..
if that makes sense, any help or advice on how to proceed (or begin) would be greatly appreciated.
Thanks
View 2 Replies
View Related
Apr 9, 2008
Sorry - duplicated post.
View 2 Replies
View Related
Apr 9, 2008
I have been developing a catering order system at work. A demo version has been in test and initial issues sorted. The users are very happy with the way it works and though far from perfect it does everything they asked for and then some.
Basically, each order for refreshments/food creates a record and order number. Orders feed through to a daily 'jobs' diary sorted by date/time which the catering staff work from.
However, what they are asking for now is to be able to link some records together for collation/charging purposes. Grouping using the customer ID and the order Date doesn't work as customers could have many orders across many dates , and some of the orders by the same customer won't need to be collated together. My initial thoughts are to add a unique code to each order that needs to be linked , has anyone any ideas on this , is there an easy way to generate a code (perhaps CustID, OrderID , Date, other?) which can be added to other records to 'link' them.
I would be grateful for any suggestions.(other than a complete redesign :eek:)
Thanks
RussG
View 3 Replies
View Related
Nov 5, 2004
Greetings,
I was hoping someone could offer some advice on how I would design the following project:
Student Table
- ID
- Name
- Unit (each student belongs to one specific unit)
- License type (each student could have multiple license types)
Unit Table
- Unit Name (string)
License Table
- License Type (string)
I have created a report that dynamically updates information according to what unit the student belongs to via a drop down box, i.e. while the report is open, select a unit from a drop down, press a button to apply the filter and the report automatically updates. I want to add the same kind of functionallity to the report based off of licenses as well. My original design had all license types in the Student Table as a yes/no option. I couldn't get the filter to work properly so I moved license types to its own table (which makes more sense anyways...) But, unlike the Units Table, any One student is allowed to have many licenses so this creates a bit of a problem. If anybody has some insight on this I would much appreciate it. If you're not following, please let me know and I will try to be more specific. Thanks.
j
View 4 Replies
View Related
Jul 25, 2006
Hi. I just recently started studying Access independently since my school never taught it to me and I'm trying to design a invoice type of database as a summer project. I'm stumped on queries because the office 2000 guide I have only briefly goes over it.Basically, what i'm trying to do is create an automated value like in excel so that the "Net" column i have will subtract with the "sales" column to automatically enter a value for the "profit" column. I can't find any place for me to enter anything like [profit] = [net] - [sale]. i tried to use the input mask but since my data is in currency, it won't allow me to do it. can anyone please tell me where to start or what i've been doing wrong? thanks.btw, i'm also trying to do the same thing with the y/n feature of access. i'm also trying to find a way so that if i type y/n for a column, it will copy the value from a different colum so say i put yes on "account R" then i want the "AR$" column to copy the value from the "sale" column automatically. if i can solve this problem the same way as the previous problem then please ignore this (i THINK this can all be solved with queries.)
View 10 Replies
View Related
Feb 3, 2005
Hi everybody !
I have an assigment and have to create a database, i'm just starting to learn how to use access properly.
there's a screen of a form I made, if anyone has some advice to make look better it would be welcomed. As you can see it is very basic.
I also would like to know if it possible to create a search bar, for example typing in "sales" and the list of all the candidates working in sales comes up (I know how to do this in a query, but how do you transpose it into a form).
thanks for your help
Ilan
View 2 Replies
View Related
May 16, 2006
is there any1 who can give me tips on an exam i have 2moz on databases. its a theory and i'm crap at them. thanx loads, luff me xx
View 2 Replies
View Related
Oct 9, 2006
For now I have 20 comboboxes on my form each bound to a field from my sourcetable. Since ya can only choose 1 value in a combobox, the users want to to choose multiple values in each box. How should I implement this?
I can't use 20 listboxes because I haven't got any free space left on my form.
Can somebody giove me some good advice on this?
thanx in advance,
Johnny
View 7 Replies
View Related
Jul 12, 2005
I am creating an incident database for students at a high school. At the moment I have one table for the students with a studentID (Autonumber) that links to an incident table in a one to many relationship.
My question is as I have many different types of incidents taking place, e.g. student on report, phone call, Referal from teacher, medical incident, exclusion etc... would it be better to have a table for each type of incident or keep it as at present.
View 8 Replies
View Related
May 25, 2006
Hi there,
Being new to Access and table relationships I need advice on the table design I have so far. A jpeg image of the table relationships can be viewed at www.joyceandstevieb.com/dbasemap.htm
Do I need to include foriegn keys to counties and countries in the address tables? Or will the connection from city to county and then country suffice?
Also, could I trim down the address and contact tables to just one of each?
I don't know how I could differentiate which is customer supplier and haulier though.
Any help would be appreciated.
View 11 Replies
View Related
Apr 26, 2005
I was hoping someone could offer me some advice.
I have created an MS Access 2000 solution for a company that utilises replication and remote synchronization. The company have about 12 people working out in the field (on laptops) who use replicas of the database (held on the company's server). The solution has become quite unstable and the amount of database conflicts is growing daily.
Could anyone suggest a more robust solution for the senario described? Would MySQL or MSDE be a more stable option? Is there anything I can do to make the MS Access solution work?!!
Any advice or suggestions would be appreciated.
Kind regards.
View 1 Replies
View Related
Jun 2, 2005
I'm creating a database that keeps track of printing jobs at a printing company... I started my project by drawing out how I want the databases to be configured.
I was going through a book that was made for access 2000, but I need to create this in access 97 because that's what the company has on their computers. One of the features in Access 2000 thats not in 97 is subdatasheets...
Basically, what I want to do is for each printing job, there can be a bunch of different tasks that need to be completed and billed for. For example, on one printing job, they need to design a logo, and then they need to print it out and send samples across the globe, and then they need to create a pdf, etc. This is going to be different for each job.
What I figured I would do is create a separate table to take care of all of the different tasks that are related to each job. This table would have the primary key of the job from the main table for each individual job, and then they would be related in a one (MAIN entry) to many (tasks) relationship.
Is this correct in how I want to do that? How will I do this inside a form, I want them to enter the information in table that expands as they put more tasks in?
This might be a very simple question, I just want to know if I'm going in the right direction.
Thanks much!
View 2 Replies
View Related
Jul 5, 2005
Hi.
Your help is very appreciated.
I want to upsize large MS ACCESS(2002) app to MS SQL SERVER 2000.
[ By "large", I mean 250 queries, 78 tables, 110 forms].
The app will be used by 25 concurrent users.(therefore the need to upsize).
I have time constraints of 2 weeks to deliver.
My questions:
1. In order to finish it asap, upsizing ONLY the tables - might be a good solution. However, will it work with a workload of 25 concurrent users?(read/write).
2. If upsizing all the tables, would it be possible to upsize SOME of the queries and leave the rest untouched?
If yes, what is the process to do it?
That will save me lots of QA time (there are 250 queries).
Mind you it's not simple, since the forms need to reference Stored Procs as well as ACCESS' SQL queries.
3. In the upsizing documentation, it says that there might be situation that the query will be upsized , no errors will appear in the log BUT it won't work anymore :mad: .
Do you have any methodology for QA the upszied queries in order to ensure the system's robustness?
Thanks a lot
Roy
View 6 Replies
View Related
Jul 13, 2005
I have several tables of test scores(Test1, Test2, etc.). I want the report to contain these scores as well as their percentile ranks and descriptions. I have created a table of all the possible scores and their corresponding percentiles and descriptions(NormsTable). I have been having problems (inconsistently) with the report and I am wondering if I should change the query that it is based on to improve the report's performance.
Right now I have a query for each test with the following fields:
Test1Query:
T1Score1 (from Test1 table)
T1S1Percentile: Dlookup([Percentile],NormsTable,Test1!T1Score1=NormsTable!StandardScor e)
T1S1Description: Dlookup([Description],NormsTable,Test1!T1Score1=NormsTable!StandardScor e)
And so on for all the scores in Test1. Then there is another query of this sort for Test2 and so on. These queries form the control source for my subreports on my ScoreReport.
If I used query joins instead of Dlookup I would have to draw relationships from each score in the Test tables to the the StandardScore field in the NormsTable, which would mean making a new query for each field, essentially. Is this still preferable to Dlookup or is there another alternative I haven't thought of?
Thanks for any advice!
View 2 Replies
View Related
Aug 5, 2005
Hi Im currently building an Access aplication and Im looking for some advice.
What Id like to do is use a list box which has a list of items in it, once an item is selected and a button clicked for this information to be sent into a text box to then be stored by the database. Each new item should be added to the end of the list.
I was wondering if its actually possible and how you would go about doing it.
Thanks for your help.
View 1 Replies
View Related
Aug 23, 2005
Hi guys.
I have been told to make my database look 'sexier' but I am a bit worried that the pictures may slow it down or increase it's size unneccesarily.
Speed is an issue because we have some offices that dial in and access it that way and as such are limited to their connection speed.
So what I want to do is created a few images, link to them, and put in code that can 'turn them on'.
The question is how?
I have created a new image object. Changed it's properties to linked.
Now I need some code that reads:
If myvalue = "Yes" then
myImage.visible = true
Else
'code to delete image
End If
I am a bit worried because that means that the images will always be there on load up, so loading the form will be just as slow because the images are there.
Or maybe I can do
If myvalue = "Yes" then
'code to create image
End If
Please help.
Edit - Part two to this question is: How do I use relative links for images? I have a folder of images and I want them to work no matter where the database sits.
Edit again - Just another question: Is it possible to have Images appear and disappear depending where the mouse is?
View 3 Replies
View Related
