Creating A Simple Relation
Dec 17, 2007
I have basically a list containing 400 names and addresses. For each address, an event can occur. If and when it does, I add 3 fields to hold the date, type, and result of the event.
Simple enough, but I am at a complete loss as to how to make a simple rdb with table 1 containing 400 rows of address data and table 2 containing event data. Hopefully somebody can help me.
My table in Access 2000 in its simplest form consists of the following fields:
ID (primary key)
Address (actually several fields)
Date1
Event1
Result1
Date2
Event2
Result2
|
|
DateN
EventN
ResultN
View Replies
ADVERTISEMENT
Jan 3, 2005
I need to create a query that will output the number of times doors have been used during time bands of a day.
I have no problem getting the info I want from the doors that have been used (in other words where there is a record in the field) however, how can I ammend the attached statement so that it reports no records as a "Zero"? in the same format always ie,
North Entrance, 2
South Entrance, 4
East Exit, 0
West Entrance, 3
I need the data in this format so that I can export it to excel.
Here is the SQL view of the query I am using
SELECT [Staff Entrance].[Door Location], Count([Staff Entrance].Time) AS CountOfTime
FROM [Staff Entrance]
WHERE ((([Staff Entrance].Time)>#12/30/1899 2:30:0# And ([Staff Entrance].Time)<#12/30/1899 23:0:0#))
GROUP BY [Staff Entrance].[Door Location];
The sample table is attached
Thanks in advance
View 5 Replies
View Related
May 17, 2007
AFternoon,
I have never used Access before and i have been asked to create a report that displays the cartridge name and the quantity. This part was easy. But, the problem i am having is that i have a field in the database called "DoNotOrder". If the cartidge has the box ticked in the field "DoNotOrder", I do not want it to be displyed in the report. I was wondering if anybody could give me a clue into writing a statement to do such a thing.
ANy help is much appreciated.
Thanks for your time
Ollie
View 1 Replies
View Related
Jun 14, 2004
Hi,
I don't know how to create a simple query that will compare 2 tables and create a new one after that.
Each table has only one field.
Example:
Table 1 has records A, B, C, D and E
Table 2 has records A and C
I want new table to be like table 1 minus table 2. Like cleaning up table 1.
New table would have records B, D and E
I think it's very simple but I can figure how to do it.
Could you pls give me advice?
Thanks a lot,
skuba
View 3 Replies
View Related
May 19, 2012
I need to create buyer and seller invoices within my access 2003 databases, my sister in law runs an antiques auction house and I'm working on a database to capture all their information.
I've created a 2 queries (a buyer invoice and seller invoice), show all unsettled items with the relevant item information and fee's.
However, I would like it to automatically allocate an invoice number, and store the information back to a table (seller and buyer ID, total number of items on the invoice, total fee etc)
I'd like store the invoices as well, so within the customer record form, I can include a box showing a list of the buyer invoices on one side and seller invoices in another (not all customers are buyers and sellers).
Once I've issued an invoice, can I automate it to show that item as then as invoiced?
Can the invoices be editable at all?
When they come in and settle their account, I also need to feed this information back in as well.
Can i arrange an invoice for seller's particularly, to show all sold items, and the fee's associated with them
Returned items as well as any associated fee's with them?
I have been looking at the Northwind example.
I see how they have an orders details table and orders table, I could replicate this.
I could have an Buyers Invoice table and Buyers Invoice Details table, but how do I generate an invoice, pull the next Invoice number from my (currently blank) invoice table, and pull in the outstanding items for that buyer for that specific auction date (I have a query), and then populate back information from the invoice?
I like the fact that their order form is editable, do any changes go back to the query, that then populate the invoice when you click print invoice?
I need to be able to (at the end of the auction) pull all items from my item log, for that buyer, and print them an invoice, automatically saving all the invoice details back into my database.
I need be able to automatically flag the items in my items table, as invoiced and pull the invoice number in?
I can see how all the Northwind tables, queries, and forms relate to each other and subforms, but I'm not sure how to actually create an invoice, get invoice number, merge with my query, and then feedback in.
View 1 Replies
View Related
Jun 23, 2007
I'm using the new Access. I never created a relationship till now. I linked three databases. I expected to enable a right-click and launch and be able to see all the records in the linked databases. Nothing happened. What am I doing wrong.
Bob
PS Is there a way to review the code used to creat relationship. I'm from dBase 4 where we had a dot prompt or regedit, not graphics. Not a complaint. It might taker me abit.
View 4 Replies
View Related
Apr 14, 2005
Im wondering, i have goten pretty comfortable with vba and undbound projects. Now im facing a pretty big project, and it got me thinking should i even bother with relationshops and alll that other stuff you need when making bound forms. i figure all the relationships can be codded in vba.
Let me know on your opinion
View 1 Replies
View Related
Aug 8, 2005
Im trying to make a computer invoice and quotation system, and im not having much luck. I have 2 table, one called products which has my product details (eg, product_code = cpu_amd_athlon64_3500) and a stock table, which uses that product code to call up all the other details. I want to go to my stock table, enter in my product code, and it brings over the product description and price from the products table. Ive been fiddling with relational databases and forms but i cant get it. Any help is much appreciated!
Subaru
View 3 Replies
View Related
Jun 6, 2005
Hi everyone,
I cannot figure out how to link my tables. Here is my problem: I have one main table that contain informations about contracts (No folder, name of the provider, etc.) And I have 12 other tables. Each of them contains informations about one specific type of invoice. for exemple, one table is used for gaz billing, an other one is used for shipping, and so on. Now I don't know how to link all these tables with my main one. Because one entry in the main table have a 1-to-many relationship with all these tables. should I leave the relations, and just retreive information manually?
Here is the definition of the main table:
No - AutoNumber and primary key
Localisation - Text
...
The definition of the 12 tables is:
NoBill - AutoNumber and primary key
NoAutoInstallation - that contains the No from the main table
Total - total of the bill
I'm really confuse. Thanks in advance for your help!
View 1 Replies
View Related
Feb 4, 2008
Usually, a lookup column looks like this - "Take the value stored in this lookup column, use it to find some information in another table, and then display the information from that table in this table."
I need to Take the value stored in this lookup table, use it to fill two separate tables depending on ordertype. If the order is type1 I fill table 2 if order is type 2 I fill table 3
I have a database with 3 tables:
Table 1:
ID autonumber primary key
ORDR (numeric value increases by 1)
Table 2: Oders printable info1
ID autonumber primary key
ORDR (linked to Table 1 )
Table 3: Oders printable info2
ID autonumber primary key
ORDR (linked to Table 1 )
It's a piece of a large dabase and hard to explain, but I have to enter specifinc information in Table 1 then with relationship enter that information in Table 2 or Table 3 depending on the order type. Table 2 and 3 hold other information that is used to generate a report. Table 1 provides start unique identifier increased by 1 like, ORDR-001, 002 003 etc.
I never have to enter or should I say link same order number from table1 to both. Would like to prevent it by acident too.
Do I need a junction table to make it work? Is there a better way to make it work?
Thanks.
View 4 Replies
View Related
Apr 11, 2006
The problem:
I have a table: TBL_INVOICES
and I have table TBL_SHIPMENT_NOTES
One invoice can have many shipment notes, so a created a
One to many relation between TBL_INVOICES and TBL_SHIPMENT_NOTES
However, the shipment notes are created earlier, thus there is no
invoice_ID when I create a shipment note.
How to deal with this problem ? Please, help ...
View 3 Replies
View Related
Nov 9, 2005
Hi!
I'm new to MS Access databases.
I'm trying to buil a database of a kind of elements. Every element has it's description (name, number, etc), but there are other parameters, which I want to add. I'll try to picture this:
Element_Table--
|
- Date
- Number
Element_Additional_Info--
|
- Data1
- Data2
For every element in Element_Table I want a seperate table Element_Additional_Info.
I tried to do this with relations, but for every element I have the same data in addition info.
Can anyone help me??
Thanks in advance...
--
Best regards
fahur
View 3 Replies
View Related
Apr 18, 2007
I've been stuck on this for hours. I have to create a relational database on a small company and when i try to create a query on anything i only get one record showing up, with no criteria. This is my relationship
http://img156.imageshack.us/img156/1809/relationshipsbi0.png (http://imageshack.us)
Any help would to make this relationship work would be greatly appreciated, i need to go ly down
View 3 Replies
View Related
Jun 11, 2007
I'm pretty new on access but I need to get a project done for my office which is a call center, we are trying to identify and quantify the reason our agents are calling our support desk, and I'm trying to set up a simple database that our support agents will feed up.
I have a table (A) that contains AGENTNAME, AGENTID and SUPERVISOR (every agent is assigned to a supervisor and there are four supervisors and around 60 agents) the primary key on this table is autonumber. I have another table (B) that is a lookup table and contains the reason CODES (Ex, GRAL INFO, RATE, ETC), there is another table (C) that is filled up using a form that pulls agents name from table A using a combobox, also pulls the reason codes from table B and fills date and time also. the problem is when I try to set up a report using all this data, I cannot get the SUPERVISOR name and AGENTID on such report, I think this may be related to the tables relationship and I´ve tried several options but cannot work it out.
Can you please help, any help from your vast knowledge will be greatly appreciated. Thanks in Advance
View 1 Replies
View Related
Jul 4, 2007
Hi guys
thank you in advance for any idea/info!!
i have 2 tables, P.O and Invoice. In PO tbl i have an Invoice field with Index set to Yes (No duplicate)
On a form based on PO table, I couldnt enter a new record due to this so i set the Index back to NO
Now..I have a combo bound to PO table with a query taking a list serial number from another table. I want to filter away whatever serial numbers from this list that have been added to PO so i write this SQL
"SELECT DISTINCTROW tblComputer_Cabinet.[CC_Serial #]
FROM tblComputer_Cabinet, tblPO
WHERE (((tblComputer_Cabinet.[CC_Serial #])<>[tblPO].[CC_Serial #]))
ORDER BY tblComputer_Cabinet.[CC_Serial #];"
which worked fine before i removed the index
Why was it?
View 2 Replies
View Related
Mar 14, 2014
i have a table set up in a many to many relation with each other.
it is
tblProducts
productId PK
productcode
Description
Status
tblLink
ProductId PFk
Location PFk
tblLocation
LocationID PK
Location
QuantityIn
DateIn
PONumber
I have created a query that shows me the product by location and a query to show me locations with product...What i am trying to do is have a main for that show me the product and insert a list box(Form Control) that show me the locations and the quantity for that product. the problem i am having is that i cant get the query to only show me the results for the product on the main form it shows me all the products on all the locations on the list box.
View 3 Replies
View Related
Sep 1, 2014
i have 2 tables
1. Actors Table
2. Movies Table
i create a third table so i put manually the id of an actor than the id of the movie belong to this actor.so in the third table we could see the movie more than one time because there are many actor in the same movie..could i get the result in the third table or anything but with an easier method because it takes too much time to finish it i have more than 1000 film.
View 4 Replies
View Related
Aug 9, 2011
I have two tables, one for jobs and one for clients. I have a one-to-many relationship, so one client can have many jobs. Due to a problem with data entry, I have some client records that have no job associated with them. I need to delete these records.
View 1 Replies
View Related
Feb 12, 2008
Hello Again!
First I just want to say thanks to everyone because you guys are great and have helped me with more than one database. Now for my problem...
I've been searching the forum and reading a lot of the posts about how get the most recent date from a query using Max. My problem is that I don't understand SQL. :confused: So, the stack of papers that I printed out to help me aren't really helping at all. ^_^
I know that I don't necessarily need to understand SQL if I know what to put into it -- so please help!
I need to pull the most recent date from a list of court dates that are updated on a subform of a subform. This way I can print a list of names to watch for in a given week.
The tables involved go as follows:
BaseTable
CrtTable
CrtSubTab
BaseTable is linked 1:Many via [ID#] to CrtTable
CrtTable is linked 1:Many via [Court ID#] to CrtSubTab
I need to find out the most recent court date [Pending Court Date] from the CrtSubTab for each [ID#] depending on the quarter (which is a field [Quarter] in the CrtTable). I will set [Quarter] criteria to pull from a choice selected on a seperate form prior to running the query. In addition, I'd like to have the dates that are pulled fall within in a specific date set, which I can also input on the same form that restricts the quarter. This I know is a simple Iif statement using < & >. But - will that impact my ability to use Max through SQL? I have seen several posts referencing the need to use two queries.
Pertinent Fields by Table would be:
BaseTable - [ID#], [Last Name], [First Name], [Middle Name], [DOB]
CrtTable - [Quarter]
CrtSubTab - [Pending Court] (which gives the hearing type), [Pending Court Date] (which provides the date)
Thanks and I hope I gave enough information.
View 11 Replies
View Related
Apr 30, 2008
hi
i have a database to manage utility bill payment , it consist of
- Bills :
- billID
- Benificiary Name
-Cost Center
- Bills Transaction :
-TransID
-bILLid
-BillDate
-BillAmount
-Payment Transactions :
-PayTranID
-bILLid
-PayAmount
-PayDate
-BankRef
i made a union query from Bills Transaction and Payment Transactions to calculate bills balances which is : billid,sum(Bills Transaction.BillAmount)-sum(Payment Transactions.PayAmount)
all is working well , but the problem is i cannot find any relation between billtransaction
and billpayment ( per bill ) , cause i wish to payment details for each single bill transaction
the normal case is : bills issued as monthly basis but may fully or partially pay as the following cases :
- each bill transaction may fully pay one time
- in some cases : each bill transaction may fully pay but in multi settle
- multi bill transaction (per BILLID) may fully pay one time
IN CONCLUSION : each bill transaction should be stteled fully within one or two or maximum 3 months , say bill balance for each bILLID shall be zero.
how i could find a relation between this two transaction ( bills and payment ) to preview
payment information for each single bill transaction
exapmle :
billID : 39
BILL Transaction BillPayment BillsBalance
Bill Date - Amount PayDate- Amount
jan08 - 1000 1-1-2008 1000 0
feb08 -1200 5-2-2008 800 400
15-2-2008 400 0
mar08 1900 1900
apr08 1100 30-04-2008 3000 0
may08 1200 05-05-2008 900 300
jun08 1300 30-06-2008 1600 0
View 13 Replies
View Related
Jun 18, 2013
I new to Access, I have used MS Works spreadsheets and database. Im working on a database for a non-profit to enter weekly donations.I have tables below:
1. DonorsT (names...) table
2. FundT (names...) table
3. SubFundT (names...)table
4. DonationT table (for cash, check, checkNumber ext.. )
5. DonationToFundsT table.
The Relation set up:
DonorT DonorID PK (1 to many) DonationT DonorID FK
FundT FundID PK (1 to many) SubFundT FundID FK (so the main Funds can have many SubFunds)
Because one donation can be split to many Funds/SubFunds:
DonationT DonationID PK (1 to many) DonationToFundsT DonationID PK
SubFundT SubFundID PK (1 to many) DonationToFundsT SubFundID PK
My question:
As you can see donations can only be recorded to SubFunds:I can work around this by having the first SubFund name be the MAIN Fund nameBut I was hoping there was a way to enter donation to the main Fund and the SubFundsExample with this set up:
General Fund
General Fund $100.00
Repairs $50.00
Total to General Fund $150.00
What I would like
General Fund $100.00
Repairs $50.00
Total to General Fund: $150.00
Because this data will be entered by people that dont work with Access much Im trying to make it simpler for them.
View 6 Replies
View Related
Apr 28, 2012
How to create a validation rule in relation to another fieldname in the same table?
For example, the first field line is the serial number, and when something is entered for the serial number the OTHER field line (which is quantity) needs to be 1, otherwise it stays blank.
I use access 2007 or 2010 depending what computer I'm using.
View 3 Replies
View Related
Jul 3, 2013
I made a database with 3 tables:
"city"
"company"
"person"
Each company has a director (I choose from a list that's bound to "person" by its ID field)
Each person lives in a city (I choose from a list that's bound to "city" by its ID field)
So:
- a person's city is stored in "person" as the ID of "city", so I only have numbers (1, 2....)
- there can be two persons in "person" with the same name (the only thing that differs them is the city they live in)
The problem is:
When I try to choose a director in "company" from a list, it shows me the name, surname and the ID of the city the person lives in, all from the table "person". The trouble is, since there can be two persons/directors with the same name, I really need to see their cities (the real city name, not just its ID). But I still want the city in "person" to be stored as ID of "city".
I attached my database so you can see what I'm writing about.
practice.accdb
View 2 Replies
View Related
Aug 20, 2004
Hi all,
Is there any option to count the number or records relating to
a specific field. Here is the details..The db is for a school and there is a tables for
class and students. I am using a form to enter students details. I had created a
relationship with these two tables. i am using a query to get the details regarding a specific
class like classname, class teacher like that.. Is there any way to calculate the number of students in that specific class.
now i am using the query as follows
SELECT Class.*
FROM Class
WHERE (((Class.ClassID)=[Forms]![Class]![ClassID]));
I had created the relationship with the above two tables with classid as the primary key
thanks in advance
Thanks
View 1 Replies
View Related
Jan 30, 2014
I am trying to create a warehouse database in acees 2007 and 2010 that can track goods that come in.i have two types of goods Specials which is not on my current stock list and Current stock of which i have a excel sheet of around 32000+ items.I have 2 warehouse to put the stock in with over 100 locations.I am trying to track the stock that comes in by saying it is Special or Current stock if Special and i enter the data it should add it to my Stock sheet if current I can choose it from the Current Stock list; allocate it to a location .
Lastly i need to be able to move the stock either from one location to another in the warehouses or to a customer on a orderI have created a few tables and tried to link it as best i can after reading 100's of posts and access for dummies
tblProduct
ProductPK-Autonumber
ProductCode - Text
Description - Text
Dept - Text
tblLocation
LocationPK - AutoNumber
Location - Text
lnktblPtoductLocation
LinkPK -Autonumber
ProductFK - Number(long integer)
LocationFK - Number(Long Integer)
Qty - Number
PoNumber - Text
DateIn - DateTime
i set this up as a many to many relation and that is as far as i got.when i try and enter a location for a product to test it say i cant update location field and things like i cant enter data on the one side of the many to many relation.
View 14 Replies
View Related
Sep 2, 2013
Let's say :
1 navigation form called Nav_Main
2 subforms :
1 called master_form ( PK = id_master) (table : master_table)
1 called details_form (with id_master in it) (tables : details_table)
the relationship is still established
I want to switch from master_form to details_form and retrieve only records corresponding to the last id_master where I was in master_form
(do I absolutely need to recreate a parameter query of details_table ?) What is (are) the simple way(s) to do it ?
View 3 Replies
View Related
