Ive done this before in College but it was along time ago and i cant remember what i did
Bus say I have a table with Businesses and i need each business to be able to have multiple records associated with it.
So i have two tables one named 'businesses' and one named 'orders' how do i make sure that when a business makes an order the data in 'orders' is associated witht the releveant business in the 'businesses' table
I really suck at table design and coding, and access. I'm a techie that usually stays away from all this development stuff; it makes my head hurt. I'm a lot happier knee deep in an Exchange migration than developing even the most basic of databases as I suck at it (think I mentioned that already) However, i've been roped into doing a tiny little project for a friend. Below is the process I went through for the data / table design. When I started to create the relationships it got messy with the many to many relationships. Am I over complicating it or am I on the wrong track? Any assistance from you super-clever developer dudes would be greatly appreciated and would affirm your superiority over us humble techies yet again. :)
I started with the raw data:
Supplier (e.g. Builder's Supplies Co.)
Item (e.g. Plywood, Sharp Concreting Sand)
SupplierItemCode (e.g. WWE3428X)
Unit (25KG, 9x2440x1220mm)
Cost
Here are the main relationships:
A Supplier can have many Items, SupplierItemCodes and Costs
An Item can have many Suppliers
An Item can have many Units (e.g. Sharp Concreting Sand comes in many different weights (Units))
A Unit can have many Items (e.g. the unit of 25Kg can have several different types of sand or other items associated with it)
So what I did was to create four tables (* - primary key, ( ) foreign keys):
I created the ID fields in each table even though Supplier, Item, Unit and SupplierItemCode are all unique values that could be used as primary keys (or would that be bad practice; I told you I sucked at this?).
Supplier Table
-------------
*SupplierID
Supplier
(ItemID)
Item Table
----------
*ItemID
Item
(SupplierID)
(UnitID)
Unit Table
----------
*UnitID
Unit
(ItemID)
SupplierItemandCost Table
-------------------------
*SupplierItemID
SupplierItemCode
SupplierItemCost
(ItemID)
(SupplierID)
Simple questions I hope.
1.
What I'd like to do is do a lookup based on 2 fields in my table.
I'm tracking inventory for the company I am currently working with.
I have a computer table with 3 relevant fields:
compID, areaId, locationId
computer table
compID = PK for this table
areaId = FK from area table
locationId = FK from location table
area table
areaId
location table
locationId
areaId
There is a distinct relationship between area + location.
I want a lookup for the locationID, based on the area they have selected.
Thanks for any help on this one. On to the next question related to this.
2. As mentioned above I have an area table controlling the general departments (for lack of a better word). When an area is selected in the table, they have the ability to do a lookup for the relevant locations (implemented in the combo box).
I'd like the user to be able to add a new location item in the computer table, and have that value be added into the location table with the corresponding areaId as well. Am I looking for cascade update or something of this sort?
Thanks anyone. This is my first post here, I'm quite new to microsoft access as a database tool.
I haven't built a relational DB in years and I realise I'm pretty damn rusty at it. Any help would be appreciated.
Basically I've compiled a list of software applications our offices use, and I'm compiling which PCs have which software installed. Ideally I'd end up building a form with checkboxes for common software apps and text fields holding serial numbers or login IDs where applicable. But I'm stumped already on just the table layout. I currently have:
tblSoftware:
SoftID (Autonumber) - Primary Key and identifier for individual software apps
Software (Text) - Software title
Description (Text) - Any additional information (Licence, etc)
tblComputers:
NetID (text) - The Network Identification of the PC, also acts as Primary Key
User (text) - Name of user assigned to the machine
and having a One to Many relationship between NetID and SoftID. This should be a simple DB but I've been separated from all my manuals and the net is proving more useful for specific fault finding.
How can I set up the tables and relationships so that a query can be used to update the software for each computer?
Hi, I was asked to help create a very simple Access DB for a small restaurant to help them keep track of inventory.
The only things that the owner wants to keep track of are:
1.Item Catalog
2.Date and Quantity of Item IN or OUT
3.Current # of Items
My problem here is that there are 5 Item categories, each with a relatively lost list of Items under each one.
1.Drinks
2.Ice Cream
3.Kitchen Supplies
4.Grocery
5.Etc.
Will it be advisable for me to create an Item Catalog TABLE for each of the category instead of piling all the Items into just one Item Catalog TABLE?
The Designs I’m currently considering are:
A.)
TABLE SET 1 : Catalog of Items
Fields (ID, Name)
•TABLE Drinks
•TABLE Ice Cream
•TABLE Kitchen Supplies
•TABLE Grocery
•TABLE ETC
---------------------------------------------------
TABLE SET 2 : Movement IN/OUT of Items
Fields (Movement ID, Name, Date, IN-Amount, OUT-Amount)
•TABLE Drinks
•TABLE Ice Cream
•TABLE Kitchen Supplies
•TABLE Grocery
•TABLE ETC
B.)
TABLE 1: Catalog of Items
Fields (ID, Category, Name)
TABLE 2: Movement IN/OUT of Items
Fields (Movement ID, Name, Category, Date, IN-Amount, OUT-Amount)
I’m really more inclined to choice A because I just re-learned Access last month and will be creating my first serious database just now. I don’t want to choose a more complex table structure (choice B) that might cause problems with the DB later (as it will really be used to keep track of real-life inventory and might screw up their operations if it goes haywire). Is Design A viable? Or flawed?
Another question I have is with the structure of the Movement – IN/OUT table. Is this workable? My thought is just to let the user enter something like:
Drink [date] [IN] [OUT]
Coke [date] [2] [0]
Coke [date] [0] [1]
In the database, then compute current Quantity of the Item using a query (Sum[IN] – Sum[OUT]) . Will this work? I’m a bit apprehensive with this because I’m thinking I should have another TABLE that will store the actual inventory Quantity instead of the Quantity just being computed through query. But doing another TABLE for Quantity does complicate stuff a lot since it will have to determine which Quantity value gets added to or subtracted from based on many criteria. Will it be OK just to use the query to compute Quantity left?
Thank you so much for any help on this!
I have a very simple database for recording data collected from a questionnaire. The database comprises of
table1: QUESTIONNAIRE
fields: QUESTIONNAIRE ID
QUEST_1 ........
up to QUEST_75
table2: VALUE (1 to 6)
ANSWER (strongly agree, agree etc)
All of the 75 question fields in table1 record the same information, and all look up to a table2 which stores the 6 values: agree (1), strongly agree (2), neither (3), disagree (4), strongly disagree (5) and spoilt (6). As the data is being entered into a from using option groups, the database stores the value rather than the text in each QUEST field.
Pretty straightforward I thought, but when I come to query the database I am getting into such a muddle I feel like my head is going to explode!!
The results I need to produce should look like this:
ANSWER QUEST_1 QUEST_2 ....all the way to QUEST_75
agree 34 3 25
strongly agree 45 5 34
neither 3 2 4
disagree 3 4 32
strongly disagree 3 3 5
spoilt 0 1 0
Basically all I want is a count of the number of answers for each question! Given that the answers for all the questions are the same I thought this would be a piece of cake, but alas no.
I thought I would simply be able to crosstab my results from the questionaire into a new table, which would have the six rows (agree, strongly agree etc) and the questions as fields. The values should be the count of "agree" etc. I can quite happly have one field and the counts of each answer but when I try an add any other fields it just duplicates the counts!! I have sat staring at my query and am at a complete blank as to how I resolve this seemly simplistic problem :confused: .
Please if anyone has any ideas why I am in such a muddle over something which seems so simple I would really appreciate their help!
Many thanks
created a main form called "frmPatientDemographics" that contains
txtFirstName (Text Box)
txtLastName (Text Box)
txtDOB (Text Box)
txtCountry (Text Box)
cboGender (Combo Box)
it's record source is a table called "tblPatientDemo" that are populated based
on the information place in the above text boxes
Here is my problem
I have a subform called "frmPatientLanguageSub"
Source Object......... frmPatientLanguge
link Child Fields..... PatientID
link Child Fields..... PatientID
When list box Multi Select is set to "Simple" and I add a new patient and click on
multiple selections in the list box it does not populate the "tblPatientLanguge"
But if I change the Multi Select is set to "None" it populates the "tblPatientLanguge"
for that patient
Any Ideas why access populates the "tblPatientLanguge" if I set the Multi Select property to "None" but not for "Simple"?
If so How do I make access poplulate when the table when muliple selections are selected in the list box?
Hello All,
At work we have a large and messy Contacts list so I decided to set one up using a database.
At present I have 3 tables:-
Companies (custID,companyname,address,etc)
People (nameID,firstname,middlename,lastname,custID)
Phones (phoneID,phonetype,areacode,number,?????)
My problem is this, Some of the phone nos belong to the individuals
and some belong to the company. If a person is replaced at a company I need to reasign the company phone nos to the new person whilst retaining the individuals and their personal phone nos. If a company is deleted I need to delete only the company phone nos. and if a person moves within the company I want the company nos to reasign to the new replacement but keep the personal nos of the individual. Now I see its going to be more complex than I thought.
Can anyone help me with the table layout and links. (nb this is only a simple database relating a person to a company without using departments etc.)
Its main use is to provide phone nos names and addresses quickly.
Many Thanks
Peter
Hey guys.
Seems simple enough, I just can't figure out how to do it. How can I display the total number of table entries in a textbox on a form? thanks!
I need a simple audit capability
I have a table (Called Master Active List)
I have an empty duplicate of it called History of Master Active List. (Identical fields, with the addition of a distinct ID (primary key)
I have a form where the master can be changed (Called customer details)
When any change is made I want to post the whole record to the history table
1 - Am I correct I no longer need a form based one,(office 2013) and can do one on the data table itself?
I want to capture everything, can this be done with a wildcard, or do I need to go line by line?
The master table has a ton of fields.
Ok I am right now making a simple Vendor/Product database to create a line sheet for some sales folks. I have 3 tables: Vendors, Products, and an associate entity Vendors_Products to relate the two. I have a form currently that draws the Vendor Name (primary key) from the Vendor table and the Product Name from the associate entity. This allows me to create new vendors and select current product types from a drop down box. The problem is that the drop down box is too long and it is tiresome when 1 vendor has 10 product types.
Can anyone tell me how to resolve this? I thought it would be better to have option buttons and display all available products. Then you could just click all of the option buttons that apply to that Vendor and it would create the relationships...is this possible?
Hi,
I have looked at some of the threads here and it is clear that many of you are working on a much higher level than me and with a high degree of familiarity with the programme.
I am hoping that someone here is able to give me some advice as I don't find the MS help files digestible.
The task I have is to join 2 databases and produce a table from which I can run a mailmerge.
I have managed to join the 2 databases and I used a customer ID as a common link. (my apologies if the terminology is incorrect)
I now have all the data I require in one table.
THE PROBLEMs I have multiple entries for some of my customers and would like to reduce this to single entries (which is understandable). Please tell me how to do this if you can, and keep it as simple as you can please.
In anticipation I thank you very much.
:-)
Hi there,
I'm making a report detailing competitors final finishing positions in a competition.
The columns are (1) Place, (2) Name and (3) Points
I have the data for their names and the points they have scored, but how do I make the table enter the record number (starting at 1) in the first field???
Thanks in advance
Col
EXAMPLE
PLACE NAME POINTS
1 John Smith 199
2 Ian Jones 90
Not sure what the issue is here but i am trying to append a Job number from a main database into two connected databases.
The number is a sequential number i have created and the primary key.
It is an autonumber field(obviously). The format is J0000 and the numbers start from J0001
I am using two different append queries to append them into the external databases tables.
However (and here i think lies the problem) the fields i am inserting them into are text fields with the same format J0000.
When i view the numbers in these tables, they are displayed as J1 or J9. The 0's are missing.
Any ideas.
Thank you in advance
Hey guys, I'm just wondering how I could limit a field to using from 2 - 20 letters. If I placed the letter "a", I'd get an error saying how at least 2 letters must be there, and a maximum of 20. How would I do this?
Lastly, how can I make it so when I enter data into a form, I can click a button which adds it all into the table?
When I do it normally, it's automatically transferred into the table as I type it into the form.
This is incredibly simple I know, but I am a n00b at Access. ^_^
Thanks guys.
OK, I have a strange problem... This doesn't seem to work..... It did... then I made some changes.... Can someone clue me in on what I am doing wrong? A query, simply summing some values in fields... no biggy...... BUT...
The field
TotalHours: ([SundayHours]+[MondayHours]+[TuesdayHours]+[WednesdayHours]+[ThursdayHours]+[FridayHours]+[SaturdayHours])
So why isn't this working?
Can I use a simple VBA statement to sum these fields?
:confused:
hi all, this is my problem:
i got a table like this
ID time in time out working hours
1
1
2
2
the working hours is [timeput]-[timein]
how to i create a query so that all the ID 1' s working hours will be summed up ?
:confused: simple query
Critera : <Date()-30 should show records from the last 30 days ???
and : "status"="active" should show records from the last 30 days that are active ????
or am i totally off the mark here ?
vey new to this be gentle :eek:
cheers
I am trying to create an IN query (ACCESS) which will firstly show the customers table (specific details) then the orders details should be in the IN query, I formulated this but it won't work:
SELECT s.CustomerID, Address, City, PostalCode
FROM Customers s
WHERE
s.OrderID IN
(SELECT s.OrderID FROM Orders p
WHERE s.OrderDate = '1996')
AND
s.OrderID IN
(SELECT s.OrderID FROM Orders p
WHERE s.OrderDate = '1997');
Here is the one I did to show the customers and orders who made orders in 1996 and 97:
SELECT Customers.CustomerID AS Customers_CustomerID, Orders.CustomerID AS Orders_CustomerID, Customers.Address, Customers.City, Customers.PostalCode, Orders.OrderID, Orders.OrderDate
FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
WHERE (((Orders.OrderDate) Between #1/1/1996# And #12/31/1997#));
it is probably something simple I am missing here! Sorry to keep asking :(
but I can't seem to figure it out. All I get is Null values when I run the query.
I have a form with a start text box and a end text box. I use the form name sucessfully in the criteria row so I know there is no problem with the form name. I also know there is no problem with the qry name. Does anyone see anything I am doing wrong?!?!
The query I am working in is labeled [qry_old_summary_date_range]. Just wanted to clarify that is not a problem.
1st Pay Increase: IIf([qry_old_summary]![1st Pay Increase]>=[Forms]![Human Resources]![Start] And [qry_old_summary]![1st Pay Increase]<=[Forms]![Human Resources]![End],[qry_old_summary]![1st Pay Increase],Null)
and presumably it's only me who's irgorant.
I have a table with dates of deadlines and another with dates of submissions. For each deadline I want to know who submitted first. I figured that all I had to do was compute the difference between the dates and the select the minimum. However, Access rejects the Min() function in the WHERE clause. How do I avoid this?
Thanks a lot !!
I have a table (imported from excel) that is a pricing grid for blinds. The header row at the top is widths(mm) and the header row down the left is heights(mm). The data is the price. The import process makes the widths the column names.
height 600 800 1200 ...
300 $60 $80 $100
600 $80 $100 $120
900 $120 $140 $160
.
.
all I want to do is to take that table and make it flattened, like this:
height width price
300 600 $60
300 800 $80
300 1200 $100
600 600 $80
600 800 $100
600 1200 $120
900 600 $120
900 800 $140
900 1200 $160
I can't think how to do this in a query - I could take the flat version and create a crosstab to go the other way. Any clues - I don't want to change too much about the way the imported excel file looks becasue I have no control over that (I already discard miscellaneous heading and junk before I import it)
Hope you can help
My Brain has completely lost all of it's gray matter.
I have two tables tbl_Employers and tbl_Services that are linked via a one to many relationship. Each Employer can have Many Services. The field i am useing in the relationship is EmployerID
If I build a simple select query using these tables I will see all of the employers that have a related record in the services table. Any employer could be listed there many times as per the relationship.
What i want is a simple count of how many employers actually have recieved a service.
Why is this so difficult for me to get my head around.
this is my query in design viewhttp://img99.imageshack.us/img99/3095/querytc3.th.gif (http://img99.imageshack.us/my.php?image=querytc3.gif)i use this query when entering datai want to know how i can set it up it display all or any one/two/none of the criteria i put into the question text boxes, number surname and state, or even just number, or just state.anyone?
View 1 Replies View Relatedthis might be a crude roundabout way of doing things
but it works as is, and i'm trying to streamline a little.
i have a database that contains all of my customer's details.
once a month remove those whom no longer want my mailout,
and export the remainder into excel so that my printer can print and stuff envelopes.
i record customers that no longer wish to recieve my mailout, by putting something(anything) in a column that says rts.
what i'm trying to work out is, how in a simple query i can prevent any record that has any value in the rts column from displaying the name/address etc.
if there's a code to say
if anything in rts exists then hide name address state postcode
G'Day:
I am working on a simnple form that has just a few simple calculations in it. One being wieght difference and the other being percentage weight difference.
I get Access to calculate the wieght difference easily, but Ican't then get it to calculate a percentage weight difference (in another cell).
This is what I have down for code.
If Weigh1 <> 0 Then
WeightDiff = Weigh1 - Weigh2
End If
If WeightDiff <> 0 Then
PerWeightLoss = WeightDiff / Weigh1
End If
Any Suggestions?
Thanks