Linking Records Between Multiple Lists - Client And Products For Finance Company?
Oct 8, 2012
I am trying to put together a database that tracks individual clients and what products they selected for a finance company. I have setup the CLIENTS list but because each client will have different (and multiple) services I wanted to add the SERVICES list as a separate item and then link each product to the individuals that each record relates to. There may also be two clients on the one product so again it would be ideal if the product (i.e a Home Loan product) was setup in the SERVICES list, and then I could link One or Two clients to that individual mortgage record using their data which is stored in the CLIENTS list.
View Replies
ADVERTISEMENT
Dec 8, 2013
I want to create tables with clients and another table to populate multiple orders for each client. How these tables will look like and how to create one to multiple relationship.
View 2 Replies
View Related
Nov 21, 2012
I have a table (tbl Team Info) which contains names and codes for teams within my business (>400 records) and another table (tbl Process) which contains a list of high level tasks (30 records).
I need to create something where for each team name 9in tbl Team Info) I can map them to the tasks that they undertake (in tbl Process) and assign a percentage of time then spend on each task. Each team could map to several different tasks.
View 3 Replies
View Related
Apr 22, 2013
I have a database with a table with company names, then a relationship to another table that shows that companies' address, but I also want there to be an address 2 and 3 and so on, and some of our companies have multiple sites.So what Im asking is how would you be able to show multiple data, on a sing record.
View 3 Replies
View Related
Jul 12, 2005
Hi!
I need to create a database of the info of a few hundred companies. Each company (food producers) specializes in various product categories, not just one. This is my problem/question: each company entry should then have a possibility to have as many product categories as needed. How can I do this without using "free text" fields? The website user should obviously also be able to search the db - by clicking checkboxes with the categories maybe?
(+Also: When the admin user needs to add the info of a new company (with, say, 9 product types) into the database, what kind of an SQL clause I need to INSERT INTO the db? For example if the product categories where laid out as checkboxes, and the user would just click the ones he wants.)
View 8 Replies
View Related
Jul 10, 2007
Hello All,
I've searched and searched and can't find anything but it might be because I'm not sure what to search for.
Here is what I'm trying to do. (I'm simplifying this to make it easier to discuss)
I have a table that looks like this:
Company Date Sales
1 5/5/07 $1,200.00
1 6/5/07 $4,200.00
1 7/5/07 $2,200.00
1 8/5/07 $4,300.00
2 5/5/07 $1,200.00
2 6/5/07 $4,200.00
2 7/5/07 $2,200.00
2 8/5/07 $9,200.00
2 9/5/07 $1,200.00
I need to run a query that grabs the last 3 months for each company.
So the correct output from the above table would be:
Company Date Sales
1 6/5/07 $4,200.00
1 7/5/07 $2,200.00
1 8/5/07 $4,300.00
2 7/5/07 $2,200.00
2 8/5/07 $9,200.00
2 9/5/07 $1,200.00
Any help would be appreciated. I'm sure this has been asked in the past so even if you could help me with a keyword to search for, I would be greatful.
Thanks in advance,
Randy
View 4 Replies
View Related
Mar 21, 2008
Hey people, hows it going? Quick question
I am creating an EPOS system for a bookstore and I have a many to many relationship between the transactions table (tbl_transactions) and the products table (tbl_products) using a link table (tbl_linktblproductstransac). The below diagram shows what i mean below:
http://img512.imageshack.us/img512/3246/relationnshipswd9.jpg
I made it many to many because 1 transaction can have many products on it and 1 product can be on many transactions.
Now what I need to know is how will i make records with more than one product on one transaction (in the table itself or in a transaction form). I would really appreciate if someone could help. Thanks :) and have a good Easter
View 2 Replies
View Related
Nov 9, 2004
Apologies - i'm a "rookie" at Access - but i have searched for a while now on the site for the answer to my question... I'm sure there is a simple solution somwhere here but i cant find it!
I am putting togther an venue database - with all the usual fields: Name, Address, Capacity etc... of which i have set up YES/NO, Free Text and Number fields. No problem!
I have also set up various lookup lists which work fine, apart form the venue "Suitability" lookup list.
The choices are:
Corporate
Private
Drinks Reception
Dry Hire
Music Event
Conference
Now, some venues may be suitable for SEVERAL of these uses - so how do i select multiple uses from this list as opposed to just one?
Many thanks in advance for your help - im learning fast! :rolleyes:
View 2 Replies
View Related
Apr 2, 2008
OK, not sure how to search for this so I am just going to ask. I have two properties that send me excel sheets with the name of the employee, employee number, department, and title. What I would like to do is make this into one list. So just one table that would list these fields and maybe get a new field that marks what property that person works at.
Example
Jon Smith, 12345, admin, office ( this is from one list)
Jane Smith 54321, admin , office (this from the other list)
What I want it to look like (query)
Name, Emp #, title,department,property
Jon Smith, 12345,admin,office, prop1
Jane Smith,54321,admin,office, prop2
How would i go about doing this?
View 2 Replies
View Related
Jan 17, 2013
I have a table (table1)that table has several columns: company / Qty / date / ERT...I want to be able to run a query that will group all the records by Company and by date and provide the sum of QTY - as well as the cumulative totals for each company and date.
My query looks like this:
amonth: MONTH CODE
Group By
assending
Field 1: Alias: Company
Group by
Ascending
Field 2: QTY
Table1
Sum
Field 3: Runtot: DSum("qty","table1","[month code]=" & [amonth] & " And [company]=" & [alias] & "")..I have the query set up - but my runtotal Dsum formula is giving me grief - specifically a #ERROR and I can't figure it out..I basically want it to look like this:
A company 15/Nov/2012 10 10
A Company 16/Nov/2012 10 20
B Company 12/Jan/2012 15 15
B Company 21/Mar/2012 5 20
View 3 Replies
View Related
Jan 21, 2014
I have been asked to make a report from my database listing property sites and listed things found with them. Ex. a property might have 3 houses and 2 cars on it. Whenever I make a report the best I can make are groups that look like:
Property 1
House A
Car a
Car b
Car c
House B
Car a
Car b
Car c
I need something that could group car and houses directly to property 1 rather than cars grouped to houses grouped to property.
Ex.
Property 1
House A
House B
Car a
Car b
Car c
View 4 Replies
View Related
Nov 8, 2012
I'm still working on that sales database...and I now ran into an issue with multiple employees per client.
I have 2 tables, tbl_Employees and tbl_Clients.
tbl_Employees:
[Employee_ID]
[Employee_Name]
tbl_Clients:
[Client_ID]
[Employee1_ID]
[Employee2_ID]
View 2 Replies
View Related
Jun 7, 2012
I have a typical table regarding client details: tblClients
Another Table containing Investment orders: tblOrders , of course many orders can be made per client.
What I would like to achieve is some way of exporting this data in to excel with all data regarding each individual client stored in a single record. (This is needed for mail merge purposes)
What i have now:
Client : Bob Fossil Order: Pension
Client : Bob Fossil Order: ISA
What i would like:
Client : Bob Fossil Order: Pension, ISA
View 1 Replies
View Related
Nov 9, 2005
Hi all
Brand new on here and desparate for some help and guidence.
So far with Access I have just used it as a store of addresses to mailshot prospective clients.
However, I now need a more complex database and this is where you might be able to help.
First things first, most of my clients are in universities. This means that I can be used by more than one person in more than one department at a university.
Does this mean I need to do three tables:
1/ "University Details" which gives the address details
2/ "Department" storing the departments of the unis we work for
3/ "Client" Name of the client(s) in that department.
After this how do I link them?
Thanks
Razieluk
View 4 Replies
View Related
May 23, 2006
Hi guys. I am trying to help a client regards an un-supported access database. They have a DB that is shared across a network (only have experiance on database on single client, never set up security etc....). The DB is installed on the Server.
The DB is access via a mapped network drive. The Database folder contains the following files:
MKC Clients_Quesries.mdb
MKC Clients.mde
MKC Clients_.des
On one machine the MKC Clients.mde is launched and 1086 records are visible. On a second machine the same MKC Clients.mde is opened but displays only 977 records?? An I missing the obvious?
Any help appreciated, she is a nice lady!!
Phil.
View 11 Replies
View Related
Jan 25, 2008
Hi,
Firstly, I apologise if this has been posted before. I have done a couple of searches of the forums, but can't find what I'm after... and being new to access, I'm finding it hard to know what exactly to look for. How to even explain what I want is difficult!
I am designing a database for the clients we deal with a work. It started out as being a database of 'post' and correspondence, so the Idea was, we'd have a databse of all the clients... and by selecting their names, it would bring up a list of all the letters that have come in for them. I have managed this part fine - probably basic, but fine.
What I would like to do, is expand it to more than just 'letters'. Each client has done a particular 'work' with us, and each client has particular details that are unique. For example, a client will have made certain transactions through a bank account.
I hope to be able to click on a client, and have a screen or page pop up with details and comments about that particular client. I have tried hyperlinking but that doesn't really produce what I'm after. For example, I tried creating a "report" with a page for each client, so that I could have comments for each client - hyperlinked to the table. But all it does is link to the "whole report" and not a particular page for a particular client.
I'm sure this is sounding really confusing:
I have a client named "Peter Litman". His name and details are within a "client" table (listing all clients). I hope to be able to click on his name (or row) and bring information (comments, details etc.) about him. I thought this could be some sort of "pop up", or link to a special page. ??
The same would then apply with all other clients.
Any ideas? questions?
thanx.
View 2 Replies
View Related
Mar 1, 2007
Hi
I would be glad of a little help.
I am creating a c ontracts database and need to keep trak of individual contractors figures.
I set up a contractor and allow the system to only issue orders to a contractor if there monthly balance doe not exceed 1/3 of their total monthly turn over.
My problem is in keeping track of the relevant figures and how I go about this?
Thanks in advance
View 6 Replies
View Related
Jul 4, 2013
I have a database that was created by a colleague. They have created a form for adding client records onto the database.This is done via a button that, when pressed, checks mandatory fields for correct entries and then saves the record.However, when we do this, it is creating two identical records on the database.
She cannot see what she has done wrong in the code, and I know next to nothing about coding,
Code:
Private Sub CmdAdd_Click()
Dim dbAddClient As DAO.Database
Dim rstAddClient As DAO.Recordset
Set dbAddClient = CurrentDb
Set rstAddClient = dbAddClient.OpenRecordset("ClientInfo")
[code]...
View 11 Replies
View Related
Jan 17, 2014
I have a table with duplicate records (which is ok) and I want to return distinct data for each client.
It works fine when there is only two fields returned however, when I add a third field to the query it no longer returns only the Distinct records - I am getting Duplicates returned.
I.E
SELECT DISTINCT tblClient.ClientNo, tblClient.Name
FROM tblClient
Works fine with only the Distinct records for each client returned
However
SELECT DISTINCT tblClient.ClientNo, tblClient.Name, tblClient.Address, tblClient.OrderValue
FROM tblClient
Now returns Duplicates!
Is there a limit to the number of fields to be returned using DISTINCT or what else could be the problem? Should I be doing this some other way?
View 5 Replies
View Related
Sep 8, 2013
I'm developing my own personal finance database and the part stumping me on the design is how to setup the categories per transaction. For example a primary category would be "home" and the secondary category would be "furnishings". How do I set this up? I'm getting stuck on how to tie it into the transaction table.
I'm familiar with writing SQL queries, but this is really my first stab at database design.
The relationship diagram is attached. The transaction table and the category tables are to the right of the diagram.
View 10 Replies
View Related
Nov 30, 2005
Hello,
I am using multiple forms to enter some data. When I enter the data in the second form and go back to the main (first) entry form it always goes back to the first record. Also when I am at a record at the first form and go to the second form it always starts at the first record. Is there a way to make the two forms match the record I am entering. I am entering data into the same table. I needed to use two forms because I could not fit all the information on one form. Thanks
View 2 Replies
View Related
Jul 2, 2012
I work for a small manufacturing company and have been asked to set up an access database to track production. The end goal is to have a touch screen at every machine where operators will input data. This data will then dump to a different workbook for each machine. My question is: Is it possible to link one access tables with multiple xls documents so that, as data is added to any of the workbooks, it is then updated to a single access table? I also will need to find a quick way to link new files to the table as new parts are put into production.
View 4 Replies
View Related
Mar 20, 2008
Hi
I am very new to Access and have been building a database from scratch. It is for inventory management and I have a number of tables, particularly:
1. Stock List
2. Orders (stock coming in)
3. Requisitions (stock going out)
I have created Select Queries for the Orders and Requisitions tables to sum the totals in these tables (i.e. the total number of Pens coming in from all of the orders etc.). These are called StockOrderTotal and StockRequisitionTotal.
I am trying to create a query/report that will allow me list all of the Stock items and the current balance (i.e Orders minus Requisitions) for each item. However, once I add the two queries together (StockOrderTotal and StockRequisitionTotal) the final query/report only lists stock items that are found in both of the underlying tables.
Is there any way that I can tell the query to return all stock items and only list the Order and/or Requisition totals that are in the underlying tables? Sort of like an 'if' statement in Excel, where the value returned will be zero if that particular stock item is not found in the Orders or Requisition tables?
Thanks
Rich
View 3 Replies
View Related
Nov 6, 2014
I am using Access 2010 and I am fairly new to it (started using it for work).
I have a table with a bunch of a agreement numbers (134) and I need to link each one to its folder on our company's shared G drive. The agreement numbers all follow the format A12.## (Where ## is the number of the agreement going from 1-134).
So I have the path to get to the location of all the agreement files, and I know there has to be a faster way than to edit each individual hyperlink, I myself just don't know how to do it.
Also the files on the shared drive start with the A12.## but then also continue with the name of the agreement. I was thinking that somehow for each record you could apply all at once the same path to the agreements and then for each individual record get it to search for its closest matching agreement number?
View 5 Replies
View Related
Aug 26, 2014
problem is that i have 2 tables - Consolidated Orders, Customers.
Customers has 2 fields with a record inside one field with the clients name , but it also has an address in another field in the same table which i want to link to the name of the client because in my invoice i show the companies name and to the left of it i show their address.
i wish to link the two fields together so that when the name shows up, the address of the client will be to the left side .
View 2 Replies
View Related
Nov 8, 2012
I am working with a large database of 8,000+ entries. We have five tables in this database, but mainly use three of them. The main table is "OAKE Main Table" which is basically a list of all members our organization has had. Each member has accounting data linked to their member ID number which has been generated by ACCESS, and can be accessed or edited by clicking on a sub table next to each member. The accounting data that has been manually entered in the OAKE Main Table can be found in the Accounting table.
Here's my issue. I need to be able to extract a list of transactions from this accounting table since January 1st. I can do this no problem, but all I get is the member ID and the accounting data. How do I get ACCESS to include the member ID's first and last name? or maybe even the first, last name, address, city, state, zip, etc so that I can get a list of people who have made donations, for example?
I'm assuming this will have something to do with the relationships we have set up in our table. Here's a picture of it. I have no clue how to make this work.
View 8 Replies
View Related
