Hi all,
I built a database and I was wondering if anyone has any suggestions on how to normalize my DB. I think my table relations are wrong.
I'll post my DB for you people to view.
18354
Any Help will be appreciated
I have a DB and for some reason queries aren't working properly. My guess is the table relationships I made. If anyone else has an idea on changing my relationships so that all forms and queries will work to thier full potential please give my DB a look.
Any Help will be greatly Appreciated
18338
I have a table named [Brand] and several tables named [Brand@@@Models], one for each field in the Brand table.I want each field from the [Brand] table to be related to it's specific Model table. I thought like that so I don't have a Brand#1 related to Brand#2 models, etc.. I didn't want to have all the models for all the brands in a single table, was I right to think like this?
View 6 Replies View RelatedI will try and be as concise as I can...
I am trying to create a database for our organization that will keep track of our "Gift Fund". I have a table with all of our personnel that includes all of their basic info with a customer ID field as the PK. I then have a table that is for all of the transactions. Deposits, withdrawls, description, date, customer ID to relate the two tables and transaction ID as the PK.
So we have a table with our personnel and a table to record transactions. Transactions occur anytime we put money in or take money out as well as when one of our personnel becomes a member of this fund by paying their "dues".
I have created a form to update our personnel table. It works fine. I also have a form to enter basic transactions. It also works fine. My problem lies where I try to make a form that will add a member to this fund (which in the personnel table is a yes/no checkbox) at the same time entering this as a transaction. Ideally the form would have the user pick personnel from a drop down list, click the yes/no checkbox making them a member and then entering their "dues" as a transaction.
Ultimately I would like to be able present this info in several dif reports showing all the silly statistics that boss' love to see.
Any help would be appreciated and if I have left out any key info, let me knw and I will be more specific.
first thing's first... i've never set up a Access db. I have, however, worked with php/mysql so I understand dbs and the basic principles behind them.
i was hoping you could help me set up my db to accomplish my goals now and help me in the future when i want to make this system more advanced.
the purpose of the database is to track the 'stages' of several different projects as well as keep account of which contacts are involved.
example
I have projects A, B, C
I have contacts I, II, III
the stages of the projects are 1, 2, 3
I already have these tables (projects, contaces, stages)
the first issue is, i dont know how to assign mutltiple contacts for specific projects
i.e. project A involves I, II, and III while project B only involves I and III. I would like a form to have a listbox with all the contacts in it and i could hold ctrl and select mutiple contacts for a specific project
the second issue... i would like to db to store dates of when a specific project moves from stage 1 to stage 2 for instance. i know i can set up fields in the projects table for these, but what happens when i add a new stage? will i need to go into the projects table and add a new field? can i make this dynamic?
i hope this makes sense to you. if you could give me direction, i would greatly appreciate it. before doing something that i will regret later, i hope you can give me some tips based on your experiences.
thanks in advance.
Hello,
I am fishing for advice on what systems people would recommend...
Currently, our system breaks down like this:
We use Peach Tree (old version - sucks) for accounting (this is where my question will kick in)
We use a rudimentary database I built for contact/prospect management (I intend for the above to become much more advanced as I learn, something more than "rudimentary as soon as possible)
We are incorporating a new phone system that will allow us to track may things and incorporate into our database (as far as area code reports, things like that as to track advertising effectiveness in specific areas
My question is this:
To eventually get to the point of having a fairly seamless infrastructure, what accounting software would you all recommend? Being able to integrate with an access interface is vital. We will likely get to the point soon where we hire someone to build a database so as my responsibilities will shift to more of a maintenance role, with support, rather than design...
This is a company that is built on paper, not a huge one yet, but decent size, and we want electronic info to increase efficiency...
I am looking into suggestions for a 20 - 40 person company. Currently, I am starting to look into MAS 90, and would love to get some insight.
Thanks!!!
Hi,
I've split a database and the backend relationships are still intact but the front end they are not and it looks like this is causing a problem. Is this usual?
How do I make the reports show in relations?
I'm (as said before) totally new to everything that has to do with programming, and therefore I work in the visual way, and thus I need to have the reports shown in relations in order to work with it...
Any ideas?
After designing the database that mean after creating all the tables and when pressed on relations buttuon it shows a diagram with relations established. Is this a kind of ERD. in this it shows relations between entities so can we call that an ERD?
View 3 Replies View RelatedHi there,
I'm a bit stuck on this but it should be pretty easy.
Say I have a table secondHandCar - this contains infromation on the makeID (eg 123), price, number of doors. Now each car can have a number of images, this number isn't set so I have an images table...
tbl_secondHandCar
makeID - key
price
number of doors
tbl_images
makeID - key
Image (ole, binary whatever)
Now clearly I have a problem: the images table is going to have many makeIDs with different images. I've just got stuck here - do I need an image id or something? But then my make ID will still repeat in tbl_images.
Thanks for the help :rolleyes:
Hi,
I'm trying to build a mdb which holds customer details, which insurance they have, who sold them this and which Insurance company provided this.
The relations I have to make are driving me nuts !
When I try to enter a new customer in a Form, there is a advisor added, the package the customer wants is added. But this shouldn't happen for the advisor and the package allready exists !
Who can help me with the proper relations ?
Attached is screendump of the current relations.
Thanks a lot !
Hi,
I am creating some table for a database I want to use.
I had the following tables:
[Organisation]
orgOrganisationID (primary key)
orgName
orgPostalcode
orgStreet
orgCity
orgCountry
[Contacts]
coContactID (primary key)
coSurename
coFirstname
coFysicPostalcode
coFysicStreet
coFysicAdditions
coPostPostalcode
coPostStreet
coPostAdditions
coTel
coFax
coEmail
[Postalcodes]
poPostalcode (primary key)
poPostalCity
poPostalCountry
I have two postalcodes in the Contacts table. I can't create two one-on-many relations from the Postalcodecodes table to the Contacts table.
So I thougt I split up the contacts table and create a one-on-one relationship between the two new tables.
So it would look like this:
ContactsGeneral
cgGeneralContactID (primary key, autonumbering)
cgSurename
cgFirstname
cgFysicPostalcode
cgFysicStreet
cgFysicAdditions
cgTel
cgFax
cgEmail
ContactsPost
cpPostContactsID (primary key, numeric)
cpPostPostalcode
cpPostStreet
cpPostAdditions
I would create a one-on-one relationship between coGeneralContactID and coPostContactsID. And now I can create my one-on-many relationships between from the Postalcodes table.
1) Is this clever?
2) Should both these fields have a key?
3) I set coGeneralContactID to autonumbering and coPostContactsID to numeric. is this also correct.
4) I also use prefixes before the fieldnames 'cp-' for 'ContactsPost' is this naming convention ok? I think it makes programming easier. But are there condingstandards for that in Access/VBA?
Thanks,
Jayke
Access user,
:confused:
I am looking how to generate a relation in one database between records of the same field type.
f.i. you have a database where you try to keep track of your parts.
You order a part1 but they deliver an identical part but from another brand so it has another part number.
You will store it under his correct partnumber being part2.
Due to the fact you still have stock of part1 and just recieved part2, now you will run out of stock when part1 + part2 reach zero stock.
In order to be able to monitor this you must be able to generate a relation between part1 and part2 showing that that parts are fully interchangeble.
How can you realise this in access?? Or is this impossible?
Also would it be possible to have for a certain part number multiple relations?
f.i. parts are fully interchangeble
restricted interchangeable
one way interchangeable
Thanks in advance
Can anyone shed light on why my Database seems to have stopped working so far as I've a combo box on a main form, which provides one half of a PK/FK relationship. The subform displays one or more records for this value. However, I've just entered the same 'value' (string) for an entry in the combo box as another, which shouldn't display any records in the subform yet - but it displays the records of the first combo 'entry'. This important thing is that the key's are all unique (I've doubled checked this), so why does it seem to not work?
Please.
I have 3 tables Metrics, FixedExpenses, Variable Expenses, I want them all to have a date in a form, and a subform for all the records to be added. I want to be able to do this without having 3 seperate date tables linked to the other three tables. I am not sure how to do this correctly. (I want to be able to see all the records for a certain day in these forms.)
View 1 Replies View RelatedSuppose I receive boxes and store them in a room. Each box can contain items that are either shaped as cubes or spheres and I have to allow for the addition of more shapes in the future, but only one shape can be in each box. Each box contains a random number of shapes and two boxes may, or may not, contain the same number of shapes as another.Cubes can be either Red or Green, Spheres can be either Blue or Yellow.
I wish to set, and have the ability to change, how many cubes and how many spheres, on a percentage basis, I wish to have (e.g., 60% Cubes and 40% spheres).I also wish to set, and have the ability to change, what percentage of cubes I want to be Red and Green and which percentage of Spheres I wish to be Blue and Yellow (e.g., Cubes -Red 70%, Yellow 30% )
e.g. -- I have several boxes totaling:
Cubes
Red 30
Green 40
Spheres
Blue 10
Yellow 20
My goal is to produce three reports.The first report would provide the Target and Actual number of Cubes and Spheres expressed as a Value and %. The #'s would simply be calculated from my desired % . So, if I wanted to have 60% cubes and I had 100 Shapes, my target # would be 60. If I had 200 Shapes it would be 120. (i.e., The actual number of shapes I have is simply a factor of how many I receive and I have no control over this.)
The First Report would look like this
Target % - Actual % - Target # - Actual #
Cubes 60% 70% 60 70
Spheres 40% 30% 40 30
The second and third reports would be the same format but would break down cubes into red and green and spheres into blue and yellow.I can accomplish the Report that breaks down the shapes but I am running into problems with the next level as I need the total number of shapes and the the total number of only Cubes or Spheres to insert into the expressions.
How can I update (some columns) in a table from the same table based on a Criteria column in the same table.
View 2 Replies View RelatedI need to get x and y coordinates for each device, but the data has to get looked up from 2 other tables.
I have a table (called InstReclosers) that has device names. Each device is on a Section. I can go to another table (called InstSections) and look up what Node that particular section is tied to. Then I need to go to another table (called Nodes) to get the X and Y location for that particular node.
How I can go about getting this X and Y data into the InstReclosers table?
...in summary, InstReclosers has device name and section name. InstSections has section name and node name. Nodes has node name and XY coords. Need XY coords for each device in InstReclosers.
When I try to transfer (ctrl c + v) data from a table in Excel to a table in Access it loses format.
Exemple: $ 1.000,00 (Excel) become 1000 (Access).
I need to keep the format to make sure that the code works properly.
I have a make-table query that pulls all the fields from 1 table (MainTable), and creates a new table with a date stamp based apon a form value entered (New Table = MainTableWithDate).
Currently, I setup the query to pull info from the form field like this:
DateField: [Forms]![frmmain]![DateField]
However, when the make-table query is done - all date fields are blank (all other fields are correctly created), and when I look at the new created table (mainTableWIthDate), the typeassigned to the date field is "Binary" (in the form, I've specified LongDate).
I have a table products with a field "id_product" and "total" (Total items in stock)
I have a query with the fields "id_product" and also the field "total in stock"
I want an update query to update the field 'total' in table 'products' with infos from that query
For each id-product in table products, replace the field total with the field 'total in stock' from the query
So I want to update a filed in a specific table with infos form another table.
Here's a query that the bottom listview in the attached form i.e. a listview representing a table of calls(many) to fims (1 top listview)
Code:
SELECT calls.id, calls.firm_id, calls.called, calls.said, calls.spoke_to, calls.next
FROM calls
WHERE (((calls.firm_id)=[firms].[id]))
ORDER BY calls.called DESC , calls.next DESC;
When I run the thing...I get a dialog asking me for firm id.
I want to change this so when I move up and down the firms LV (top)... the bottom LV updates taking firm id from the top LV with focus.
Access 2003.
I have an MS Access accdb with linked SQL Server 2012 ODBC tables. I am working on a procedure to copy data from local tables to these linked tables (identical schema). I did a simple
Code:
DoCmd.RunSQL "INSERT INTO linkedTable SELECT * FROM localTable"
This works, but is very slow. Way too slow. (INSERT copies the data one record at a time).
I would like to copy the data in a bulk operation, or operations that I can execute programmatically.
I have a table for a multiple parents linked to a child table. I need to figure out a way to only allow 1 parent to be coded as primary, 1 as secondary, and then the rest as other... I thought about making Primary/Secondary/Other a primary key. But then I can only have 1 other. I would have to make a finite number of parents that could be entered and I want an infinite number.... My end goal is to have a report that only has a primary and second parent on it, but the rest of the parents still exist in the table...
*Child_ID
Parent_First_Name
*Primary/Secondary/Other
How to make a form open with data from a table based on a date and time in the/a table?
View 7 Replies View RelatedHi,
I am extracting data from linked db2 table using access make table query. First I create a select query and can view the linked db2 data, but when I change to a make table query I get an error message, "invalid argument", when I run the make table query. There is no selection critera specified. Has anyone had this happen? and Do you know a solution?
Thanks for your support
GinnyP