Seperating A Field By Another Fields Value

May 4, 2006

Hi All,
I am a novice at MS Access but I am learing new things every day.
I am using MS Access 2000 for this DB.
If you need to see the DB let me know and I will strip the sensative data out of it and post or send a copy. The DB is for a small retail sales store.

What I want to do is something like this::
If [Tables]![Sales Entry]![Item Tax] [= $0.00] Then copy [Tables]![Sales Entry]![Item Price] to [Queries]![Taxable and Non]![Non-Taxable] [Else[If [Tables]![Sales Entry]![Item Tax] [> $0.00] Then copy [Tables]![Sales Entry]![Item Price] to [Queries]![Taxable and Non]![Taxable]]] End If

I want to split up the Item Price field by checking to see if Tax was applied or not and copy the Item Price to the proper new fields in the query called "Taxable" and "Non-Taxable". Then in my Report I can sum each of the new fields by date.

Thanks
Ron

View Replies


ADVERTISEMENT

Seperating Data In A Single Field

Mar 10, 2007

Sorry for this totally stupid question but my memory is just not what it once was.

I need to separate one field into two, in a table that was imported from excel

Basically it has a size and a unit in each field

Example:

24.67 sy
26.84 sf


Thanks in advance for jogging an old man's memory.

I want to do this in a query not code if possible I remember something about a formula you can enter into the update to box in a saved query.

View 1 Replies View Related

Seperating Table From Program

Oct 20, 2004

I have several tables that I would like to be seperate from the forms. Bottom line is that I want the program stuff to be seperate from the tables seperate as in two different files.

I have not messed around with this yet and am wondering how big of a pain this will be to do with the code that I have already written?

Any thoughts ideas etc.?

View 1 Replies View Related

Value Of One Field Changes ANother FIelds Value

Oct 21, 2005

I am trying this new project out, and i have two fields set up, Both will be drop down boxes.
One field is Airline, the next Aircraft Type

If the user clicks on the British Airways dropdown link under the "Airline" field, i would the the Aircraft type field to automaticaly pull up only those Aircraft types
that british Airways flys.

Could anyone help me out?

View 2 Replies View Related

Changing To Field Value Instead Of Fields.

Dec 14, 2006

Help needed: Large Yes/no tables!

I have a form with names of employees, and different skills that employee has.

It is listed in a form as columns with the employee name in rows, and columns for the skills. The form is in fact really great, but I see that the solution might bring problems in the future because of maintainance issues, and field limitations. I add fields frequently, and the table is growing without me beeing able to control it.

I have organized this in only one table, and used the name of the skill as a field. There is many other tables in the DB, but none of them covers this area.

exampletable:

employeeID | auto
employeename | text
Hireddate | date/time
Fireddate | date/time
baking | yes/no
cooking | yes/no
driving | yes/no
juggling | yes/no

With this solution I get the skillname as a heading, and the name as a column with lots of yes/no values. I love the filter functions, and the form is quite nice...


exampleform:

Employee baking cooking driving juggling
employeeeID1 yes no no yes
employeeeID2 no yes yes yes
employeeeID3 no no yes yes
employeeeID4 yes yes yes no

and so on.

Is there a way to organize this so that the skill name comes in the top row, the results get listed as shown above when the skill is placed as a field value, not a field itself?

I have looked for a solution with many-to-many relations with 3 tables, but cant seem to make it work right. It is especially the form I have trouble creating with the new design.
I have also tried to make crosstab queries with minor success.

Does anyone have a suggestion on how I can make the relations work.
One employee can possess a great number of different skills, and the skill can be possessed by a great number of employees. This is not a difficult issue by itself, the problem is to make the form as shown over.



Thank you, in advance.

View 1 Replies View Related

Merge Fields Into One Field

Aug 14, 2005

I have a table and would like to merge all the fields into one text field of the table after the insert. I'm using adp (access/sql server).

When using standard sql the null values give a problem and i don't want to use the if clause as it will give too much coding.....

View 1 Replies View Related

