Selecting a record with a query 1

[Gerbers404]

Hi there. I am wondering how to select one record with a query. I have a table that has various fields for our projects. It has both project numbers and invoice numbers. One project number can be tied to several invoice numbers, because we do progress billing. I want to build a select query that selects by project number and then selects the higest invioce number. I have the first part working fine, but I can't seem to figure out how to select the proper invoice number. I have tried using the Max() function, but it isn't working. Any ideas?

 

[nickjar2]

if the invoice number is numeric have you tried:

select max(invoice) as maxInv from table where project_id = something

Nick

 

[nickjar2]

or maybe

select max(invoice) as maxInv from table where project_id = something
group by project_Id

Nick

 

[Gerbers404]

are you saying to put that in SQL, or can I enter in Criteria field? The invoice number is numeric, but it is a text field in the table.

Gerbers404

 

[nickjar2]

if it is a text field then try:

select max(Val(invoice)) as maxInv from table where project_id = something
group by project_Id

put this into the SQL window

if u want to see al project ids then maybe

select max(invoice) as maxInv from table group by project_Id


Nick

 

[Gerbers404]

I guess I don't understand what you mean by "as maxInv" When I put put the following into SQL:

SELECT Max(Val(Projlog.Invoice Number)) as maxInv

An error message occured stating that there was a syntax error (missing operator) in that query expression.

 

[tlbroadbent]

Fill in the query designer grid like this.
[tt]
Field Name: project_id MaxInvc: invoice
Table: tablename tablename
Total: Group By Max
Sort:
Show:
Criteria: "projid"
[/tt]
In SQL View the query would look something like the following but with more parentheses/

Select project_id, max(invoice) as maxInvc
From tablename
Where project_id="projid"
Group By project_id;

BTW: The MAX function works on text as well as numeric data. Terry

http://members.home.net/tlbroadbent/sqlarticles.htm

"I shall try to correct errors when shown to be errors, and I shall adopt new views so fast as they shall appear to be true views." - Abraham Lincoln

 

[Gerbers404]

In query design, it still returns more than one document. I looked at it in SQL, and it does pretty much look like you say it should. Here's the SQL:

SELECT Projlog.[Project Number], Projlog.[Booked Date], Projlog.Customer, Projlog.Description, Projlog.[PO Number], Projlog.Revenue, Projlog.[Project Type], Projlog.Remaining, Projlog.Booktotal, Projlog.ProjectCost, Max(Projlog.[Invoice Number]) AS MaxInv
FROM Projlog
WHERE (((Projlog.[Project Number])=[Forms]![FrmEditInvoiceOptions]![Text3]))
GROUP BY Projlog.[Project Number], Projlog.[Booked Date], Projlog.Customer, Projlog.Description, Projlog.[PO Number], Projlog.Revenue, Projlog.[Project Type], Projlog.Remaining, Projlog.Booktotal, Projlog.ProjectCost;

I eventually want to turn this into an append query that takes most of the info from the higest numbered invoice of a given project number and makes a new record with a few fields left null, Invoice Number being one of them.

Gerbers404

 

[01jeffro]

I'm having the same problem of it showing all the records. It is fine when the only thing the query has in it is the field that I select the max from. As soon as I have it show more fields from the same table, it shows all records.

 

[tlbroadbent]

When you group by more than one column, you'll usually get more than one row returned. I need to learn to ask more questions before volunteering answers. The query I suggested will return the highest invoice for each project. Now the question is, "How do I select the record with that invoice?" The answer, which involves Joining your query with the Max Invoice query, follows.

SELECT Projlog.[Project Number], Projlog.[Booked Date], Projlog.Customer, Projlog.Description, Projlog.[PO Number], Projlog.Revenue, Projlog.[Project Type], Projlog.Remaining, Projlog.Booktotal, Projlog.ProjectCost, Projlog.[Invoice Number]

FROM Projlog Inner Join
(Select Projlog.[Project Number], Max(Projlog.[Invoice Number]) as MaxInvc From Projlog.
Group By Projlog.[Project Number]) As m

ON Projlog.[Project Number]=m.[Project Number]
AND Projlog.[Invoice Number]=m.[MaxInvc]

WHERE Projlog.[Project Number]=[Forms]![FrmEditInvoiceOptions]![Text3] Terry

http://members.home.net/tlbroadbent/sqlarticles.htm

"I shall try to correct errors when shown to be errors, and I shall adopt new views so fast as they shall appear to be true views." - Abraham Lincoln

 

[Gerbers404]

It says I have a syntax error in my from clause and returns me to the comma of that from clause after I exit the error message.

Gerbers404

 

[tlbroadbent]

Remove the period from "Projlog." in the sub-query. Sorry I didn't catch that before. I'm not as good at checking syntax as SQL is. Terry

http://members.home.net/tlbroadbent/sqlarticles.htm

"I shall try to correct errors when shown to be errors, and I shall adopt new views so fast as they shall appear to be true views." - Abraham Lincoln

 

[Gerbers404]

I had already done that before I replied... It still has same error.

Gerbers404

 

[tlbroadbent]

Please post your entire query and indicate where the error is occurring.

Thanks, Terry

http://members.home.net/tlbroadbent/sqlarticles.htm

"I shall try to correct errors when shown to be errors, and I shall adopt new views so fast as they shall appear to be true views." - Abraham Lincoln

 

[Gerbers404]

SELECT Projlog.[Project Number], Projlog.[Booked Date], Projlog.Customer, Projlog.Description, Projlog.[PO Number], Projlog.Revenue, Projlog.[Project Type], Projlog.Remaining, Projlog.Booktotal, Projlog.ProjectCost, Projlog.[Invoice Number]

FROM Projlog INNER JOIN [Select Projlog.[Project Number], Max(Projlog.[Invoice Number]) as MaxInvc From Projlog

GROUP BY Projlog.[Project Number]. AS m ON (Projlog.[Invoice Number]=m.[MaxInvc]) AND (Projlog.[Project Number]=m.[Project Number])

WHERE Projlog.[Project Number]=[Forms]![FrmEditInvoiceOptions]![Text3];

 

[tlbroadbent]

Looks like there is a period (.) in the place of a bracket (]) in the query.

GROUP BY Projlog.[Project Number]. AS m


Should be

GROUP BY Projlog.[Project Number]] AS m
Terry

http://members.home.net/tlbroadbent/sqlarticles.htm

"I shall try to correct errors when shown to be errors, and I shall adopt new views so fast as they shall appear to be true views." - Abraham Lincoln

 

[Gerbers404]

Alright Terry! That's working now.... Thank you so much for your help... Really appreciate it.

Gerbers404