April 18, 2008 at 10:01 am
Hello,
I have one table in the database that I query using a simple query:
select * from member
When I run it in SQL, the data comes out fine. When I run it in Access, the right number of rows appear (i.e. per customer) but the sales totals or product types are not consistent with that in SQL. It seems that Access is finding one record and recreating it multiple times (the same number of rows as there are in the SQL query).
I haven't the faintest idea as to why this is.
Thanks,
Ivan
April 18, 2008 at 10:07 am
thought I should add this info:
When I query Acces with Sum Of balance: Sum(Abs([member].balance))
The balance is equal to what it should be, that is, what it is in the SQL database.
April 18, 2008 at 11:09 am
- is your sqlserver "member" object a view or a table ?
If it is a view, can you post the ddl ?
-Are you using a linked table to sqlserver in your access app?
- ABS: Returns the absolute, positive value of the given numeric expression.
So there must be negative numbers in your rows ...
- How come you would sum in access and perform a select * in sqlserver ?
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data and code to get the best help
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 18, 2008 at 11:30 am
Hi,
Member is a table that I have linked from the SQL odbc database.
there are negative number in the rows (this I do not have an issue with).
I used sum in access (in another query) just to see whether I could get the number that I retrieved using SQL. ultimately, I want the select * in sqlserver to give me the same data from the select * in access. It is not.
In SQL, the records are as they should be, whereas in access, the resords are not the same as in SQL.
What happens in the SQL Select * in access is that the appropriate # of rows are generated, but for each customer, the rows are duplicated. In other words, output in SQL would be:
Name Balance type
alan 100 1
alan 150 2
alan 175 3
whereas when I use the select * in access the data comes out as:
Name Balance type
alan 100 1
alan 100 1
alan 100 1
Thank you for your help!!!!
Ivan
April 18, 2008 at 11:40 am
Ivanl - Could you post the SQL from both what you do in SQL Server and in Access? something's missing - but we can't tell without some specifics....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 18, 2008 at 12:02 pm
Hi Matt,
This is what I am using. You will have to excuse any lack of info as I am newer to using both access and sql:
in SQL query analyzer I input the code:
select * from member
in MS access, what I did was:
1) linked the member table to the access database I created
2) created a query in design view where I added the member table
3) went to the SQL view in (in view)
4) changed the code from 'SELECT FROM dbo_member' to 'select * from dbo_member'
5) clicked on query and clicked on run
Then I received the results as I had decribed above
April 18, 2008 at 12:51 pm
well ... maybe the are ...
try to add an "order by 1,2,3 " at the sqlserver side as wel as at the access side.
Maybe then you'll see the data is actualy the same, just ordered diffently.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data and code to get the best help
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 18, 2008 at 12:56 pm
Hello,
The order is the same in both SQL and MS access. The problem is that the data that I get out of the access query is incorrect. Specifically, access uses multiples of one record for each customer instead of giving me each record for each customer (as is the case in the sql query).
thanks again,
Ivan
April 18, 2008 at 1:01 pm
ALZDBA (4/18/2008)
well ... maybe the are ...try to add an "order by 1,2,3 " at the sqlserver side as wel as at the access side.
Maybe then you'll see the data is actualy the same, just ordered diffently.
I'm thinking you mean order by name,balance, type there, don't you? (Access can't use the numeric rank that way) but yes - that would be a good place to start!
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 18, 2008 at 1:10 pm
Hi Matt,
thanks again for the help. I used the order by in access and the records are still different.
in access, the output is (e.g.):
name (field) balance (field) type (field)
james 100 1
james 100 1
james 100 1
The output in access should be:
james 100 1
james 150 3
james 133 2
April 18, 2008 at 1:28 pm
I think I might know what is happening. in this linked table, the primary key for the records are not disctinct in the records for each customer. However, when I try to change the primary key to a unique field, access tells me that I cannot save property changes for linked tables.
could this be the problem, and if it is the case, how to I change the primary key to a unique field?
Thanks,
Ivan
April 18, 2008 at 1:31 pm
yup, that did the trick. The primary key was not set correctly.
Thanks to all for your help.
Have a great weekend.
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply