Over 1 million tech questions and answers.

How to combine fields in Access 2010 based on 2 equal fields?

Q: How to combine fields in Access 2010 based on 2 equal fields?

Hi all,

I?m really in need of some help from some Access experts! I?m a newbie to Access, but I got myself busy with quite a big project... I?m trying to map out the product supply to shops in the whole country. I?ve come quite far for a starter, but I keep having 3 questions, it would be great if you could help me with this!

I have, amongst other tables, two tables called Stock_counted and Reported_quantity.

The table Stock_counted has the following fields:
- Shop_number
- Product_code
- Quantity_counted

The table Reported_quantity has the following fields:
- Shop_number
- Product_code
- Quantity_in
- Quantity_lost_or_returned
- Quantity_out

1. What I would like to do is to make it one table based on Shop_number AND Product_code. So in one record I would like to have Shop_number, Product_code, Quantity_in, Quantity_lost_or_returned, Quantity_out and Quantity_counted. How do I do this, making sure that the quantities end up behind the right product and shop? It is possible that some shops don?t have all items, while other shops might not have all products reported, or both. On top of that, I have another table with a price for each product, which needs to be related to it as well.

2. It happens that some products are found more than once in one specific shop and therefore appear more than once in the table, with the same shop number and product code. How do I automatically SUM these quantities, so that this product only shows up once per shop?

3. Finally, I have some products which are the same but have slightly different descriptions and therefore different product codes. Is there a way to link them all under one (existing, one of the descriptions) code without having the other codes showing up anymore?

I would really appreciate it if anyone can help me with this, I?m desperate!!

Thank you so much,

Josephine
Access 2010 user

RELEVANCY SCORE 200
Preferred Solution: How to combine fields in Access 2010 based on 2 equal fields?

I recommend downloading and running Reimage. It's a computer repair tool that has been proven to identify and fix many Windows problems with a high level of success.

I've used it in the past to identify and fix everything from blue screens (BSOD's), ActiveX errors, corrupt files and processes, dll/exe/sys errors, recover lost memory, Windows update problems, defragging, malware removal etc.

You can download it direct from this link http://downloadreimage.com/download.php. (This link will automatically start a download of Reimage that you can save to your computer.)

A: How to combine fields in Access 2010 based on 2 equal fields?

Quote: Originally Posted by Josephine87



2. It happens that some products are found more than once in one specific shop and therefore appear more than once in the table, with the same shop number and product code. How do I automatically SUM these quantities, so that this product only shows up once per shop?



select shop, product, sum(quantityfield1), sum(quantityfield2)
from sometable
group by shop, product

Read other 1 answers
RELEVANCY SCORE 110

I have one table that has information on all my gears, called tblGearFeed. This table has 3 columns of information (CAM, Feed, and Gear A)

I am using a form called frmQuotation.

I have "CAM" and "Feed" fields that users will fill in. Based on these two fields, I need the "Gear A" field to lookup the cooresponding data from tblGearFeed.

I have been trying to use the following function:

=DLookUp("[Gear A]","[tblGearFeed]","[CAM]=Forms![frmQuotation]![CAM] And [Feed]=Forms![frmQuotation]![Feed]")

This formula is not working.
Can anyone guide me as to what I am doing wrong?
 

A:Solved: Access- DLookup based on two fields

If you do an AND it will only bring up the values in each field that equal your parameters. Use an OR instead.
 

Read other 3 answers
RELEVANCY SCORE 108.8

Hey there again, I've come across another issue with my program:
I have a search form that allows the user to select a piece of equipment. A macro passes a value (EquipmentID) to a new form, and all of the fields are suppose to draw info from the table based on EquipmentID.
My problem is getting the fields to automatically update once you change the value. I started with using the ResourceSource of the forum, but users can cycle through enteries and once the Equipment ID changes the form doesn't update. So I'm down to several options:

Somehow use the ResourceSource correctly
Use a macro/code to fill the info in
Fill in a invisible field and pull the info from there

If you've got a suggestion on how this should be done, or you could let me know more on what's actually happening in Access, please let me know.
 

A:Access 2003: Autofilling fields from a Query based on a Value

The form, if it remains open does not get the new Value unless you "Requery".
To test if this is the problem you should be able to use Main Menu>Records>Refresh,
if that updates the form with your new value then that is the problem.
To me it would make more senese to have the New forma a Subform to the first form, even if it is as a Tabbed Subform.
 

Read other 1 answers
RELEVANCY SCORE 105.2

Hello,
I am fairly new to database and I don't know much about VBA coding.
I am creating a database and I have created a form for end-users to submit create a new record. but I want all the fields except one to be required.
I tried to make them required in the table but it gives me error when I enter data through the form. Can anyone please help me with this.

I have the following fields to be required

First name
Last name
Date of birth
Department
Employee level
employee status
Employee ID
GID
Hire date
E-mail
Phone number
Model ID

Thank you in advance
 

A:Access 2010 Require Fields

Read other 6 answers
RELEVANCY SCORE 105.2

Hi,
Here is my problem. I have a table with details of enrolled students. Many of the students have the same name with only an initial or middle name to differentiate them.
I have a field each for forename, initial/middle name and surname in the table.
I need each field to be able to accept duplicate entries but where both forename and surname are identical, the initial/middle name must be a required field.
Problem is, not everybody has a middle name, so I cannot set the field property to required. I need it to be required ONLY in the case that the forename and surname are identical.
Anybody got any ideas?
I tried using ID, forename, initial/middle name, surname as PKs but that doesn't work.
Please explain in simple words, I am not a technical expert, just an admin.
Regards
Nick
 

A:Access 2010 unique fields

Read other 8 answers
RELEVANCY SCORE 105.2

I cannot change entry in ID field of Facilities Table due to 1-1 relationship to Test Data Current Table. I just want to modify it from "70100" to "AI-70100".
 

A:Modifying Fields in Access 2010

Is there just the one record that you want to change?
 

Read other 3 answers
RELEVANCY SCORE 104

Experts,
Usually iin MS ACCESS 2010 when you create a field in a table for attachement, the sub fields are there in the " Add Fields" top a form but this time after entering the field of Attachment in a table , i dont see any of the subfields. Did I miss something here.
Your help is appreciated.
 

A:Solved: MS Access 2010 sub fields attachment

Sorry, I have never used Access 2010 or it's Attachments field.
 

Read other 2 answers
RELEVANCY SCORE 104

I have a Table in Access 2010 which has several true/false fields. Is there a way for me to pull a report that lists which fields are true for each record while not listing the other fields?
 

A:Access 2010, List True Fields

Read other 8 answers
RELEVANCY SCORE 102.8

Now in the form, only Names is updated in table tblOrders. I want to update BizUnit in tblOrders too, what should I do?
 

A:[ACCESS 2010]2 tables with same 2 fields but only one field updated.

Why do you want to add the BizUnit to the Orders table?
I notice that you also have emails in there as well, they also should not be in there.
Only data relating to the Order should be in the orders table.
The data is then brought together using a Query for Output purposes like Reports & Invoices etc.
 

Read other 1 answers
RELEVANCY SCORE 102.8

I am using Access 2010, I have certain fields on the form that need to be entered in Arabic and certain in English. I have set the keyboard language for the text box to be "Arabic" (for text in Arabic) and "English" for text in English. When I do this, it takes a little more time to move between fields. How can I overcome this problem. In Office Xp, I never had this problem.
 

A:Access 2010 - Moving between fields takes time

how did you set the thext boxes to use different languages? I have never done this so I need to configure the same to test, also have you gone through all the settings and advanced settings of language Bar, Regional settings etc..

how much more time are you talking?
 

Read other 3 answers
RELEVANCY SCORE 102.8

You guys have been a huge help in the past with issues I've had with Software Licensing database I created in Access, so I'm hoping you can lend a hand with a new issue I'm running into:

i copied some data from a table to do some comparison and clean up in Excel. I realized a little late that I should have pulled more fields than I did... I have done the clean up and now I'd like to get the additional fields to finish the job. I've imported the cleaned up data as a new table (Table A) and want to run a query against the original table (Table B) to pull those additional fields. Not all of the records in Table A are present in Table B, and vice versa.

In the query design, I show Tables A and B and join the First Name, Last Name and Application fields. The Join Properties are to include all records from Table A and only those from Table B where the joined fields are equal. Table A has 1606 records, so my hope is to have the query return 1606 records with the missing fields added. I'm bringing in all of the fields from Table A and adding the missing fields from Table B.

