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]
I have a tblCustomer table to track customer information. One field is for flights the customer books. I expect that a customer might book more than one flight, which should affect what he owes. (My tblFlights table includes fields for the cost of each flight.) How should I make the Flight field in tblCustomer so it accepts multiple flights and reflects on the customer's final bill and my own accounting tables that I haven't even created, yet?
I'm probably overthinking everything. This is just an Intro to Access class, and I only need 3 tables with 1 relationship. So far, I have 9 primary tables, 4 duplicate tables (for M:N relationships) and 11 relationships. I have to have tables for financial data, customer info and product/service info. I wasn't sure what I would need for my fictional airline, so I created tables to track everything I could think of, hoping to cover all 3 required types of data in the process.
Am creating a Product-Sales Database, and I would like the corresponding Sales made in the Sale Table to be automatically deducted or to be reflected in the Product Table. The product table contains all my stock and has a relationship with the Sales Table. The Sale Table does not necessarily include the Stock. How can I create possibly a Sales Form that will be used as an entry point for all the products (stock) sold and automatically register the sold products in the Sales Table and at the same time make the required adjustments in the Products Table.
View 1 Replies View RelatedHi 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
Hey guys-
I am importing data on a daily basis- some of it contains existing records, some of those existing records have updated information, and then the rest will be new records. I am importing into a table, say, tbImport.
I have 3 tables I am splitting this info out to- tbListings, tbAgent, and tbAgentOffice. Each record can have up to 3 Sales Agents on the file- Agent 1, Agent 2, and Agent 3. Each Agent has their own Employee ID, as well as Office ID, Mobile, Home, Pager numbers. Each Agent can belong to only ONE Office, but every office can have multiple agents. I am using the Agent's Employee ID (it's assigned by another SQL db- and I will never be assigning this freely to anyone else, or new agents- they come into the DB already assigned a unique code).
Agent 1, 2 and 3 can freely change positions (Agent 1 can be Agent 3 at times, etc etc) from record to record. However, I want to store all their contact info into a separate table, going for 3rd Level Normalization...
So what I have is this-
tbImport
_____________________
fldInfoID
fldWhatever
fldAgent1Code
fldAgent1Name
fldAgent1Mobile
fldAgent1Pager
fldAgent1Home
fldAgent1OfficeName
fldAgent1OfficeCode
fldAgent1OfficeFax
fldAgent1OfficeNumber
fldAgent2Code
fldAgent2Name
fldAgent2Mobile
fldAgent2Pager
fldAgent2Home
fldAgent2OfficeName
fldAgent2OfficeCode
fldAgent2OfficeFax
fldAgent2OfficeNumber
fldAgent3Code
fldAgent3Name
fldAgent3Mobile
fldAgent3Pager
fldAgent3Home
fldAgent3OfficeName
fldAgent3OfficeCode
fldAgent3OfficeFax
fldAgent3OfficeNumber
**plus a ton of other data- but you get the idea here
AND I want it to go into tables like these 2-
tbAgent
________________
fldAgentCode (Primary Key)
fldAgentName
fldAgentMobile
fldAgentPager
fldAgentHome
fldAgentOfficeName
fldAgentOfficeCode
fldAgentOfficeFax
fldAgentOfficeNumber
tbAgentOffice
_________________
fldAgentOfficeCode (Primary Key)
fldAgentOfficeName
fldAgentOfficeNumber
fldAgentOfficeFax
So- the tbAgent table will store all the contact info for every agent, and tbAgentOffice stores info for their offices. I know I'll have relationships from tbListings.fldAgent1Code (as well as Agent 2 and 3 at the same time) to the tbAgent.fldAgentCode. I'll also have a relationship from tbAgent.fldAgentOfficeCode to tbAgentOffice.fldAgentOfficeCode. So, when I do daily imports- I will need to run an update query from the tbImport table into the other 3 tables at the same time. My question is this-
How do I cram the individual fields for all 3 agents, into one universal field in the tbAgent table at the same time? I can't get my mind around the concept here. How will it take all the fields for each agent in the tbImport table- and cram them into 1 record set (while checking for existing/update records) in the tbAgent table? How do I write that in the sql code to tell it to link Agent2Mobile into the same Record as Agent2Code, and not into the Agent1Code or Agent3Code? Right now it seems to me that there's no way to distinguish between the Mobile Number for Agent 1 2 and 3 compared to their own Codes to identify the records with. Does that make sense?
Any help you can provide is EXTREMELY appreciated!
Attached i have a Database with 3 Tables.
-Table MA's. is the just a table were you can select if an Analyst is at the office or not. so if the check box is selected the analyst is available.
-Table Backups. We have Alias which is a reference code to a supplier the 1 Analyst and then Backup 1 and backup 2
-Table suppliers are just de supplier details.
what i whould need is when in the MA's table an Analyst is not selected a macro or query should search for backup 1 and replace it. when the backup 1 is not available as wel than backup 2 should be put in place.
When no backups are availeble then a message can put in the cell like"No Backup Available'.
This result can be put in a new table named. Decksplit with then the Supplier Alias, Supplier Name, Analyst
Can a Append Query move all my data stored in multiple tables to another database with a identical table structure?
Because as I try to work the query, I keep getting prompted to "Select a table" I want to append to, and I don't want to append to just one table...
I'm developing a database that tracks monthly metrics of employees. These employee are at several different locations.
Here's what I would like:
A table of the employees and their location.
A table of the metrics.
A form that you can select an employee and it would autofill the location and then you could fill out the metrics.
the ability to change an employee's location without it effecting past records.
A report, by month and location of the employee's metrics. (pretty sure I can do this on my own, just can't get to this point.)
I've tried to use a auto lookup query but that then changes the employees location on past records.
design a database in access 2007.
employee personal recordemployee job statusleave recordinventory record issue to employeejob performanceinventory control of equipmentforms, queries, tables, login forms.
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 RelatedI'm trying to set up a simple query that links four tables. However, the tables are extremely large, all in excess of 1.5GB each so I had to split the tables up into four separate DBs. I've tried the following with no success:
1) Link the 4 tables in the DB which contains my primary key. This quickly inflates increases the file size above 2GB and won't let me go any further.
2) Build a remote query to connect the four tables. This looked promising until I tried to run the query and it became evident that it only knows to point to the last database source that you specified.
I'm running everything locally on my C drive. The data source are simple text files (1.6 million rows) from the FDA website.
I am creating an employee database for a client. The employee table has a performance column for each employee, and my client wants to be able to choose from four performance codes - Excellent, Good, Average, and Poor. They want to choose these descriptions instead of typing them in each time. How can I make this happen?
View 7 Replies View RelatedI 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
I am working on an Insurance DB.. I have two tables tbl_Clients and tbl_PolicyDetails with a One-Many relationship.. tbl_Clients have the Clients basic info, Name, Tel, Address etc. The tbl_PolicyDetails have the details of the policy for the customer like Start date, policy term, amount they pay, policy status etc. Policy Status can be any of the following Quote, Active, Suspended, Lapsed, Cancelled, Terminated..
One client can have many Policies.. When one policy finishes (like when cover upgraded/downgraded/any changed their insured property address etc.) we categorise them as Termination of old policy thus the client will have a New policy.. At any stage we only need the newest policy per client. My problem now is I need to list all Clients with policy status Active, Suspended, Lapsed, Cancelled..
Code:
SELECT tbl_Clients.cli_ID, tbl_Clients.firstName, tbl_Clients.address, tbl_PolicyDetails.policyCover
FROM tbl_Clients INNER JOIN tbl_PolicyDetails ON tbl_Clients.cli_ID = tbl_PolicyDetails.cliID_FK
WHERE tbl_PolicyDetails.policyStatus = "Active" Or tbl_PolicyDetails.policyStatus = "Suspended" Or tbl_PolicyDetails.policyStatus = "Lapsed" Or tbl_PolicyDetails.policyStatus = "Cancelled";
Code:
cli_ID firstName address
1 Paul Bournemouth
2 Mark Bristol
3 Lauren London
4 Caryn Devon
[code]...
When a Client wishes to Cancel a policy it is not Terminated, the policy status is just Cancelled.. Similar to Lapsed policy, if the policy hits 30 days of no pay then it is passed on to a Debt collection agency.. If in future the Client comes back a new policy is generated for both cases.. So in this case Client Lauren and Caryn will have been repeated twice, in the report I am trying to generate (based on the above query).. I do not wish to display the Cancelled policy if the customer has another policy which again can be Active, Suspended, Lapsed, Cancelled..
Code:
cli_ID firstName address policyCover policyStatus
1 Paul Bournemouth PDB Active
2 Mark Bristol PDB Active
3 Lauren London PDBCH Suspended
4 Caryn Devon PDE Active
i would like to know how to replace the sales values in the quarterly sales report with tax values so as to create a "Quarterly tax report" in Northwind.
View 1 Replies View RelatedI 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 2 Replies View RelatedI have developed .net application ....at back end I am using ms access database. When I deploy my application at client end , I want only my ms access database should accessible to application only. Client should not be even to see which type of database i am using or its business logic .
View 7 Replies View RelatedI have created multiple tables in my database and now for some reason it keeps telling me it can not find a few of my existing fields.
View 5 Replies View RelatedUsing VBA (in Access), what code should I use to check if a client name already exists in a table.
Say, client name is "Smith", my table is called tblClient and the field name in the table is called fldClientName.
I just need to do the check, that's all.
Hi,
I have a problem with form design,
I want a form like in Northwind sample database: Summary of Sales by Year Report.
It use Sorting and Grouping for Footer that I can't find it in Form design.
Is it any other way to do it in Form design so I can get the same result like in Report design?.
The reason why I want it, because I want to control the size.
thankyou in advance for your help.
Gunawan.
I have created 2 tables: Client and Routing.
Each client record is unique. A client can have 'several' routing records.
Key field in Client is 'Client ID'.
Routing table has foreign key of: Client IDFK
I created a relationship of one to many from Client table 'Client ID' to Routing table 'Client IDFK'.
I created a form for the Client table and works.
Where I am having issue is: Client data is not populating into the following Routing form.
* I want a 'Routing form' that you can lookup client info and place it into that form.
* The bottom of the form will be all the routing table fields. The new routing info will be entered into it.
My client needs that form printed for the driver. Client will have many routing forms(records) but only one client record. The driver will have one completed form for each time he picks up client.
I have two tables(see below). I want to set up a query, link these 2 tables together. I set a one-to-one relationship between Client ID in two table. But got error message :"Type mismatch in expression".
I tried to change Client ID data type from "Text" to "Number", then Access deleted some data under Client ID in Order table.How can I make this work, but not having to re-type in all data?
Client Table:
Client ID(Autonumber)
Client Name (Text)
Client Address (Text)
Order Table:
Order ID(Autonumber)
Client ID(Short Text)
Unit Order(Number)
Unit Price
I am trying to create a table which would include financial data, by year, by client. For example:
Client #1
2009 2010 2011
Revenue 5000 10000 1200
Expenses 2000 1000 700
Net Income 3000 9000 500
Client #2
2009 2010 2011
Revenue 5000 10000 1200
Expenses 2000 1000 700
Net Income 3000 9000 500
Etc.
Should I have headers as such:
Client #, 2009Revenue, 2009Expenses, 2009NetIncome, 2010Revenue, 2010Expenses, 2010NetIncome, 2011Revenue, 2011Expenses, 2011NetIncome
Im working on a cross comparison database for the three food vendors for my boss. What is the best way to search across the three tables? They all have similar record titles but arent all uniform, and I cant change the records because I have to have the record names the same as the files that are being exported from the vendors websites. What would be the easiest way to search across three similar but not uniform tables in one database? Ive also tried to make three buttons to update the lists after I download the items to have up-to-date prices on the products but am unsure if they are actually working correctly.
View 3 Replies View RelatedI need to set up an attendance database, that has multiple statuses available for a single day.Example: employee can be present, he can have a sick leave, he can be away on training, or business trip, etc... And for some of those statuses, like business trip, i need to be able to freely enter a comment, stating where he is etc..
Anyway, the key is that this database should be able to offer a "headcount" option, and traceability for past statuses for at least a year, for every and all employees. Now i just need to set up the database tables and relationships.
I am creating a training database and i have a courses table. And I need to somehow record all of the different employees that attended a course?
What i can do to make this happen? My employees have an employee number so is there a way of putting all of the employees together in one field? what field would the attendees be? currently is numeric which doesn't seem to work.