Compare 2 Fields To Get 3rd Field...

Apr 13, 2006

Hello,

I have 2 tables:

tblProviders
ProviderName
ProviderAddress
ProviderType
LocationCode
FacilityType
Etc...


tblFacilityType
ProviderType
Location
FacilityType

In tblFacilityType, the FacilityType field is a combonation of ProviderType and Location. Each of those fields are alphanumeric. What I want to do is have the FacilityType field in the the tblProviders table to look at the Location and the ProviderType fields in the tblFacilityType table to determine what the FacilityType field in the tblProviders table should be.

Hope that is understandable.

Thanks in advance for any assistance.

View 5 Replies View Related

Field Value Based On Other Fields

Nov 30, 2005

Here is what I am trying to do.

I have a table with routes that tell me which carrier to use. This is based on from what state to what state it is traveling as well as the service lever (next day, standard, etc.)

How would I code for the value in the carrier field to populate based on the state and service level values entered.

Should I write a query to get the results? Or should it be code in an event on a particular field?

Whichever way works, any ideas on how to get started?

Thanks.

Gary:confused:

View 3 Replies View Related

Having A Field Calculate Other Fields

Aug 15, 2007

I am trying to get one of my fields to calculate this expression which includes other fields:

If "Financing Type" = 1 or 2 THEN 20% * "Loan Amount" OR
If "Financing Type" = 3 or 4 THEN 100% * "Loan Amount" = 2,000,000.


I thought that this would work, but it only works in queries or reports.

=IIF([FinanceType]<3,[LoanAmount]*.2,[LoanAmount])

I would like this to work in the table.

View 11 Replies View Related

Tables :: Several Fields On One Field

Oct 3, 2012

I would like to have a field data type lookup data from another table but display them on this one field. ie on member information table there is a FirstName & LastName fields separately I would like another table with Member field lookup member information and pull both FirstName and LastName fields on one field. How do I go about this?

View 1 Replies View Related

Auto Populating Fields From A Different Field

Apr 19, 2006

Hi there,

I've been working on this issue for quite sometime. I've tried different methods, such as the DLookup function, but I think I'm doing something wrong. Let me explain what I'm trying to do:

This is a Work Order, the user will Select a Customer (Table Customer: Contains CustomerID and Customer Name, address, etc) from a drop down box. When they do this, I have another textbox which is designated for the Address. I would like the address to automatically populate. but its not quite working. I've tried it with the DLookup function (I entered it into the control source of the textbox). I entered the following syntax:

=DLookup("Address","Customer","CustID=" & cboCustId)

It keeps coming up with an error. I have no idea why.

Now this is just the first half. The second half I have a subform on the same main form, which lists the equipment they have on their site. The database has a number of customers with many equipment for each of them. How do I make sure that the subform only populates equipment associated with the Customer that was selected on the main form?

I hope someone is able to help me.

Thanks a lot!

View 3 Replies View Related

Validating A Field (dependent Fields)

May 28, 2005

I am trying to put in a validation rule so that data in one field cannot be less than that in another.
e.g. i want to make a validation rule so that a phone call end time cannot be before the phone call start time.
Can anyone help???

View 5 Replies View Related

Can I Merge 2 Txt Fields To 1 Memo Field

Apr 6, 2006

Hi,

I have an old database for generating service reports which has:
Report No
Date
Customer
Site

In separate fields which is fine

but also

wrk1
wrk2
wrk3
etc.

with a separate field for each printed line on the page
I would like to merge all these into one memo field while retaining the previous data which goes back 15 years (it came from a MSWorks 2.0 DOS db which is the reason for the weird structure I think)

Is there any way to write a macro or code to selectively merge some but not all fields in a table?

I've already copied the db and modified it after clearing all the old data, it works fine but I'd like to have access to the old data in the new format

TIA

Kim

View 3 Replies View Related

Adding Fields Into A New Field Drives Me Mad.

Dec 4, 2007

So I have a table containing 2 fields containing the first name and last name of a customer. I do need to add these names in the same table into a new field called Name. Can someone help me out?

View 5 Replies View Related

Comparing Two Fields In One Criteria Field

Aug 9, 2005

Hi,
I just started doing something in access and need your help.
I have created a database and need to create a query which will sort all important fields within a certain time period. I have a date field and in the criteria field I wrote an expression:">Forms![Insert_date_form]![starting_date_field] and < Forms![insert_date_form]![ending_date_field]"

Its purpose is to show all fields which date is between this to dates. User will have to enter this dates in a separate form(Insert_date_form) which will store it in a small database containing only these two fields, e.g. generate report of all employees that are started working within a period between starting_date and a ending date. When I try to create and open a report based on this query I've been prompted with a small dialog which says: Enter parameter value.... for starting_date_field and same for ending_date_field. I don't need that. These dates are allready entered by user in a insert_date form and stored in its databese.

Is there a beter way to do this?

Thanks!
P.S. Sorry for my poor english. It's not my native language.

View 8 Replies View Related

Update Field Based On Other Other Fields

Jan 5, 2006

drvRegion either contains "EUROPE", "AMERICA" , "ASIA" or is NULL.

if drvOrderSource="Whatever" THEN update drvRegion with "EUROPE", "AMERICA" , "ASIA"

Right now I'm getting a circular reference error :/

please advise. Thank you.

View 5 Replies View Related

Breaking Down A Field Into Multiple Fields

Dec 19, 2007

I am attempting to create a report that breaks down a field of 'ClaimID' numbers into groups of x. In the sample report below x = 12 and the report will apportion the first 12 'ClaimID's to the first page and textboxes with extra large fonts will signify the start and end of 'ClaimID' numbers for that page. These sheets are used for sorting and pulling guides at our local Xmas project and x will vary depending on the size of the facility we're using.
http://i35.photobucket.com/albums/d186/HoodRiverDude/SortReport.gif
I believe I can attain my goal if I were able to create a query which broke down the field 'ClaimID' into multiple fields based on x. The sample below represents this breakdown creating multiple records with x, 4 and 5, amount of fields.
http://i35.photobucket.com/albums/d186/HoodRiverDude/SortReport2.gif
Does anyone know how I can create this query, or perhaps suggest another way to create this report using the existing 'ClaimID' data.

Thanks in advance,
Aldo

View 2 Replies View Related

How To: Separate Data In One Field Into 2 Fields

Mar 18, 2005

I am new to Access and was wondering if there was a way to parse the data from a field into two fields.

I have a field named tName which contains both the first name and last name of a person.

How can I get the corrosponding last name into a new adjacent field called tLastName (this field doesn't exist yet)?

Thanks so much in advance,
Paul

P.S. What if the the person doesn't have a last name? or if they included a middle initial?
I know, it's messed up. This DB has a bad setup.

View 2 Replies View Related

Combine Two Text Fields Into New Field?

Aug 20, 2004

Hi,
I am trying to combine 2 text fields into a new field (concatenate) so that I can use it as the primary field.
Can anyone pls help.

View 3 Replies View Related

Generating A Field With Parts Of Other Fields

Jan 1, 2008

If I have three fields

Surname - text
Given name - text
Dateofbirth - dd/mm/yyyy

how can I generate a field consisting of

surname(then)first letter of given name (then) year of birth

(ie SMITHK66)

Thanks in advance

View 2 Replies View Related

Queries :: Getting Information From One Field To Two Fields

Dec 20, 2014

I have a table that contains Investigator1,Investigator2 and other fields

And another table that contains PID, Rank_Name_Family and other fields

I am making a query in that query i want to get the names of Investigator1 and Investigator2

Investigator1 and Investigator2 is the PID

for example

Investigator1 = 1
Investigator2 = 2

PID = 1 Rank_Name_Family= Roy Jalbout
PID = 2 Rank_Name_Family= Rony Jalbout

i tried this two solutions but the result is error

Solution 1

Code:
Name1: DLookUp([Rank_Name_Family];"tbl_Personal_Information";[tbl_Personal_Information].[PID]=[tbl_Driving_Expert].[Investigator1])
Name2: DLookUp([Rank_Name_Family];"tbl_Personal_Information";[tbl_Personal_Information].[PID]=[tbl_Driving_Expert].[Investigator2])

Solution 2

Code:
Name1: DLookUp([Rank_Name_Family];"tbl_Personal_Information";[tbl_Personal_Information].[PID]=[Investigator1])
Name2: DLookUp([Rank_Name_Family];"tbl_Personal_Information";[tbl_Personal_Information].[PID]=[Investigator2])

View 3 Replies View Related

Forms :: Add Two Fields And Store In Another Field

Apr 11, 2015

I have a form (frmFeeIncome) based on a table (tblFeeIncome)

On the form I have three fields which are FeesMonth, FeesYear, FeesUK (currency), FeesElsewhere (currency) and FeesTotal (currency). Each new record is entered like this

January 2015 25000 25000

What I need is for the total in the FeesUK field and FeesElsewhere field to be added up and stored in FeesTotal when FeesTotal has GotFocus

The FeesTotal has to be stored (I know it shouldn't be but it has to!)

I have tried the following in the GotFocus Event procedure but it does not work.

=[FeesUK] + [FeesElsewhere]

View 3 Replies View Related

Forms :: Sum Total Of 2 Fields On Third Field

Oct 22, 2013

I've tried many things (compact and repair, Nz(,0), sum()) and I can't get it to work.

I entered =[field1]+[field2] but it behaves like if I had entered =[Field1] & [Field2].

Yes instead of adding the numbers it just puts them together.

For example: Field1=3 and Field2=5 instead of field3=8 it shows Field3=35.

View 3 Replies View Related

Split Up Data In One Field Into Several Fields?

Feb 24, 2012

I have a field in an Access 2003 table that has several image names in it separated by "; " (semi colon and space). The thing is, I need to split them up into their own fields..My table name is "ONE BIG TABLE" (will be exporting data from one table for CSV).My starting field name is "ALT_IMG".An example of the contents of a record within "ALT_IMG" is

Code:
/AAG70260G05_2_1.JPG; AAG70260G05_3_1.JPG; AAG70260G05_5_1.JPG; AAG70260G05_6_1.JPG; AAG70260G05_7_1.JPG; AAG70260G05_8_1.JPG; AAG70260G05_4_1.JPG
What I need is to split these up into their own fields. I can create new fields to populate, I just need to get them in the fields and to remove them from the original ALT_IMG field after moved. There can be as many as 0 or 1 to 20 images in the ALT_IMG field.

Ultimately I was thinking about making new fields named "ALT_IMG_2", "ALT_IMG_3" (up tp 20) and then making an update query of sort to anything more than 1 image to the next field. Meaning if there are two images, then the first stays where it is and the second is moved to ALT_IMG_2. If there are three then the first stays where it is, the second goes to ALT_IMG_2 and the third goes to ALT_IMG_3.Now I know that there are benefits of having multiple tables but I need this to end up in the same table.

View 3 Replies View Related

Using Two Fields In A Form To Generate A Third Field

Oct 9, 2015

I've got a form where I want to add an employee and I have three fields (EmpFirstName), (EmpLastName), and (EmpCode).They all have their own control source from a table but what I want is EmpCode to automatically fill in after they type a first name and last name and not be editable. Smith, John and the empcode will fill in with smijo (3 of last and 2 of first). I understand this is probably completed using Expression Builder (not sure on the command) and how do I make it so Empcode still receives the data entered??

View 2 Replies View Related

How To Update A Field With Contents Of Two Other Fields

Jul 3, 2012

I'm an excel user and in using excel I can take two fields and combine their data in one field. I've got about 6 database files with anywhere form 1000 to 3000 records that have already been prepared and imported into access database files .mdb and I realize that the firstname lastname fields need to also be combined into a contact field with both names. In Excel it's easy in access I'm a little lost. I was looking to an update query however I can't find instructions on what I'm trying to do.

View 2 Replies View Related







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