The query returns 1899 records. I ofound one user who only has four records in Table A, three records in Table B, and the query is returning 12 records for them.

What am I doing wrong?
 

A:Access 2010 - Adding fields to a table using a query

Read other 16 answers
RELEVANCY SCORE 101.6

Hi,

I got a form A and a subform B. I use B to display data retrieved from a table while use B to simutaneously update that data. More particularly, if I click a row in B, that row will be display on A and the user will be able to edit that data. The forms were designed by someone in MS Access 97. I converted it to MS Access 2003 format. Therefore, I have to link form A and form B. However, I couldn't find Link Master Fields and Link Child Fields in MS Access 2003!

Any ideas? Thanks in advance!
 

A:Link Child Fields and Link Master Fields in MS Access 2003

climbingrose said:

Hi,

I got a form A and a subform B. I use B to display data retrieved from a table while use B to simutaneously update that data. More particularly, if I click a row in B, that row will be display on A and the user will be able to edit that data. The forms were designed by someone in MS Access 97. I converted it to MS Access 2003 format. Therefore, I have to link form A and form B. However, I couldn't find Link Master Fields and Link Child Fields in MS Access 2003!

Any ideas? Thanks in advance!Click to expand...

It really sounds like form A and subform B are both using the same table, and are not a Master/Child field link situation. But rather, form B (the subform) will have code in the "On Current" event that will find the current record on the form B within the form A, therefore allowing that record to be edited. Review your original post, I think you used the "B" one place where you meant to say "A". No matter what, you need to clear up with us out here if both form A and form B are using the same table.

Vic
 

Read other 1 answers
RELEVANCY SCORE 99.2

I need to combine 27 tables with same record ID's but all tables have different fields. How can I do this in ACCESS 2007? Many thanks.
 

A:Solved: combine tables with different fields in ACESS2007

Read other 14 answers
RELEVANCY SCORE 99.2

MS Access:
I have several fields in a form that uses two tables. I would like to set up a command button so that when you are on a current record, it will copy the field information from the record you are on to other fields in the same record. I do not know the SQL commands to set this up.

For example, there are name fields, address fields, and phone to be copied. Reason: One is contact information; one is survey information. In most cases the information is the same but on rare instances, the information needs to be entered (when different).
This is not an update for all records in the database. This is based individually on each record.
Not sure if you need to do On current, on click, or after update.
Not sure if default value in form can be used.
Please help.
 

A:Copy data fields in same table to other fields in same table MS Access

Read other 16 answers
RELEVANCY SCORE 98

I am trying to create links to specific locations with each document between two documents. The document names will change. I can update the current name of the other document by creating a custom property called "otherDoc". I have solved most problems, however, I am unable to insert a field into the address of the link (to make the link dynamic) without utilizing the showfieldcodes command (which is very memory and time consuming since the documents I am working on are very large). Below is my code. I was wondering if there is any way to set the address as a field? Or somehow input an address and it would be understood by word to be field.
Sub link_to_other()

'pastes hyperlink to the other document
Selection.PasteSpecial Link:=True, DataType:=wdPasteHyperlink, Placement:= _
wdInLine, DisplayAsIcon:=False

'selects the hyperlink that was just inserted
Set myField = Selection.PreviousField

'creates variables
Dim temp_address As String
Dim temp_subaddress As String

'sets variables
temp_address = ActiveDocument.CustomDocumentProperties("otherDoc").Value & ".doc"
temp_subaddress = Selection.Range.Hyperlinks(1).SubAddress

'changes the hyperlink the the dynamic value
Set SCut = ActiveDocument.Hyperlinks.Add( _
Anchor:=Selection.Range, _
Address:=temp_address, SubAddress:=temp_subaddress)
End Sub

 

Read other answers
RELEVANCY SCORE 92.4

Hi

I'm working in Access 2000. I created a database in which you select a course number and the course name automatically appears in the "course name" box. I created this database but cannot for the life of me figure out how I did that.

Now I need to do it again...I've looked at the properties of the fields in the form and everything, and cannot find where you point it to fill in one field based on a another.

Can anyone help me with this?

thanks
Bobbi
 

A:Access2000 -Automatically fill in fields based on a selection

Read other 6 answers
RELEVANCY SCORE 92.4

