Here is my current code using the suggestions received:
Code:
SELECT CLng([jobnum]) AS jobnumn, salesregister.cusnum, salesregister.cusname, salesregister.invnum, salesregister.invdate, salesregister.grossamt, salesregister.taxableamt,
BurtonBach
Last Activity: Oct 21 '09, 09:44 PM
Joined: Oct 26 '06
Location:
-
I have a new wrinkle on this. How do I deal with Null values? There are about 10% null values in the jobnum field that we are converting using the CLng and those fields are coming up #Error.
Here is my current code using the suggestions received:
... -
I appreciate your detailed response. It is very helpful!!Leave a comment:
-
I believe I may have figured out my own answer. Does anyone have a better way to do this?
Code:SELECT Format([jobnum],"General Number") AS jobnumn, [jcdetail].[phasenum], [jcdetail].[billqty] INTO jcdetail IN 'C:\Documents and Settings\jbach\Desktop\Jworking.mdb' FROM jcdetail WHERE (((Format([jobnum],"GeneralNumber"))<>"T2006"));
Leave a comment:
-
Is it possible to maketable and change field?
I have a very simple make table query and creates a table in a separate database. Is it possible to also change the type of field of the first field at the same time?
Specifically, the original table has this field as 'text'. While I would like the new table in the separate database to have this field as a 'number' (single). All the data except one line fits this type and I was going to exclude that line in my where statement as... -
That worked. Thank you!Leave a comment:
-
Default Date
This question is in regards to a text box on a form in Access. Is there a way to make the default value setting default to the first day of the current month? -
The urgency around this has passed. I pushed the dataset to Excel and sorted it by hand and was able to take care of the need. However, I am still interested for my learning and I appreciate your efforts in your responses.
In looking at the results that come from your suggestion above it only returns data where all four columns have data and this eliminates returns that were needed.
The only rows I want to exclude...Leave a comment:
-
Here my latest attempt:
...Code:SELECT customer.cusnum, qrycustnames2004.name, qrycustnames2005.name, qrycustnames2006.name, qrycustnames2007.name FROM (((customer LEFT JOIN qrycustnames2004 ON customer.cusnum = qrycustnames2004.cusnum) LEFT JOIN qrycustnames2005 ON customer.cusnum = qrycustnames2005.cusnum) LEFT JOIN qrycustnames2006 ON customer.cusnum = qrycustnames2006.cusnum) LEFT JOIN qrycustnames2007 ON customer.cusnum = qryc
Leave a comment:
-
My understanding is that Full Outer Joins don't work in Access. When I try it I get an error....Leave a comment:
-
How to Left Join (or outer join) a number of queries?
I have the following query already working for me in Access:
I would like to tie in two more queries that pull the results for 05 and 04 in the same tables. In the end I would like to see all data in each column regardless of whether...Code:SELECT qrycustnames2006.cusnum, qrycustnames2006.name, qrycustnames2007.name FROM qrycustnames2006 LEFT JOIN qrycustnames2007 ON qrycustnames2006.cusnum = qrycustnames2007.cusnum;
-
Handling Null Values in Access Query
I previously wrote the following query which gives me that data I wanted and currently returns 436 records:
...Code:SELECT qryTonnageSelect.Jobs_Job_ID, qryTonnageSelect.SumOfTransactions_TonsNetWeight, qryProfitability.Margin, jcjob.name, qryProfitability.SumOfcost, qryProfitability.SumOfSumOfcost, qryProfitability.DollarMargin FROM (qryTonnageSelect INNER JOIN qryProfitability ON qryTonnageSelect.Jobs_Job_ID = qryProfitability.jobnum) INNER
-
I am just trying to work with the situation I find myself in. I don't know why they built the system the way that they did.
The Badge number is a function that was originally designed to be used as a clock in clock out feature for time and attendance, I am trying to see if we could use it for the job number instead so I am haveing the drivers key in the job numbers using that function on each round. It still shows up as 'Badge' on...Leave a comment:
-
I cannot change the structure of the table, I am getting this through an ODBC into the back end of a proprietary softare. The table shows up in my Access database as a linked table.
The reason that there are two Arrive Plant records is because the truck actually arrived at the plant on the first record. The second record is created by the automatic check that usually occurs every 15 min and it keep the status that was there before....Leave a comment:
-
Thank you very much to both of you for attempting to help me.
I am sorry for the delay in posting a reply. The last couple days here at the office have been rough.
I attempted to describe things thoroughly above but maybe I didn't do well so I am going to try again.
Here is a text past of the relevent columns in my table (this is just a small clip from one trucks returns for part of a day where the field...Leave a comment:
-
Thank you for joining in on my question. I am definately pushing the edge of my query abilities here and learning a lot.
I attempted your answer and I'm getting the following:
"Syntax error in JOIN operation."
In addition to thoughts on why that is happening I would like to aske the following:
I started this whole project attempting to benchmark off of this query developed by...Leave a comment:
-
I forgott to answer the question about the error. It only says 'Syntax Error in From Clause'Leave a comment:
-
Thanks Mike,
If anyone else can throw in some thoughts before Mike's return it would be appreciated.
The relevant fields are: alias (Truck # (which shows up actuall as 'Truck 3' or 'Truck 1)), time (hh:mm:ss), date (mm:dd:yy), status (Arrive Job, Leave Job, etc) and address. These are all on the same table named 'x_logs'.
Trips do not generally start one day and end the next.
The way we...Leave a comment:
-
Thank you very much for the reply.
I am getting a Syntax Error in the From Clause.
Please let me know what additional information is necessary. I will attempt to add some information, but any questions that help me know what you need to know would be appreciated.
The table comes from a automated data collection system that I have linked into using ODBC. I believe its foxpro based. Is there a way to...Leave a comment:
-
Syntax Error in Select Query
I am working to create some reports that find and calculate round times and costs related to trucking. The data is gathered from a tracking system and I cannot control the structure of the table.
I am attempting to benchmark off of some queries that were written by a college that I no longer have access to for a similar application but I am running in to some syntax errors and do not know why.
Here is my query so far:... -
I just discovered the Find Duplicate Records Query templates! I should have known. Thanks anyway.Leave a comment:
No activity results to display
Leave a comment: