Raise your hand if you're an SQL or Access God. I need HELP desperately!

[Hawkeye-X]

It's frustrating enough having to wait for the answers!!!1

[progressiveBadger]

1. Not sure if I'm a god...

but I do work with SQL and Access fairly often.

[Nicholas D Wolfwood]

4. I have a question: how do I upload my access database

that has been exported to a text file (comma delimited, " text qualifier) to a SQL database?

[LynneSin]

6. Answer

File>>>>Get External Data>>>>Import

For file of type use the dropdown box to select Text Files (csv should be in there) and click on import

Now you'll get into the Import Wizard. For the csv file I import everyday on my job (which came from a SQL db), I do the following:

I use Delimited Width (then click Next)
Comma is usually the separtor and I always put a check in the First Row Contain Field Names (then click on Next)
You can either create a new table or export into a table you already have (then click on Next)
Now this section is important: If there are any fields in that CSV file that are dates, you need to change the 'data type' from Text to Date/Time. If you don't do that then Access won't treat those fields as date and you can't do a date search. You can use the horizontal scroll bar on the bottom to slide through and click on the fields you want to be date. Everything else you'll probably want to leave as Text
Always let Access add the primary key and then click finish.

Now, if you want to use this new table as a query, just go to the Query tab, selected "Create Query by using wizard" and then select your newly designed table. You'll move all the fields over so use the >> button and hit done. It'll give the query the same name as your table.

Hope that helpes!

[Nicholas D Wolfwood]

8. Thanks Lynne

As usual, you rule. :toast: :yourock:

[Jo March]

9. You beat me to it, LynneSin!

;)

[LynneSin]

12. I do this everyday - I could do it in my sleep

:shrug:

[Jo March]

15. Are you a DBA?

I'm a sys analyst now but used to develop Access and manage SQL dbs.

[SaveElmer]

2. Use them quite a bit

What's the question

[sui generis]

3. bring it on Hawkeye

minor deity - mostly parlor tricks actually, but SQL second language

[Hawkeye-X]

5. Here is my problem.

I'm trying to covert the Excel code below to either
Expression Builder or SQL


Code:

=IF(SUM('CURRENT DMR'!F35)<>0,'CURRENT
DMR'!F35*8.34*'CURRENT DMR'!B35,"") 


Right now, 'CURRENT DMR'!F35 is equal to
[AvgInfluentBOD]![Expr1] and 'CURRENT DMR'!B35 is equal to
[AvgInfluent]![Expr1000]

This doesn't work when I try this code:

30daysBOD:
SELECT
IIf(Sum([AvgInfluentBOD]![Expr1])<>0,[AvgInfluentBOD]![Expr1]*8.34*[AvgInfluent]![Expr1000],"")
AS 30dayBOD;

An error comes out: 

You tried to execute a query that does not include the
specified expression" 'Iif(Not Sum(/)=0,
[AvgInfluentBOD]![Expr1]*8.34*[AvgInfluent]![Expr1000],
"")' as part of an aggregate function.

Some extra helps:

This is from AvgInfluentBOD: 
SELECT Avg([300I (Influent)]![BOD Lab mg/l]) AS Expr1,
Format(Date,"yyyy-mm") AS Expr2 
FROM [300I (Influent)] 
WHERE ((([300I (Influent)].Date) Between [Start Date] And [End
Date])) 
GROUP BY Format(Date,"yyyy-mm"); 

This is from AvgInfluent: 
SELECT Avg([Flow Data Calculations]![Influent Calc]),
Format(Date,"yyyy-mm") 
FROM [Flow Data Calculations] 
WHERE Date Between [Start Date] and [End Date] 
GROUP BY Format(Date,"yyyy-mm"); 

Hope this extra bit of info helps.


So any help would be greatly appreciated!

Hawkeye-X

[sui generis]

7. go into expression builder and change

from "sum" to "expression" using the drop down menu on the grid interface. You may need to edit the expression and delete the "Sum(" and ")" after you change the grouping type to "Expression".

That should fix the problem. :propellerhead:

[Hawkeye-X]

13. Where is the grid interface

I'm using Access 2003, btw

[NashVegas]

14. If statements

IIRC, IF statements don't belong in your SQL call - that is, assuming you're doing something for a web page. You get the stuff you need, and then use your coding language to display the results if they meet the requirements you're looking for.

ie, if the conditions you want are met, set a variable to be displayed. The statements you're using are a little complex for my ass, though, so ...

[kiahzero]

16. You've got a malformed query, I think

I've been working with MySQL recently, but I'm almost positive that the syntax is the same.

For a SELECT query:
SELECT <columns> FROM <table> WHERE <test>

Now, to your specific code:

=IF(SUM('CURRENT DMR'!F35)<>0,'CURRENT
DMR'!F35*8.34*'CURRENT DMR'!B35,"")

Let me make sure I understand what you're trying to do with this code:
If the sum of ! is not 0, set the value of the cell to 8.34*!*!.
Otherwise, set it to an empty string.

What are you trying to do with this code in access? Set a form value? Set a field value?

[Hawkeye-X]

18. It's a field value.

=IF(SUM('CURRENT DMR'!F35)<>0,'CURRENT
DMR'!F35*8.34*'CURRENT DMR'!B35,"")

!F35 refers to a field value that is from Excel.

The current DMR!F35, in this case, is 329 (that's from Excel)

The current DMR!B35, in this case, is 1.480


So, I'm trying to get the result to show up as 4061 (rounded up)

[kiahzero]

19. Not quite what I meant

So, in Access, you're trying to calculate that value in a function?

[Hawkeye-X]

20. I think so.

Hmm.

[kiahzero]

21. So, here's what I think you're going to need to do

I think you should probably create one or two queries to pull up the data you need from the database, then in the function you're doing this in, call those queries, and use that data to generate your value.

Did that help at all?

[Hawkeye-X]

23. That is exactly what the

[AvgInfluentBOD] and [AvgInfluent] are --- they are previous
queries.

Hmm..

[indigo32]

22. Hawkey

are these fields in an Access Database now?

[Hawkeye-X]

24. Yes they are, indigo32

:)

[indigo32]

25. What you've posted

is only a function that would be part of a query.
I can't see what other fields you are trying to select with it... and the error you are getting tells me you've got more data you are selecting.

SELECT
IIf(Sum(!)<>0,!*8.34*!,"")
AS 30dayBOD;

creates no relationship between the two queries first off, and the sum statement in the middle of the function is giving you a problem as well. You might want to sum in the original query AvgInfluent, or I could probably help you write the final query if I had more information.

[LynzM]

10. Can't help you with that one, specifically, but...

Check out this great resource:

www.experts-exchange.com

When I was learning Access/VB/SQL, I got more great help here. Amazing depth of knowledge and people willing to help! Good luck to you, SQL can be a #*$&# pain!

[Hawkeye-X]

11. but it costs me money!

*sob*

[LynzM]

17. Doesn't have to

I think you can ask a limited number of questions per month for no fee. You just can't search the archives, AFAIK. It really depends how much you'd use it... for me, at that time, the $10 was worth it.