I have a form designed to track processing times for various activities. This information is entered from a Production Log that the employee completes throughout the day. The main form has the following fields: Name, ArrivalTime, DepartTime, ProductionDate. After the user enters data into these fields, the cursor moves to the subform where the activity id is entered, the start and end times for that activity, the beginning & ending sequence number, and several other fields.

Since some activities don't require entry into all fields, I would like to disable certain fields based on the value entered into the activity id field. I have 4 activity ID's that I want to trigger the disable event on 3 other fields in the subform. I need to know how to code this, where to code it, and then to check in the form's BeforeUpdate event that the above is correct.

Thank you for your help!
 

A:Solved: Disable Fields on Subform Based on Value of Another Field

Read other 10 answers
RELEVANCY SCORE 90.4

Every day I send out excel documents that I then convert to pdf's. I use the same excel template on each document. The only thing that changes in each document per recipient is the "name" "company" & "email" fields in the excel document.

Question: Is there a way I can get these documents to interface with outlook so that when I select more than one person to receive the document (as many as 30 people will receive the same document), and auto populate the contact info "name" "company" "email."

This way would save me a few steps so that I don't have to pick a contact... type all their info in the fields... send. Select my next contact... enter appropriate info in the fields... and repeat and repeat.

I'm emailing job specific information to everyone working on the same job. I'm sending it to multiple people, but need it to look like they were the only one who received the document. Just like when you get an email about the "deal of the week" that says, "Dear Mr. Johnson, How's your summer going?.... " while someone in NYC gets the same letter that says "Dear Mr. Thompson, How's your summer going.... " from the same sender. Of course the deal of the week changes each week.

How can I make this happen? If there is a better way outside using excel to create pdfs, or a totally different method entirely. I'm all ears. I just need to send the informatio... Read more

Read other answers
RELEVANCY SCORE 87.6

Is it possible to create user defined fields in outlook and then make them sortable?
We would like to Categorize clients by
By type and office
Be able to sort by office
By Client Level
By Portfolio type
Thanks,
Jerry
 

A:User Defined Fields Outlook 2010

Read other 6 answers
RELEVANCY SCORE 84.8

Hi,
I've been searching all kinds of fora for this problem and although I find information on how to do this with (legacy) Form Fields, my problem involves Content Control fields and I can't seem to fix this.
Basically I have Sales Reports that are being filled out on a weekly basis by all Sales Reps. These contains all kinds of different Content Control fields (Drop down, rich text, dates etc...), and the data in there should be copied to an excel sheet. Meaning: copying the contents of the fields from all the different reports into 1 excel report.
Below is a Macro I found which works for legacy forms (FieldForm) (pre-2007) but I cannot seem to make it work for 2010. I pinned down the problem to being just a wording problem in MS, but then again, it might be a little more complicated

Many thanks for your help,
Code:

Dim vField As FormFields
Dim fso As Scripting.FileSystemObject
Dim fsDir As Scripting.Folder
Dim fsFile As Scripting.File
Dim wdApp As Word.Application
Dim myDoc As Word.Document
Dim vColumn As Integer
Dim vLastRow As Integer
Dim x As Integer
Sub AddFormFields()
vLastRow = ActiveSheet.UsedRange.Rows.Count + 1
vColumn = 1
Set fso = New Scripting.FileSystemObject
Set fsDir = fso.GetFolder _
("Q:\Sales Reports\Unprocessed")
Set wdApp = New Word.Application
wdApp.Visible = True
For Each fsFile In fsDir.Files
wdApp.Documents.Open (fsFile)
Set myDoc = wdApp.ActiveDocument
For Each vField In wdApp.Documents(myDoc).FormFields
vField.Select
vV... Read more

A:Exporting data from Content Control Fields in Word to Excel 2010

Read other 6 answers
RELEVANCY SCORE 84.4

how many total fields can you have in an access table
I'm using access 2000
 

A:access fields

I believe the limit is 255, but I am not 100% sure. Even so, you will most likely never have that many fields in one table...
 

Read other 1 answers
RELEVANCY SCORE 83.2

I have this table that I want to have a drop down list for the builder and then the next field another drop down list that only lists those tracts that the chosen builder has. I am trying to create a form right from the table
 

A:Access lookup fields

