Tables :: Field For Multiple Sales On Single Record

May 7, 2013

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.

View Replies


ADVERTISEMENT

Tables :: Update Single Field From Multiple Fields

Nov 6, 2014

I wrote a database several years ago and recently pulled it out to give to a friend. The problem is, back then (not knowing better) I set the Employee table up with as a single field "NAME". Now, in order to make it effective, I need the Employee's name in four (4) parts (First, Middle, Last, Suffix).

I have several queries based on the "NAME" field and and would like to avoid changing all of them. I have a simple form "frmUpdateEmployees" that populates the Employee table and Name field. I was hoping to change the form and/or add a query that would be easier and more simple.

Table: Employee
Field: Name
Form: frmUpdateEmployees
Queries: 16 that depend on the table and field above.

View 5 Replies View Related

Queries :: Two Tables To Result In Single Column With Field Name Identifier For Each Record

Apr 27, 2015

How to get this one to display in a single column.

I know how to do this wiht VBA. But, this output will need to reside on a SQL Server View. So I need a SQL language solution. If it can work in MS Access Query, it won't be too difficult to test then translate to SQL Server.

Customer Table with PK Customer_ID.

There are two tables with FK Customer_ID.

1. Table Lease1 - Has 3 Fields - the form code enforces No Fields -or All Fields. The red * indicate a Required field - These 3 are entered together.

2. Table Lease2 - Has 1 field with 0 to Many records.

Goal:

The Type shows up in a single column.

Each Type shows where the data comes from (Lease Type, Surface Owner, Mineral Owner, or Hz Lease Type)

Challenge:

Lease1 table has 3 fields that need to be transformed into a single column.

Lease2 table has 1 field to be appended to the single table.

Then, there is the column that identifies where the data came from based on the column name.

View 6 Replies View Related

Tables :: Sales Database - Multiple Employees Per Client

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

How Can I Create A Sales Form That Can Update Both The Product And Sales Tables

Oct 11, 2007

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 Related

Tables :: Consolidating Multiple Tables In One Single Table (Sorted Ascending By Time)

May 30, 2014

What I have is a database setup with multiple tables in which different areas of my DC can input information simultaneously into their respective tables. I then have another database linked to it for myself to have a live view of each updated record. I would like to see all the records of each table in 1 single table (possibly just sorted ascending by time). Each table has the same Field headings but may have different qtys of records. As I will then have it linked to an Excel table to VLOOKUP from it.

I have tried Union coding but always get Syntax Error etc.

View 8 Replies View Related

Tables :: Export Multiple Tables Into Single MS Excel 2010 Workbook

Feb 10, 2015

I'm using the MS Access 2010 ExportwithFormating action to export three tables to a single MS Excel 2010 workbook. The action overwrites the first excel worksheet each time instead of saving all three worksheets in a single excel workbook.

How can I export three tables into a single excel workbook.

View 1 Replies View Related

Multiple Notes - Single Record

Jul 20, 2015

So I have a list of jobs and each job is subject to change from the original plan.

Each change needs to be documented and dated. So what I want is a form where the site can be selected and a note written. When this is saved, a record of the current time would also be needed.

The form aspect of this seems fairly straight forward to me. I just cant visualise where the 'notes' will be stored.

View 2 Replies View Related

Single Form, Multiple Tables...

Apr 11, 2005

Sorry for the newbie question but...

I've got a form called 'frmStudent' which is linked to 'tblStudent'. I have designed it as a tabbed form, the first tab has "Student name", "Student Number", etc. The second tab is linked to visits by the student to a mentor (such as a tutor etc). Here I'd like to have "Date of visit", "Mentor Name", etc.

So far I've been able to get the first tab to work (I'm guessing because it's linked to 'tblStudent'), but can't get my 2nd tab to work as I have no clue on how to link it with 'tblVisits'. Is there a VBA methor or perhaps a nother method to do this?

I'm attaching a link to the sample of what I'm doing (please shift-open into frmStudent to view what I'm talking about). Any ideas and suggestions would be greatly appreciated!

DOWNLOAD DATABASE:
http://www.myfilestash.com/userfiles/sabotage1945/Student_Help.zip

Kindest regards,
Sab.

View 4 Replies View Related

Charting Multiple Values From Single Record

Feb 11, 2008

This particular database generally looks at reporting individual records as opposed to summary reports. So for a particular data record i have the following data [x1][x2][x3][x4][x5][y1][y2][y3][y4][y5]

I need to chart (scatter) these figures as paired x,y variables for display on a feedback report such as

x1,y1
x2,y2
x3,y3
x4,y4
x5,y5

For some reason i simply can't wrap my head around haw to make this happen.

Many Thanks in advance for any help.

Cheers
ndeans

View 2 Replies View Related

Saving Multiple Values To A Single Record

Oct 22, 2005

I want to have a box with yes/no choices defining the Lessons a Student chooses. Sth like this
Because the Lessons are quite plenty and they change from time to time, I don't want to have them predefined in the Sudent table, but to have a related table containg the Lessons providing more flexibility.
My problem is how to assign the Yes/No values from the form to a single record. ANy suggestions?
A first thought is to create a List from Lessons table, with 2 columns. 1st the Lesson and 2nd the Y/N box. Every Lesson would have a value like this:
1,2,4,8,16,32,64...
With this way the sum of the Lessons would be unique for each combination and it could be saved in a single record. But such a solution is not so practical.
Any alternatives?

Thnx in advance

View 1 Replies View Related

Assign Multiple Names To Single Record

Jul 18, 2013

I have a Project Database where I keep track of projects throughout my plant. A project can have one owner or several owners working on it. Is there a way to assign multiple owners to a project?

I tried doing a subform but I don't like how it looks on the form and when I try to do a query that filters based on a person's name, the form has to be a single form rather than a continuous form which is not what I want.

Is there another way that I can accomplish this?

View 1 Replies View Related

Data From Multiple Tables On Single Form

Oct 14, 2004

I have a tabbed form that contains multiple rows of data just like the following: (it's a service checklist for a store where the user makes choices from the combo boxes: e.g. good, fair, poor, etc)

Paint (txtHrs), Action (cboPaintAction), Condition (cboPaintCondition)
Carpet (txtCarpetHrs), Action (cboCarpetAction), Condition (cboCarpetCondition)
Counter (txtCounterHrs), Action (cboCounterAction), Condition (cboCounterCondition)
etc.

Since the combo boxes describe data that relates only to that element I can't put all the data needed into a single table. There are 40 of these rows so does that mean I have to create 40 tables? If so, how do I add the data to them from the form? Any help with this problem would be greatly appreciated!

View 2 Replies View Related

Tables :: Set Up Attendance Database That Has Multiple Status Available For Single Day

Dec 9, 2012

I 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.

View 6 Replies View Related

Tables :: Database With Multiple Entries For A Single Date?

May 17, 2013

We have two facilities that receive four different types of shipments. I wanted to set up a database to track these shipments. Is there a way for me to set up tables such that the person entering the information can leverage the same date and location field for each entry type? For example, we may only receive two types of shipments today and four tomorrow. I want to avoid having the clerk enter the date and location two times today and four times tomorrow for each data entry.

Here's an example of what I mean above:

Date Location Type Qty
5/16 1 A 10
5/16 1 B 1
5/17 1 A 1,000
5/17 1 B 100
5/17 1 C 1
5/17 1 D 11

I'd like for the clerk to select from a fixed number of locations (1 and 2) and a fixed number of types (A, B, C, or D) and provide the date and quantity received.

View 4 Replies View Related

Adding Records Into Multiple Tables At A Single Moment

Dec 2, 2011

Supposing you have 4 Tables

1.)User
2.)Admin
3.)Teacher
4.)Pupil

Now the "User" table has the ff. fields: ID(auto num),FName,MName,LName,User,Pass,Type.

Now what I want to do is, if I add a new a record in the table "User" and if the record has the data "Admin" in the field "Type" then the record should also be saved in the table "Admin" but if the data is "Teacher" or "Pupil" then it should be also saved in their respective tables.

Question is: Is that possible? If yes, then how??

View 5 Replies View Related

Queries :: Return Multiple Rows From A Single Record In Database

Jul 16, 2014

For each record in my database, there are observation periods which are recorded in the format dd/mm/yyyy hh:mm:ss, titles as follows

1st Obs Start
1st Obs End
2nd Obs Start
2nd Obs End
3rd Obs Start
3rd Obs End.

I have been asked to create a query that will quickly show how many obervation periods commenced in a particular month. What I am trying to do is create a column that will be named Obs Start, and another, Obs End. For each record ID, this would then show as follows:

ID......Obs Start.............Obs End........
1....[1st Obs Start].....[1st Obs End]....
1....[2nd Obs Start]....[2nd Obs End]....
2....[1st Obs Start].....[1st Obs End]....
2....[2nd Obs Start]....[2nd Obs End]....
2....[3rd Obs Start].....[3rd Obs End]....
3....[1st Obs Start].....[1st Obs End]....
4....[1st Obs Start].....[1st Obs End]....

etc.

I could then quickly count how many obs periods started within the desired month.

View 14 Replies View Related

Forms :: Update Multiple Tables From A Single Form / Page?

Jun 27, 2013

My database allows you to log issues (see attached)

An issue will belong to the project, the project may have multiple test plans.

The issue will also be the primary responsibility of a company, and that company may have multiple contacts (people)

Now, i could create a form over the top of each table but then if someone wanted to add a new project, testplans for that project, suppliers (companies) for that project AND new contacts for those companies, they are going to end up going through loads, and loads of forms.

So my question is, what approach would you recommend to create a control panel where they could add these items with the fewest number of page switches possible? (preferably none.

View 1 Replies View Related

General :: Merging Data From Multiple Tables Into Single Column

Sep 10, 2012

I've created a access database containing multiple tables, theres one main table that will contain all the data compiled.

I then have other tables that contain some matching data but with an additonal column filled in,

I want to be able to pull the data from these other tables into the main table and compile it including merging some data into a single feild.

Effectively this is what the inputs will be:

Code:

Table 1
Ident Number Solution TRN
T0941A1 SLN019149 EFE0008
T0941B1 SLN019149 EFE0008
T0941C1 SLN019149 EFE0008

Code:

Table 2
Ident Number Solution TRN
T0941A1 SLN019149 EFE0167
T0941B1 SLN019149 EFE0167
T0941C1 SLN019149 EFE0167

Code:

Table 3
Ident Number Solution TRN
T0941A1 SLN019149 EFE0188
T0941B1 SLN019149 EFE0188
T0941C1 SLN019149 EFE0188

Then the main table i want to compile the data like this:

Code:
Table 4
Ident Number Solution TRN
T0941A1 SLN019149 EFE0008,TRN0167,EFE0188
T0941B1 SLN019149 EFE0008,TRN0167,EFE0188
T0941C1 SLN019149 EFE0008,TRN0167,EFE0188

i want it to show as 1 line item and compile the TRN numbers into a single feild per row rather than having duplicates.

View 1 Replies View Related

Multiple Answers For A Single Field

Nov 12, 2004

Hi, this is my first post here.

My question is this: I am creating a database for a student survey. Some of the questions may have multiple answers.
For instance, question 2 reads:
"What location/time are you taking courses (select all that apply)"

and the possible answers are:
1)Daytime/Oakland
2)Evening/Oakland
3)Saturday College
4)BC

