Microsoft Access Forums - Queries

How to combine mulitple rows into single row in access using sql query

Joyce — Wed, 22 May 2013 21:56:50 GMT

Iam new to microsoft access.

I need to combine multiple rows into single row in access using sql query

Could anyone help me how to write the query?

Field 1 Field 2

A01 123
A02 234
A03 345

Result should be A01 123,234,345

Thanks in advance,

New Project -- can use some help

djclntn — Wed, 22 May 2013 21:04:11 GMT

I was given a new project which I can use some help. It�s a database which contains 50+ Employees &Records are kept containing the �Dates� employees worked & the �Hrs.� Their salaries/pay is of course calculated accordingto �Hrs� worked, but the problem is I need to give many employees an increasein their salaries by i.e. 6/1/2013. I knowI can�t just go in & change/update employees� salaries because that willchange ALL of the employees� salaries proceeding 6/1/2013. I�m not sure if this requires an Append Queryor an Update query. I hope that some canunderstand my question; it may be hazy?

Thanks in advance.

Queries, relationships and duplicate creation that I can't remove.

kcmiuser — Wed, 22 May 2013 20:26:32 GMT

Okay let me start by saying that I'm not an experienced access user, although I would like to be, but job duties don't permit me the time to really learn it. So there are a lot of areas that confound me, but I do use it occasionally to create small projects to make my department run a bit smoother. So the problem my having is as follows: I have two pass-through queries that are pulling data from a very large ERP database. I need to create a make table query that will use feels from both of these, the problem is now that I'm getting duplicates. The first query contains data that has to do with material requirements planning and action messages, I need all of the fields that are in this query for the make table; second query pulls inventory data but has a small amount of fields but these are necessary in the make table for creating parameters. I know I'm getting duplicates because I can see them in the make table record count and also see them visually, so do I have to create a relationship between these two queries in order to eliminate the duplicates without losing all of the message data from the first query-can you create relationships between queries or is that only for tables.. I can use any help anyone can give me because I'm in areas now that I just don't understand very well. Thank you

Query question

jerrypeco — Wed, 22 May 2013 19:14:15 GMT

Have an existing Access 97 database. I am trying to create a query (and ultimately a report) that will show line items purchased and a separate freight charge.

I am using a query that pulls records from 3 tables. All is ok except that my freight charges in their field �duplicate� in conjunction with the records obtained.

Order Date	Ship Date	City	Unit Price	Freight	Order Detail ID	Order ID
3/10/2013	3/12/2013	Atlanta	$310.00	$0.00	33418	18810
3/10/2013	3/12/2013	Atlanta	$0.00	$0.00	33417	18810
5/13/2013	5/14/2013	Spokane	$310.00	$280.00	33447	18819
5/13/2013	5/14/2013	Spokane	$0.00	$280.00	33428	18819
5/13/2013	5/14/2013	McAllen	$310.00	$115.00	33437	18820
5/13/2013	5/14/2013	McAllen	$41.41	$115.00	33434	18820
5/13/2013	5/14/2013	McAllen	$0.00	$115.00	33432	18820

The Order ID and freight come from one table and the Freight and the Order Detail and Unit Price come from another. Freight is listed only one time in the Order table but duplicates on the query and naturally the report.

Not sure if this is enough info, but can anyone point me in the right direction?

How to query data from junction table?

bigal0043 — Wed, 22 May 2013 19:11:09 GMT

In access 2010 and it is a web database. Not using multivalued option, using a junction table.

For example lets say I have three tables for a contact database where a person may belong to multiple countries.

Contact
id, name

Countries
id, countryname

Contact_Countries
id, contactid, countryid

I want to have a way for the user to view all the contacts and then select which country then want to only see.

total record in each Query to be show in the Main Dashboard form

YaseenIskaf — Wed, 22 May 2013 18:55:37 GMT

Dear All

I have a database with many Quires I want to create in the main form �welcome Form� many txt boxes cells
To show a total records in Each Quires
For Example I have the below list queries

Expired Employees Passport
Expired Employees Work Permit
Expired Employees Visas
Expired Employees Insurance

In the �Welcome Forms� I will create a txt boxes Cells for Each one of those Queries to be Show only No �total Record in Each one�
It is like a dashboard to monitor daily without opening the Report or Query

So, can I get a help

Thank you All

Top Score Per Branch - Can't figure this one out!!

LanieB — Wed, 22 May 2013 18:35:22 GMT

Hello Ladies and Gents!

My db is used for internal audits. I'm trying to run a query that shows the top score PER branch. I can't figure out what criteria I would need to get this result.
If my fields are as follows: BRANCH, USER NAME, OVERALL AVERAGE. What would I need as the criteria in the query to pull the TOP score PER branch?

Thank you all!

Lanie :)

Deduct sell products from receiving list

Georgi — Wed, 22 May 2013 08:01:24 GMT

Good day to all,

Please see attached file, I am trying to create a query that shows the rest of products in the store, I have so far 3 tables, Order Details, Products, Receiving Products.

Would you please help me?

Thank you in advance and have a great day.

Georgi.

What am I missing????

Loc — Wed, 22 May 2013 03:41:46 GMT

I want to build a query using 2 tables and have this information returned into a form. The problem is one of the tables has no data in it (OdrQty) when I run my query it shows no results but if I delete the table with no data it works just fine. Problem is this table will not always be populated with data right away and I need to have both tables up for my query to give the proper information. If I put data in the OdrQty table it works just fine from the tests I did. I do have a relationship between the two tables I just don't know what I am missing.

Here is what it looks like.
SELECT Parts.PartNo, Parts.Description, Parts.OnHand, Parts.ReOrderPt, Parts.MinOrder, OdrQty.Complete, OdrQty.OdrQty
FROM Parts INNER JOIN OdrQty ON Parts.PartID = OdrQty.PardID
WHERE (((Parts.OnHand)<=[ReOrderPt]) AND ((OdrQty.Complete)=No) AND ((OdrQty.OdrQty)=0));

Update query error

dccjr — Wed, 22 May 2013 00:04:41 GMT

I have the following sub to change the users passwork from the default to their entered one. However, when I run through the procedure I get a pop-up "Enter Parameter Value". I am not sure where it is coming from. It is suppose to get the new password from a textbox. Here is what I have:

Code:

Private Sub cmdChangePassword_Click()

Dim strInsertPassSQL As String

    If Me.tbxNewPass1 <> Me.tbxNewPass2 Then

        MsgBox ("Paswords do not match. Please try again.")

        Me.tbxNewPass1.Value = ""

        Me.tbxNewPass2.Value = ""

        Me.tbxNewPass1.SetFocus

    Else

        strInsertPassSQL = "UPDATE Secure SET [Password] =" & Me.tbxNewPass1.Value & " WHERE [EmployeeNo] =" & Me.tbxUserEmpNo.Value & ""

        DoCmd.RunSQL strInsertPassSQL

    End If

End Sub

Could one of you guys or gals point me in a direction?hanks.

DCount issue

RobWulf — Tue, 21 May 2013 21:23:55 GMT

Okay first a bit on my problem. I have a list of employees that comes in a semicolon seperated list in one field (not my choice and it has to be kept this way), and I have a list of all the employees on another table all nicely seperated out by their name and departments. I want to get a count of every time their name shows up in the list however I can't figure out how to get a count if they are part of a group that worked on something.

My code here gives me a great count if they worked a ticket by themselves, but how do I get a count if they worked with someone else too?

Code:

Me.A.Value = DCount("[ticket]", "tbl_ticket", "[employeelist] Like Combo37.Value")

[Solved] Query Help Needed

mak1176 — Tue, 21 May 2013 21:00:10 GMT

I'm trying to create a query that will pull information from multiple tables and another query. I am trying to report out every PO that is listed in tblRawMaterialReceiving. The issue is that the query will only report out those PO Numbers that are found in the qryFreightPerPound and not every PO Number from the Table. I basically want to show that if the PO Number does not have any frieght, it would report out as 0.00. And if it does have freight, it will report that cost.

Query is below.
qryPic.jpg

Attached Images

qryPic.jpg (80.9 KB)

UPDATE Query problem

Stan Denman — Tue, 21 May 2013 20:18:39 GMT

I am trying to use the UPDATE query to go from a many-to-many arrangement to a one-to-many arrangement. My current arrangement relates client to addresses via a relational table. So my contact table has autonumber ContID as key, and my address table has autonumber AddrID as key. the relational table has ContID and AddrID as primary keys. I am having a very difficult time deals with the many-to-many for a data entry, and after some though realize I don't need that arrangement. A client can only have one home mailing address but more than one client could theoretically live in the same house.

So I created a field in the contact table for an Address ID. But I have having trouble setting up the update query such that it puts the AddrID from the relational table in the new contacts table AddrID field where the AddrID and ContactID match in the relational table. Thanks.

Algorithm for program maintenance

Ricardobalbio — Tue, 21 May 2013 18:27:55 GMT

Hello,

I need to develop a query where I put the hour meter machine and know when it's time to perform maintenance. The problem is that if I simply subtract one from the previous hour meter, I did not know if it is necessary to maintain longer periods.

Example: With 210 hours of work I have to do the maintenance of 50, 100 and 200 hours. But with 260 I have to do with only the 50 hours maintenance. And with 310 I have to do the maintenance of 50,100 and 300.

Can anyone help me?Hello,

I need to develop a query where I put the hour meter machine and know when it's time to perform maintenance. The problem is that if I simply subtract one from the previous hour meter, I did not know if it is necessary to maintain longer periods.

Example: With 210 hours of work I have to do the maintenance of 50, 100 and 200 hours. But with 260 I have to do with only the 50 hours maintenance. And with 310 I have to do the maintenance of 50,100 and 300.

Can anyone help me?

Setup Update Query to Import Excel file Weekly

flebber — Tue, 21 May 2013 11:30:29 GMT

Hi

I am trying to understand how to setup an import for Access that will allow me to import an excel file into Access weekly and update new and existing records.
Struggling to get off the ground, I created a test database as my real import will update many tables from one excel sheet.

Whenever I complete an update query and press run I get '0 rows will be updated'. In SQL form this is what I have done.

Code:

UPDATE Groceries, Person, Sheet1 SET Sheet1.FirstName = [Person].[FirstName], Sheet1.LastName = [Person].[LastName], Sheet1.Perishables = [Groceries].[Persihables], Sheet1.Fruit = [Groceries].[Fruit], Sheet1.Vegetables = [Groceries].[Vegetables], Sheet1.Meat = [Groceries].[Meat], Sheet1.BreakFast = [Groceries].[Breakfast];

So here I have created 2 tables Person and Groceries. I imported the excel file into sheet1, which contains one field for every field in the database. I ran update and still received a 0 to update error.

What am I missing here?