Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.
Course Of The Month
3 days, 11 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
MS Access Database Problems
Posted on 2012-12-27
Hello Experts,
I've spent 2 days building my first Access Database, learning from trial and error, and by attending the University of Google. I've managed to just about reach all of my goals, but I've stumbled right at the end, and need some assistance.
I've built a currency exchange access database. Basically, a customer will present at or shop asking for a currency exchange, the staff member will take their currency, plug the numbers into my access database, and it will calculate the correct amount of currency to exchange. It will keep a record of all these transactions, and eventually print a receipt for the customer etc. We only have 4 currencies we deal with, and need it to be super simple.
My first major problem is my queries. If I have at least 1 of each transactions type recorded (AUD to USD, USD to AUD, AUD to EUR, EUR to AUD, AUD to CNY, CNY to AUD) for the reporting period, my queries run perfect. But, if I am missing 1 or more of any transaction type, the queries fails to run.
I've worked out that this is because of the null values running through my queries (because there are no records for the query to query).
What I can't work out, is how to make a "0" (zero) run through the query, so the result is a zero. I know a zero will work, because if I generate records with zeros in them, it works perfect.
I've tried using the NZ function, and IIF statements, but I think because of where and how I do my calculations, it won’t work.
I've made the database available so you can work out what I’m talking about. Keep in mind that this is my first database, so there are probably things in there that could be done differently, and I’m open to suggestions.
http://www.phseafarers.org/uploads/5/0/9/4/5094800/currencyexchange_-_copy.zip
Thanks for your help.
I've spent 2 days building my first Access Database, learning from trial and error, and by attending the University of Google. I've managed to just about reach all of my goals, but I've stumbled right at the end, and need some assistance.
I've built a currency exchange access database. Basically, a customer will present at or shop asking for a currency exchange, the staff member will take their currency, plug the numbers into my access database, and it will calculate the correct amount of currency to exchange. It will keep a record of all these transactions, and eventually print a receipt for the customer etc. We only have 4 currencies we deal with, and need it to be super simple.
My first major problem is my queries. If I have at least 1 of each transactions type recorded (AUD to USD, USD to AUD, AUD to EUR, EUR to AUD, AUD to CNY, CNY to AUD) for the reporting period, my queries run perfect. But, if I am missing 1 or more of any transaction type, the queries fails to run.
I've worked out that this is because of the null values running through my queries (because there are no records for the query to query).
What I can't work out, is how to make a "0" (zero) run through the query, so the result is a zero. I know a zero will work, because if I generate records with zeros in them, it works perfect.
I've tried using the NZ function, and IIF statements, but I think because of where and how I do my calculations, it won’t work.
I've made the database available so you can work out what I’m talking about. Keep in mind that this is my first database, so there are probably things in there that could be done differently, and I’m open to suggestions.
http://www.phseafarers.org/uploads/5/0/9/4/5094800/currencyexchange_-_copy.zip
Thanks for your help.
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
6
3
- +2
12 Comments
Its difficult to say without seeing the query - if its a query then you should be able to use the term Is Null to display null values
Active 3 days ago
Thanks for your reply KCTS,
Ideally I am trying to get my queries to still run when there are null values, and to return final values of zero, instead of null. If there happens to be just 1 null in my query, it causes all the other results to null also. The NZ function sounds like the way to go, but it just doesn’t work for me.
I have attached the link to the Database in my original post, as it will probably be easier to understand what I am trying to do by having the whole database.
I attempted using the Is Null function, but as with the other functions, I couldn't get it to work.
If you would be willing to look at my database, I would be very grateful. :D
Note: Some of the "design view" queries I have saved in the database, aren't used. They are there for testing and so I could generate the SQL for the 2 main queries I use, since I haven’t learnt to write SQL yet...
Ideally I am trying to get my queries to still run when there are null values, and to return final values of zero, instead of null. If there happens to be just 1 null in my query, it causes all the other results to null also. The NZ function sounds like the way to go, but it just doesn’t work for me.
I have attached the link to the Database in my original post, as it will probably be easier to understand what I am trying to do by having the whole database.
I attempted using the Is Null function, but as with the other functions, I couldn't get it to work.
If you would be willing to look at my database, I would be very grateful. :D
Note: Some of the "design view" queries I have saved in the database, aren't used. They are there for testing and so I could generate the SQL for the 2 main queries I use, since I haven’t learnt to write SQL yet...
Active 3 days ago
Hi boag2000,
The issue occurs when there is not at least 1 of each type of transaction for the reporting period. There are 6 transaction types, 1 - 6 (or in text they are AUD to USD, USD to AUD, AUD to EUR, EUR to AUD, AUD to CNY, CNY to AUD).
To replicate the fault:
Notice there is no values returned, when there has been transactions. If you generate another transaction of the same type, the values will appear in the In Out report. You need to have at least 1 of each transaction type to get a float report to work.
The issue occurs when there is not at least 1 of each type of transaction for the reporting period. There are 6 transaction types, 1 - 6 (or in text they are AUD to USD, USD to AUD, AUD to EUR, EUR to AUD, AUD to CNY, CNY to AUD).
To replicate the fault:
In the transactions table, remove all transactions for todays date.
Make sure you have set a float for todays date (Manager Menu > Edit Float).
Generate a transaction ( New transaction > select type > input from value > confirm exchange > return to main menu)
Generate an In Out Report or Float Report for todays date (Manager menu > set date > Generate In Out Report / Generate Float Report)
Notice there is no values returned, when there has been transactions. If you generate another transaction of the same type, the values will appear in the In Out report. You need to have at least 1 of each transaction type to get a float report to work.
Can't see any way to achieve the result with that type of syntax.
Do this instead..
SELECT DSUM("[Sum Of CurrencyFrom]","[AUD in Query]") as AUDINN, DSUM("[Sum Of CurrencyTo]", "[AUD out Query]") as AUDOUTT,nz(AUDINN)-nz(AUDO
UNION
....repeat for each type
NOTE that there is no FROM clause in any of the component queries that make up the Union query.
Do this instead..
SELECT DSUM("[Sum Of CurrencyFrom]","[AUD in Query]") as AUDINN, DSUM("[Sum Of CurrencyTo]", "[AUD out Query]") as AUDOUTT,nz(AUDINN)-nz(AUDO
UNION
....repeat for each type
NOTE that there is no FROM clause in any of the component queries that make up the Union query.
Active 3 days ago
Hello peter57r,
Thanks for your response. I built the SQL as you suggested for the in out result query, but the same thing happens. If the record does not exist for a certain exchange type, no result is returned (with zero as the value) in the reports or in the datasheet view.
Because the in out result query refers to the (for example) AUD in query and AUD out query, would it be easier to make the AUD in query and AUD out query return zeros, rather than the in out result query?
All my other queries fail to return a "zero" result when no records exist also.... I understand that the way it is currently set up, it never will, but ultimately I want it to. If there is no currency exchanges made, return $0.00 in the report. Makes sense, doesn't it?
Thanks again for your help so far.
Thanks for your response. I built the SQL as you suggested for the in out result query, but the same thing happens. If the record does not exist for a certain exchange type, no result is returned (with zero as the value) in the reports or in the datasheet view.
Because the in out result query refers to the (for example) AUD in query and AUD out query, would it be easier to make the AUD in query and AUD out query return zeros, rather than the in out result query?
All my other queries fail to return a "zero" result when no records exist also.... I understand that the way it is currently set up, it never will, but ultimately I want it to. If there is no currency exchanges made, return $0.00 in the report. Makes sense, doesn't it?
Thanks again for your help so far.
OK - a slight change- it seems that although Access is happy not to have a table in a single query, it doesn't like 'no table' in a Union query so I have created a table with one record , called tblOne to use in the union query.
So the union query i am using is ...
SELECT DSUM("[Sum Of CurrencyFrom]","[AUD in Query]") as AUDINN, DSUM("[Sum Of CurrencyTo]", "[AUD out Query]") as AUDOUTT,nz(AUDINN)-nz(AUDO
UNION
SELECT DSUM("[Sum Of CurrencyFrom]","[USD in Query]") as USDINN, DSUM("[Sum Of CurrencyTo]", "[USD out Query]") as USDOUTT,nz(USDINN)-nz(USDO
UNION
SELECT DSUM("[Sum Of CurrencyFrom]","[EUR in Query]") as EURINN, DSUM("[Sum Of CurrencyTo]", "[EUR out Query]") as EUROUTT,nz(EURINN)-nz(EURO
UNION SELECT DSUM("[Sum Of CurrencyFrom]","[CNY in Query]") as CNYINN, DSUM("[Sum Of CurrencyTo]", "[CNY out Query]") as CNYOUTT,nz(CNYINN)-nz(CNYO
This works ok with or without data.
See the attached report.
Capture10.PNG
So the union query i am using is ...
SELECT DSUM("[Sum Of CurrencyFrom]","[AUD in Query]") as AUDINN, DSUM("[Sum Of CurrencyTo]", "[AUD out Query]") as AUDOUTT,nz(AUDINN)-nz(AUDO
UNION
SELECT DSUM("[Sum Of CurrencyFrom]","[USD in Query]") as USDINN, DSUM("[Sum Of CurrencyTo]", "[USD out Query]") as USDOUTT,nz(USDINN)-nz(USDO
UNION
SELECT DSUM("[Sum Of CurrencyFrom]","[EUR in Query]") as EURINN, DSUM("[Sum Of CurrencyTo]", "[EUR out Query]") as EUROUTT,nz(EURINN)-nz(EURO
UNION SELECT DSUM("[Sum Of CurrencyFrom]","[CNY in Query]") as CNYINN, DSUM("[Sum Of CurrencyTo]", "[CNY out Query]") as CNYOUTT,nz(CNYINN)-nz(CNYO
This works ok with or without data.
See the attached report.
Capture10.PNG
Active 3 days ago
peter57r,
Looks good! I've made the changes and that particular query and report is working. I appreciate your help so far.
In the morning I will try and make the same kind of changes to the CurrencyTotalQuery.
Any thoughts on the other queries, such as the AUD in Query, AUD out Query? Should I write them in SQL similar as you have done with the In Out Results Query? Although having these 1 record tables floating around probably isn't ideal (nor is the way I have originally designed this database, as it turns out).
Looks good! I've made the changes and that particular query and report is working. I appreciate your help so far.
In the morning I will try and make the same kind of changes to the CurrencyTotalQuery.
Any thoughts on the other queries, such as the AUD in Query, AUD out Query? Should I write them in SQL similar as you have done with the In Out Results Query? Although having these 1 record tables floating around probably isn't ideal (nor is the way I have originally designed this database, as it turns out).
You only need one 1-record table.
Some dbms's have something built-in which can be used where a single record is required - for example Oracle has a virtual 1-row table called Dual.
MS SQL Server doesn't require a table to be used in queries, as far as I know.
I didn't realise that Access sometimes requires it until dealing with this Q, although I have used the 1-row table technique a number of times in the past just because I felt safer doing so.
In terms of changing other queries/reports - unless you have the same problem then I don't see why you should change them.
Some dbms's have something built-in which can be used where a single record is required - for example Oracle has a virtual 1-row table called Dual.
MS SQL Server doesn't require a table to be used in queries, as far as I know.
I didn't realise that Access sometimes requires it until dealing with this Q, although I have used the 1-row table technique a number of times in the past just because I felt safer doing so.
In terms of changing other queries/reports - unless you have the same problem then I don't see why you should change them.
Active today
re: 1 record tables
I generally include a table (tbl_Numbers) in every one of my databases. This table contains a single field (lng_Number) and only 10 records (the values 0 - 9). On occassion, I will use this table as my (1 record table) by using a WHERE clause, but the real advantage of this table is that I can create a list of numbers from 0 to infinity (well not really) simply by combining multiple copies of the table in a Cartesian join query (qry_Numbers).
SELECT Tens.lng_Number * 10 + Ones.lng_Number as lng_Number
FROM tbl_Numbers as Tens, tbl_Numbers as Ones
From there, you could use the DateAdd() function to get a list of dates, or you could evaluate a function over a set of values.
SELECT DateAdd("d", qry_Numbers.lng_Number, Date()) as ListOfDates
FROM qry_Numbers
ORDER BY qry_Numbers.lng_Number
This one table comes in handy for all sorts of things.
I generally include a table (tbl_Numbers) in every one of my databases. This table contains a single field (lng_Number) and only 10 records (the values 0 - 9). On occassion, I will use this table as my (1 record table) by using a WHERE clause, but the real advantage of this table is that I can create a list of numbers from 0 to infinity (well not really) simply by combining multiple copies of the table in a Cartesian join query (qry_Numbers).
SELECT Tens.lng_Number * 10 + Ones.lng_Number as lng_Number
FROM tbl_Numbers as Tens, tbl_Numbers as Ones
From there, you could use the DateAdd() function to get a list of dates, or you could evaluate a function over a set of values.
SELECT DateAdd("d", qry_Numbers.lng_Number, Date()) as ListOfDates
FROM qry_Numbers
ORDER BY qry_Numbers.lng_Number
This one table comes in handy for all sorts of things.
Active 3 days ago
Hello again,
I've been able to sort out my null / zero query results. Thanks to peter57r example, I was able to copy and paste my way to fixing my other queries with SQL.
I also (somehow) managed to make my queries work without tblOne. I don't know how, but I haven't been able to break it, yet....
I have some other little bugs to work out, but they will be in another thread. ;)
Thanks for everyone’s help!
I've been able to sort out my null / zero query results. Thanks to peter57r example, I was able to copy and paste my way to fixing my other queries with SQL.
I also (somehow) managed to make my queries work without tblOne. I don't know how, but I haven't been able to break it, yet....
I have some other little bugs to work out, but they will be in another thread. ;)
Thanks for everyone’s help!
Featured Post
This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder.
But why not let Windows retr…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents.
Type out your initial …
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month3 days, 11 hours left to enroll
630 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.