Please read this previous post
http://forums.techguy.org/business-applications/485383-solved-access-lookup-based-previous.html
 

Read other 1 answers
RELEVANCY SCORE 83.2

I have two tables tEp and tLn.

I have a field in tLn for tEp but I need to be able to select multiple values, Ive followed guys on office and here: http://www.databasedev.co.uk/multivalued-fields.html

But I cant get the Allow Multiple Fields checkbox!! It just doesnt show!?

Why?
 

A:Access Multiple Value Fields

Worked out this only in accdb, is it possible to do something similar in mdb?
 

Read other 1 answers
RELEVANCY SCORE 83.2

I must be having a brain fade here but what i want to do is real simple, just can't get it right.

I have a field on an MS Access form and all I want to do is check if something has been entered into it.

Something along thw line of :-
if field is blank do then
do something
else if field is equal to spaces then
do something different
end if

of the life of me I can't get it to work :-(
 

A:Blank fields in Access

I'm presuming this is just pseudocode for a corresponding Vb project.

If so then i recommend:

Option Explicit

Dim wksp As Workspace
Dim db As Database

Private Sub command1_Click()

Dim entry As Recordset
Dim search_string as string

search_string = txt_search_string.text

Set wksp = DBEngine.Workspaces(0)
Set db = wksp.OpenDatabase(App.Path & "\DATABASE NAME.mdb")

Set entry = dbfacilities.OpenRecordset("SELECT * FROM [TABLE NAME] WHERE [FIELD NAME] = '" & search_string & "'")

If entry.RecordCount = 0 Then
MsgBox ("The field is blank")

txt_search_string.Text = ""
txt_search_string.SetFocus

Else
'unsure of what you wanted here...
End If

Hope this helps.
 

Read other 3 answers
RELEVANCY SCORE 83.2

Ok, i have a form that has a calendar...when you click on the "hiredate" or "birthdate" the calendar appears, and when you click anywhere on the detail area, it disappears...however, whenyou change a date on the calendar you must click in either the "hiredate" or the "birthdate" for it to take the change....i would like to make this automatic, so that when a person clicks the calendar the coresponding field...hiredate or birthdate, is changed....is this done with the "on update" event? any help would greatly help my grade!
if so could someone give me an example?

-Craig
 

A:Access: One Calendar for Two Fields?

Yes, well, a couple of questions, so that our solutions change your environment the minimum amount possible:

1. Are you sure that the calendar that appears is on the same form? Or is a special calendar form appearing and disappearing? You might want to poke around in the db window, and/or post back the details (macro or code behind the events, for example, on click) for hiredate and birthdate.

2. Also, what's behind the calendar? The tricky part of what you're talking about is having the same control (or form containing a control) for two different text boxes; that's why you're having to click in the appropriate box, I'm guessing: to tell the calendar which box to put its value in.

I'm guessing that you didn't build this initially? If these questions seem to complicated, just zip up an example version of the related table(s) and the form(s) in an .mdb file and send them to me. (Must be under 1MB.) But if you can post back the details on what's going on behind your controls, we can probably give you a faster response.
 

Read other 3 answers
RELEVANCY SCORE 83.2

Here's the situation:

I have been given several access databases with contact information in them. The contact information has all the usual info (address, phone, etc.); however, the name format is two different fields: a first name, and a last name. My goal is to import all the database information into ACT!

I was planning on doing this by exporting a text file with comma seperated values; the only problem, though, is that the existing ACT! database doesn't have two name fields, and instead only has one "Contact" field.

Sooo.. my question is, would it be best to try and convert the names to one full name in access, or is there some way to import and convert them with ACT!? And, whichever way is best, how exactly would I go about doing that?

Thank you very much!!
 

A:Merging two fields in Access and/or ACT

Read other 7 answers
RELEVANCY SCORE 83.2

I know how to concatenate field values in a record but I'm wondering if it is possible to concatenate field values in separate records. My reason is that I have a form showing data from a query made from two tables. I want only one record in the underlying data and if I join two tables where one has multiple matches then there will be multiple records in the query. Table 1 has a record with joining field f1 (no duplicates allowed) and Table 2 has multiple records for f1 with different f2 values. Here's what I mean:
T1 has f1 = 90000
T2 has
f1 = 90000, f2 = T
f1 = 90000, f2 = Y
f1 = 90000, f2 = N

Is there a way to set up a query operating on T2 to give a table with the value
f1 = 90000, f2 = Y,T,N

If so, then a join on f1 with this table would yield only one record for the form I display. As a fallback position should I just do a summary query on T2 and show the resultant records in a small subform?

Thanks in advance for any advice on this?
 

A:MS Access Concatenate Fields

Read other 6 answers
RELEVANCY SCORE 83.2

Hi,

I am currently building a shop type database...

I have made a 'transaction form' On the form is things such as transaction ID, customer details, staff details etc, then there is a subform, where the products on the transaction get entered into.

On the subform (Transaction/Product link Subform), there are the fields (among others...) 'Product ID', 'Price' and 'Quantity', I have then made an unbound field to calculate the subtotal which has '=[QuantityOrdered]*[p_Price]'

On the main form, I then wanted to have a 'total' field, whereby it adds up all of the subtotals from the subform, the problem is is that through the various methods i have used i keep on getting #Error or it will only show me the subtotal of one record, when I need it to show me the total subtotals from all records.

Any solutions? please

A:Calculated fields in Access

... it will only show me the subtotal of one record, when I need it to show me the total subtotals from all records.If I understand correctly what you said, I think you need to make a calculated field on the form which will say something like=Sum(the name of the calculated field on the subform)and put that total field in the footer of the form. The footer should contain form totals.

Read other 4 answers
RELEVANCY SCORE 83.2

I am building a query in Access 2007 and when I select and drag a field from a query into the lower section of the building query screen it only brings the one field (like I want), but when I run the select query I am getting all the fields in the original query. How can I get it to only show the one field?
 

A:Access Query too many fields

Ensure that the query does not contain a column with the heading tablename.* or queryname.* where tablename and querynames are the names of your query table or query.
The "*" means all fields.
 

Read other 2 answers
RELEVANCY SCORE 83.2

In Access 97 SR-1 is it possible to create a lookup field in a table that is also a required field? I have created a table in Access containing several lookup fields. Now the user is requesting that the fields also be required - that the person doing the input via a form would not be able to advance to the next record before choosing an option in each lookup field in the current record. Setting the required switch to "yes" after the field has been created as a lookup field does not prevent the user from advancing to the next record in the form.

Please advise any suggestions. Thanks!
 

A:Access 97 required fields

You can set the "lookup" field back to a standard field and use a Combo box to do the look up for you.
 

Read other 2 answers
RELEVANCY SCORE 83.2

I have a form with Qty, Price, Total, MTD(MonthToDate), YTD(YearToDate). I am only storing Qty and Price in the Table. I can get a sum for Total, but how do I get a sum for MTD and YTD?

Should I have a field in my table for the Total, MTD, and YTD?

What is the best way to set this up?
 

A:Summing fields in Access

Read other 10 answers
RELEVANCY SCORE 82.4

I have a fully functioning dbase with the last Calculation that I cannot work out.

I am attaching a sample Dbase to help w the explanation.

I would like the Bonus Total to reflect all Totals. After totaling rate and standby total, I then want to choose my services and total out the Services total and refresh the Bonus Total with all of the updated info. I have stared merciless at this and tried several ways to make this happen.

I am slowly going mad. This is the last calculation then I am finished with this but have gone blank.

If anyone can help me, I would appreciate it.

As you tab through the dbase you will see that the Base rate is divided by 2 to get the standby rate and this reflects automatically in the Bonus Total in the top half of the dbase. Then you progress to service selections and the total is reflected upon pressing enter in the Services Total. This Total is what I want added to the existing Bonus Total that is calculated in the top of the dbase.

Sorry if I am repeating myself.

Anyone?
 

A:Solved: Access, Calculated Fields, OBP?? Anyone?

Bonus total has the calculation =[cboRateTotal]+[cboStbyRateTotal]+[cboServicesTotal] as it's control source.
Once you do that you can no longer change the value using VBA.
So the answer is to replace the =[cboRateTotal]+[cboStbyRateTotal]+[cboServicesTotal] with the VBA version.
So either set the control source to nothing or make it a field in the table.
 

Read other 2 answers
RELEVANCY SCORE 82.4

Our objective was to get the field to populate throughout the datasheet table. Meaning that

Field 1 Field 2 Field 3 Field 4
1111 Contract Apples Carrots
1111 Contract Soups Celery

Getting and Maintaining Field 1 & Field 2 to stay the same throughout the table
Since my brain is fried today and its already the middle of the week. Any suggestions?
 

A:Access: Record Keeping. Same Fields

Read other 12 answers
RELEVANCY SCORE 82.4

I have generated an access report that shows the process comments for the different locations we have in the organization. What I need to do is to count the total number of comments we have per day for the daily report I generated. I used a textbox under the gourp footer with the expresson "=Count([Comment])". It worked fine to get the count of teh comments under each goroup. I need to gererate a grand total for the report summing the values of the textbox with the above expression. Also, is there a way to count the text fields one time as a grand total for the report. Thanks in advance.
 

A:Summing calculated fields in access

The unbound text box that you have used for the "=Count([Comment])" should have a name like text2, so you use the following in a another unbound text box in the report footer using the correct name for your counting text box

=sum([text2])

I am not sure about your last question "is there a way to count the text fields one time as a grand total for the report"
 

Read other 2 answers
RELEVANCY SCORE 82.4

I have to concatenate the following by item number: I have attached a file with a sample table.

item number / memo subject / memo text
15056 / x/ e
15056 / t / r
15056 / s / q

there is also the issue where I have ~;~ in the memo text field. This is coming from some type of code in our ERP software. I would like to have this removed and not added in the final result which would be something like this:

15056/ x t s / e r q
I do not need comma's in between the data but if the field is blank i don't need that either.

I have researched and I have tried to do some VBA with Modules but I really don't know what I am doing.... Help!
 

A:Solved: Access - Concatenate fields

Read other 16 answers
RELEVANCY SCORE 82.4

In an existing database, there are fields that have text I would like to separate (like first name and last name both in same field). Can this be done and how?

Thanks for your help!
 

A:Access - Can you separate text in fields?

Yes. You should probably start by reading up in Access help on the following functions:
InStr: Looks for a specific character, and returns its position in the string (data in text fields is called string data
Left([FldNm], x): Returns the leftmost x characters from a string
Right([FldNm], x): Returns the rightmost x characters from a string
Mid: Used to return part of a string, beginning from a midpoint, continuing a certain number of characters or to the string's end.

Access Help will explain to you how each one works in more detail. Basically, you test a field for a space using InStr; InStr will return 0 if it doesn't find one, or a position number if it does. If my name is "Joe Kool", for example, InStr will return 4. You then use Left() and Right() or Mid() to separate out the strings.

Let us know what more we can explain.
 

Read other 1 answers
RELEVANCY SCORE 82.4

I will be working with an extremely large database and am wondering if the maximum number of fields,in MS Access (255),is absolute. I may also have several tens of thousands of records. Can the program be tweaked to allow for more fields? Is it RAM dependent?
 

A:Maximum Number of Fields in Access

IMHO:

I strongly suggest that you never create a table with that many fields. Instead, think about how you are laying your data out. Access is a relational database where specific data should be kept in individual tables.

For instance, a payroll database should contain several tables, including: personal employee information (empID#, SSN, Name, Address, etc.), payroll info (empID#, PayLevel, amount of pay or range of pay), gross pay by period (empID#, date paid, gross amount, taxes withheld, net amount), etc., and they would all be related by the empID# field.

By doing this, you should have a much more manageable amount of data in multiple tables.

You can email me a sample of your data if you want. Just make sure there's column headings and about 100 records would be great.

I've emailed you a document about "normalization," a term used to describe the way to set up tables in a relational database. Hope it helps!
[This message has been edited for tactfulness by Dreamboat (edited 03-29-2000).]

[This message has been edited by Dreamboat (edited 03-29-2000).]
 

Read other 2 answers
RELEVANCY SCORE 82.4

Hi, I receive an excel file everyday that I need to import. However, the column header of the fourth column of data changes daily (it's the corresponding day's date). I would like to import/append the data to the previous day's import. Is it possible to import and append fields by their position number, e,g, the fourth column is not called using its column header but is called by its position, say "field4"(?).
 

A:Access: querying fields generically

PMCD, welcome to the Forum.
I don't think what you want to do can be done with just one Import query because of the name change and Append Queries use the field names.
You can Import the Data to a temporary table, change the name, append the data to the master table and the run a delete query to remove the data from the temp table.
What you could have is an SQL statement renaming the field, although I am not sure if SQL can handle the field name change, and then run the append query.
You can definitely use VBA code to change the name and then run the Append query though.
Or you change the field name manually prior to running the Append query
 

Read other 1 answers
RELEVANCY SCORE 82.4

I produce my departmental purchase plans on MS Access so that I can attach my own symbols eg prioritising and run queries. I have been told that I can transfer/link/export individual fields from Access into a template order form set up in MS Word. Is this possible? How is it done? Is there an easier way?
Any suggestions?
John.
 

A:Linking Access Fields to Word

Hey there.

Is there some reason a mailmerge from Word won't work?

www.thewordexpert.com/word.htm

Check out how to set up a mail merge.
 

Read other 1 answers
RELEVANCY SCORE 82.4

I have a table in an Access database. This table has three columns in it, and I want to add a fourth column. I want the value of this fourth column to be "Primary" for every record.

What's the easiest way to accomplish this? I'm an Access newbie.

-- Richard
 

A:Auto-fill access fields?

Read other 6 answers
RELEVANCY SCORE 82.4

have two tables like

Staff Table
............
Staff ID
Staff Name
Age
...and more

Staff Salary Table
..................
Staff ID
Staff Name
basic Salary
...and more

Now I have created the Staff Salary form in which I got the staff ID from the staff table using the lookup wizard query in the Staff ID field of the Staff Salary Table
SELECT [Staff Table].[Staff ID] FROM [Staff Table];

but I need one more

When The staff ID was selected from the drop down list, I need the relevant staff name (from the Staff table) also be displayed in the staff salary from .

Can you please let me know how to do this..
 

A:Getting the data from different fields [ MS Access problem ]

Read other 12 answers
RELEVANCY SCORE 82.4

Aloha FRIDAY Tech ppl!!
I've set fields in MS ACCESS 2010 table to be required fields --- BUT it "kicks" me out the form IF i try to save the record to test the field by leaving the field blank!!

Were should I enter the parameter to ask me to enter the required field then SAVE the record ...
thank you!!!!!
 

A:MS ACCESS table required fields

The table is the correct place to do this.
I am not sure what you mean when you say "save the record" as Acces automatically saves the record.
What happens if you try to close the form yourself, or move to another or new record?
 

Read other 3 answers
RELEVANCY SCORE 82.4

Hi all,

I am making a report in Access 2003. I have salaries that will be displayed by the month. However, since we are only in April, the months May-December are not in the query that the report is based on. Is there a way that I can make the report display a 0 in the place of the months that aren't yet there? The months that are there will display the salaries.

I appreciate any help! Thank you!
 

A:Access 2003 non-existing fields

Are you using a Crosstab query for the Report?
If so you can force the Headings to be entered by typing them in the Haedings Property of the Query.
 

Read other 3 answers
RELEVANCY SCORE 82.4

Hello All.

I am working on a piece of code to check what field names are in a query and then I need to pass those field names into another query.

I found this code here on the forum and it gives me field names but I need to skip a few known field names as well.

Any ideas?

Sub FieldNames()
Dim Rst As Recordset
Dim f As Field

Set Rst = CurrentDb.OpenRecordset("qryAccessorial_CrossTab")

For Each f In Rst.Fields
MsgBox (f.Name)
Next
Rst.Close
End Sub
 

A:Getting fields names in Access 2007

Read other 13 answers
RELEVANCY SCORE 82.4

I was wondering if there was a way to jump to a specific field in a Access database from VB6 using an SQL statement or something simliar.

Any help on this matter would be greatly appriciated,

Thanks in advance,

Dayne
 

A:Jumping to Fields in VB6 from Access Tables

Read other 8 answers
RELEVANCY SCORE 82.4

I have a form with many subforms. The main form and subforms are linked by the access generated ID numbers. However, whenever I try to input data into one of the subforms I get the message, "The LinkMasterFields property setting has produced this error: Object or class does not support the set of events" I am not sure what that means, but it hasn't affected the form that I know of. The data still saves within the subforms. Anyone know how to remedy this problem?
 

A:Access Link Master Fields help!!

Read other 7 answers