- Where a student could be taking classes at multiple locations or times. I am completely lost as to how to set this up. I have tried to set the properties for the field on the form to accept multiple responses, but of course, these do not save to the table and so it's completely useless.

Thank you in advance for your help!!!

View 6 Replies View Related

Multiple Choice In A Single Field

May 20, 2013

I have question regarding multiple choices fields. I would like to store constrained but multiple values in a single field. I used the assistant to create the multiple choice field in the table, but I'd like to have it displayed in my form with check boxes (more readable in my tabular form) instead of drop down list. Is it possible ...

View 6 Replies View Related

Multiple Parameters [for The Same Field] For A Single Query?

Nov 23, 2005

I have set up a parameter query in Access 2003 that asks the user for the "Section", such as "Admin", "Accounting" etc.

I need them to be able to respond to the prompt with more than one section if they want- sometimes just one, sometimes two or three or four.

So they can get "Accounting" and "Admin" both in the records that are returned.

The code:
SELECT T_ElainesMaster.Section, T_ElainesMaster.Login, T_ElainesMaster.Workstation, T_ElainesMaster.NT, T_ElainesMaster.Barcode, T_ElainesMaster.[PC model], T_ElainesMaster.[Emp Name], T_ElainesMaster.[swap or not], T_ElainesMaster.[Exp Date]
FROM T_ElainesMaster
WHERE (((T_ElainesMaster.Section) Like [What section would you like to include?]))
ORDER BY T_ElainesMaster.Section;

Thanks!

View 1 Replies View Related

Concatentate A Single Field From Multiple Records

Nov 17, 2006

I have a table of every reading of every electric meter. This history goes back a couple of years.

Field1 = date
Field2 = Meter#
Field3 = Single digit code identifying method of reading

The same meter is read multiple times in a year. The number of readings in the database is 1 or more (no limit).

I want to create a single line of text in 1 field that shows a history of the reading method of a particular meter.

Reading method translations
A=Radio Frequency
B=Manual
C=Not Read
X=Estimated


Here's a sample of what I would like to see .....

View 4 Replies View Related

Dynamic Multiple Criteria In A Single Field

Apr 5, 2007

I have a query that displays all records. I need it to limit based on multiple criteria in a single field. I.E... instead of all employees from every section, I just want it to display employees from section A, C, D, F one time and next time maybe go with section B, C, D. For some reason the solution eludes my little brain.

View 3 Replies View Related

Add A Single Field Multiple Times In A Form

Oct 3, 2006

I hope someone can help me on my problem below:-

A user wanted to key in the data in a single form where she can select where
a particular procedure is located. The procedure could be duplicated and
placed in a few departments.

My tables are as follows:-
tbl_Proc_Dept:-
ProcNo - Text (Primary)
TrgConducted - Yes/No
DeptAbbv - Text (Primary)

tbl_Dept:-
DeptAbbv:- Text (Primary)
DeptDetails:- Text

I created a form where i have the following fields in the form:-
1. ProcedureNo - Text
2. TrgConducted - CheckBox
3. MainDept- ComboBox (DeptAbbv data field)
4. Finance – CheckBox (DeptAbbv data field?)
5. Admin – CheckBox (DeptAbbv data field?)
6. Purchasing – CheckBox (DeptAbbv data field?)
7. Facilities – CheckBox (DeptAbbv data field?)
8. MIS – CheckBox (DeptAbbv data field?)

How can I add multiple deptabbv field in a single form?

Pls help.

Thanks.

View 1 Replies View Related

Multiple Links On Single Database Field

Dec 28, 2006

Hi, this question is more about Microsoft Access than it is about ASP. However it involves a database that I am setting up for a website being written in ASP.

I have a table which is going to store business details. Every business belongs to a business category and related subcategory. For example Garages and MOT Centre.

I want to store up to 5 categories for each business. Is it possible to link to the categories and subcategories tables for each of the 5 category fields in the business table?

Thanks, Steve (Blackpool)

View 1 Replies View Related







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