Find A Relation Between 2 Diff Tables

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 Replies


ADVERTISEMENT

Relation Between Tables?

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

Compare Tables From Diff Db's

May 5, 2005

hello, i have checked out the forum for pevious Q's to this problem and found the below.

http://www.access-programmers.co.uk/forums/showthread.php?t=80692&highlight=compare+tables

only problem is thats its writen at a bit too technicle a level from where i am. i've done some investigation into the problem though.

found out i need to use the docmd.transferdatabase function to import the tables i need to compare into my current db.

my question is this. how does it import them, does it create new tables within the current db or is it put somewhere in temp memory for use only while db is open, not sure how it would work. ideally i would like it to import tables, do the table comparison then create a report of the differences or make a new table showing the differences, then it reverts back to how it was before i imported it. i.e. he current databse is unchanged except for maybe a new report or new table (the results).

thank you for any advice.

Neil

View 1 Replies View Related

Tables :: Many Relation Between Two Table

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

Tables :: Table Relation With Sub Groups

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

Entering Data Into Form To Diff Tables

Apr 21, 2006

Is it possible in Access to create one input form that includes fields from different tables.

I want to create a single form that dispenses input fields to separate tables, I don't see anything to make this happen. I know you can retrieve data from separate tables utilizing querys. But is it possible to input data into a single form to multiple tables ?

View 2 Replies View Related

Tables :: Cannot Enter Data On One Side Of Many To Many Relation

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

Relation

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

To Relation Or Not To

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

Help With Relation Database.

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

One Table Relation To Two

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

Date Diff

Jan 29, 2008

hi all

im looking at doing a query to check records for absense

the 1st will be checking if the person has been absense for 4 weeks (28days) so i will have to check from absense date to today does that equal or greater than 28 days but lesss than 91 days (13 weeks) and is the return to work date blank

is that possible???

View 6 Replies View Related

Date Diff

Nov 7, 2005

Hello,
I have a form on which I have places a textbox named txtDOB which stored a date of birth.
I have then another text box which has the following function:

=DateDiff("yyyy",[txtDOB],Now())+Int(Format(Now(),"mmdd")<Format([txtDOB],"mmdd"))

Is there a way I can show the age + months?

Thanks.

View 5 Replies View Related

1 To Many Relation, 1-site Is Not Yet Created

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

Date Diff Problem

Mar 19, 2008

Hello,
I am having problems creating a Exp to show the difference in time.
I am currently using :Expr1:DateDiff("n",[TimeReported],[TimeDefectGiven]) which is returning a figure in decimal.
If the answer was to be 90 minutes i need it to return the figure as 1.50 instead.

I have looked on the site using Date Diff and Time without success


Any help given would be great

View 3 Replies View Related

[Table Relation] Problem

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

A Problem With A Relation Table

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

Problem With Table Relation

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

Diff Between Table Fields

Jun 7, 2005

How do I make a query that shows me the differences between two tables with relation to the fields.

View 1 Replies View Related

Index And Query Relation?

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

Calculation Between 2 Diff. Rows

Aug 21, 2007

hi everyone, i have a table of timeseies data and would like to run a query working out the difference between the current row's value (day) and a previous row (day, or days before) and put this difference in a new column in the resulting query.


I know it would be straight forward in Excel to run a formula between 2 different rows, but this table has >1 million records and are appended with new records regularly.

Any help or inspiration would be greatly appreciated!

View 14 Replies View Related

Date Diff Question

Sep 18, 2007

I have a date field, I am trying to have my query only pull the records that have a date entry that is within and up to 90 days from today or Now. For this query all other records are irrelevant.

THis is what I have found so far

=(Abs(DateDiff("y",Now(),[ls_Exp Date])))

Can someone show me how to add the 90 days in there?

Thanks.

Fen How

View 3 Replies View Related

Date Diff Problem / Please Help.

Oct 11, 2007

I'm using the date diff .... DateDiff("h",[Start time],[End time]) in a query and then I report the results in a report. I have run into a problem for the first time. I had a start time of 7:00pm, and an end time of 7:00am. This was calculated in the query as (-12) which is messing up my cost formula.

How do I resolve this?

Jerry

View 3 Replies View Related

Date Diff In A Query

Apr 27, 2006

Here we go:

My overall goal is to find out a worker’s downtime (count of weeks between assignments)

I have a qry_Downtime that pull in a worker and his current and future assignment. Each assignment is associate with a start and end date.

Here is how the results look:

Employee (joe schmo)ProjectONE ProjectONEStartDateProjectONEEndDate
Employee (joe schmo)ProjectTWO ProjectTWOStartDateProjectTWOEndDate
Employee (joe schmo)ProjectTHREE ProjectTHREEStartDateProjectTHREEEndDate
Employee (joe schmo)ProjectFOUR ProjectFOURStartDateProjectFOUREndDate

Downtime would be defined the diff in weeks between ProjectONEEndDate and ProjectTWOStartDate, ProjectTWOEndDate and ProjectTHREEStartDate, etc.

Any suggestions.
T

View 5 Replies View Related

Date Diff Problem

Jun 14, 2006

Friends,

I have a problem with a date diff calculation:
On my form I have placed two controls, a text box and a check box.

The textbox contains is a date field (mm/dd/yyyy).
What I need is a code that checks the checkbox if the date in the textbox is within the last 12 months.
This is the code I am using but not working:

If DateDiff("yyyy", textbox, Now()) >= 1 Then
Me.check1 = "yes"
End If

Any help? Thanks.

View 11 Replies View Related

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 10 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved