SOLVED - Query with Date Parameter, Check Box Criteria Count, Sum Numeric Fields, Syntax Errors - Data Not Loaded - unable to

13 replies [Last post]
mcMark
Offline
Last seen: 6 days 11 hours ago
Title: ★★
Joined: 15 May 2016
Posts: 16
SOLVED

Setting up a query for the report listing the Clients Served at a Food Bank.

I have achieved the ability to generate syntax errors, crash base and corrupting a copy of the current revision. This may explain other items not currently listed or noted.

Here is what I have learned:

  • Can not use COUNT in a field that has a null entry in any of the records.
  • If I use a function in a column, I must use a function in all other columns checked visible.
  • A Check Box returns On or Off.
  • It is not advisable to save a query that does not work even after removing the parts that threw errors.

And what I have nut grasped sufficiently:

  • Following SQL commands when compared to GUI
  • Why a 1 or 0 has no effect in the Criteria of a Check Box column.
  • Why a Check Box not checked doesn’t throw an error when Counted
  • Parameter formatting when entering a date.
  • The order of columns do make a difference.

And not query related

  • Why Access2Base says one thing and the LibreOffice Base Handbook says another.(actually sort of understand but still why?)
  • Why sometimes/too often the TAB feature in a particular Form does not know how to count 1, 2, 3…and ignores the List Boxes and Option Buttons.
  • And there is not a way to create any type of relationship other than 1 to Many when connecting keys?
  • Where is the Grid Line option?

So starting at the beginning would be Date format in a Parameter

SELECT “DatesServed”.“DateServed”, :P1
FROM “DatesServed”, “HouseHold”
WHERE “DatesServed”.“HouseHoldID” = “HouseHold”.“HouseholdID”
AND “DatesServed”.“DateServed” = ‘‘ || ( :P1 ) || ‘

Entering 5/17/16 into the Parameter and I get an error:

The data could not be loaded:
Wrong data type…..

Any help with the other stuff would be great but first things first I suppose.

Not Base Related:
Something I learned years ago…May 18th, 1980 to be exact:
Don’t shrug away the sight of an ash cloud.

It is better to have and not need than to need and not have… …until you have too much and can’t find what you need
Ratskinger
Offline
Last seen: 4 days 18 hours ago
Title: ★★★★
Joined: 23 Feb 2016
Posts: 91
SQL parameter
Your statement line by line:

SELECT “DatesServed”.“DateServed”, :P1 ——————These values are both the same

FROM “DatesServed”, “HouseHold” —————————-HouseHold not used in Select – not necessary

WHERE “DatesServed”.“HouseHoldID” = “HouseHold”.“HouseholdID” —————not clear what is to be accomplished?

AND “DatesServed”.“DateServed” = ‘‘ || ( :P1 ) || ‘’ —————————-s/b = :P1

so…..



SELECT “DatesServed”.“DateServed” FROM “DatesServed”  WHERE “DatesServed”.“DateServed” = :P1

You have quite a number of different questions here and some need further information. However here are a few answers ( in no particular order):

A CheckBox is a boolean field – 0/1, ON/OFF, Yes/NO

Date stored in a DB are formatted as YYYY-MM-DD. When comparing directly you must use this format (ie: WHERE MYDATE = ’2016-05-18’). Using a parameter 05/18/2016 is converted for you. An entry 2016-05-18 would also work.

Order of columns typically don’t make any difference.

Access2Base is NOT UNO. It’s a conversion process.

Table Grids lack a lot of accessible options. This is not a single control but multiple controls gathered into one. Grid controls (so far only on dialogs & have to code it) have more options but are not data smart.

Ratskinger
Offline
Last seen: 4 days 18 hours ago
Title: ★★★★
Joined: 23 Feb 2016
Posts: 91
A Few more
You can use COUNT where some are ‘NULL

Forgot CheckBox – TRUE/FALSE

SQL, relationships, Primary Keys, Foreign Keys, and much more can be seen in the HyperSQL guide available on-line or in pdf version. Remember, HSQLDB with base is a shortened version of the full HSQLDB. You can install & use the full version (or many other DB’s) if you choose.

Speaking of documentation, do you have OOME (Open Office Macros Explained)-pretty much a must have. Also have you checked the page on this forum: LO Programming Documentation

Ratskinger
Offline
Last seen: 4 days 18 hours ago
Title: ★★★★
Joined: 23 Feb 2016
Posts: 91
SQL
It has struck me that your SQL statement doesn’t make any sense. Why would you want a list of dates equal to the entered date? That is what your statement poses. I believe you may want a list of dates for a Household. If so then…

SELECT “DatesServed”.“HouseHoldID”, “DatesServed”.“DateServed” FROM “DatesServed”  WHERE “DatesServed”.“HouseHoldID” = :P1

Instead of entering a date in the parameter, enter the HouseHoldID using this statement. It will produce a list of dates served for a particular household.

mcMark
Offline
Last seen: 6 days 11 hours ago
Title: ★★
Joined: 15 May 2016
Posts: 16
DATE PARAMETER FORMAT
Yes, there is much to work out and I would like to get this report functional by Friday morning Food Bank. The Date Parameter entered will be a Food Bank day….need the hierarchy of tables.

4 Tables, shown in their hierarchy when used in current Forms for entering and searching Data.
ClientInfo
Address
HouseHold
DatesServed

Some of the Data may not make sense due to its nature of being generated. (500 records)

After the Clients information is initially recorded, the only new records typically generated will be in the DatesServed Table for every visit. (Eventually a change in a Clients HouseHold Data will generate a new HouseHold(HouseHoldID) record.

Starting simple is a query that accepts a Date Parameter and shows the number of visits.
I can do this with a static Date, but not a Date Parameter. Throws the error.
The Data currently needed for submitting to the EFAP is:

  • Total number of clients served
    Number of returning clients
    Number of individuals in the House hold sorted into 4 age groups: 0 – 2, 3 -17, 18 – 54, 55 and Older.

I believe a query is needed to generate the monthly report for submitting.
A weekly report could be used for the volunteers interested in how their helping the community.

A query in which I enter a day for that days data or a month for the month data.
The date filters out all records not used and the HouseHoldId is used to collect the age group data.

AddressId is included in the DatesServed Table for checking if the residence is sending more than 1 person. (This is a feature for later use but I wanted the data already available)

SELECT “DatesServed”.“DateServed” FROM “DatesServed”, “HouseHold” WHERE “DatesServed”.“HouseHoldID” = “HouseHold”.“HouseholdID” AND “DatesServed”.“ClientID” = “HouseHold”.“ClientID” AND “DatesServed”.“AddressID” = “HouseHold”.“AddressID” AND “DatesServed”.“DateServed” = ‘‘ || ( :P1 ) || ‘

Wrong attached images deleted (by the moderator charlie)

AttachmentSize
TABLES.jpg 1023.4 KB
InputError.jpg 257.28 KB
It is better to have and not need than to need and not have… …until you have too much and can’t find what you need
Ratskinger
Offline
Last seen: 4 days 18 hours ago
Title: ★★★★
Joined: 23 Feb 2016
Posts: 91
Not sure what this is about –
Not sure what this is about – “DatesServed”.“DateServed” = ‘ % ‘ || ( :P1 ) || ‘ % ‘

Again, it should be “DatesServed”.“DateServed” = :P1

AttachmentSize
FoodDB.odb 4.2 KB
eremmel
Offline
Last seen: 5 days 20 hours ago
Title: ★★★★★
Joined: 15 Dec 2011
Posts: 276
normalization incomplete
Based on your data model, I see that your normalization is not complete.

It looks like that a household-record brings a client and an address together. So in your servicing registration it is sufficient to maintain the relation to a household. You can always join together DatesServed —> HouseHold —> (ClientInfo, Addres)

An address should no relate to a client: the household bring them together.

P.S. @Ratsinger:
The benefit of an inner join that does not result in extra fields in the select clause is that it serves as an ‘exist-in-other-table’ condition.

Joining
I like to advice you to specify JOIN expressions explicitly i.s.o. implicitly in the WHERE-clause. This makes reading easier.

So your query skeleton might look like to get all data together (based on proper normalization):


SELECT ...
FROM DatesServed D
     INNER JOIN HouseHold H ON D.HouseHoldID = H HouseHoldID
     INNER JOIN Address A ON H.AddressID = A.AddressID
     INNER JOIN ClientInfo C ON H.ClientInfoID = C.ClientInfoID
WHERE ...

Note that I left out the quotes for readability (I might misspelled names) and that I used aliases D, H, A and C to write less code.

n/a
mcMark
Offline
Last seen: 6 days 11 hours ago
Title: ★★
Joined: 15 May 2016
Posts: 16
Reply...
Finally got a chance to get back. That ‘‘ || (:P1) || ‘’ was copied from my other search query where I use a Formatted Text Box and 2 Option Buttons for entering a Clients Birthdate and Gender. That works fine. I was simply copying the technique which apparently was an issue.

Trying to count the results doesn’t go as hoped. Big smile But HEY! The first piece.

Each of those 05/17/2016 records have a Returning Client Check Box and a HouseHoldID. (and a couple other IDs for later)

First the Returning Client Check Box

Wrong attached image deleted (by the moderator charlie)

AttachmentSize
DateParameter_LookUp.jpg 290.6 KB
It is better to have and not need than to need and not have… …until you have too much and can’t find what you need
mcMark
Offline
Last seen: 6 days 11 hours ago
Title: ★★
Joined: 15 May 2016
Posts: 16
SOLVED...Another Piece Solved
I have given up…on trying to Count a Check Box Control

!{width:80%} http://en.libreofficeforum.org/sites/libreofficeforum.org/files/Returnin...!

Instead I’m Count*ing the HouseHoldID in the DatesServed *Table.

And the INNER JOIN apparently performs better than the GUIs WHERE.
Found a good demonstration on INNER, OUTER, LEFT, RIGHT, OUTER LEFT, UPPER Oups …NO UPPER.
http://www.programmerinterview.com/index.php/database-sql/inner-vs-outer...

I will have to make another for the New Clients “DatesServed”.“ReturningClient” <> 1

Wrong attached image deleted (by the moderator charlie)

I removed the ClientID and AddressID relationships from the query since they’re not used here.

All your replies helped me piece this together and I would probably be still corrupting copies of this. Angry
………………..*Current Count: 7 * Star

I made an error in reading the …Copy, Copy(2)…. and lost all versions back to 8:30 last night.

BUT it was just DATA LOSS not the database. Puzzled Sick

AttachmentSize
ReturningClientCount.jpg 328.81 KB
ReturningHouseHold.jpg 403.97 KB
It is better to have and not need than to need and not have… …until you have too much and can’t find what you need
mcMark
Offline
Last seen: 6 days 11 hours ago
Title: ★★
Joined: 15 May 2016
Posts: 16
GRID LINES
The Grid Lines I was referring to above is the Grid on/in the Forms.

I can not find the menu option for turning them off & on.

And other than why I couldn’t get COUNT to work correctly with/in/on/@ the Check Box Control, I do believe the above list has been resolved.
Your Help, Feed Back, Suggestions and most important, Critiquing, is what makes this Forum… Crown

…but I still can not turn off those grid lines.

It is better to have and not need than to need and not have… …until you have too much and can’t find what you need
Ratskinger
Offline
Last seen: 4 days 18 hours ago
Title: ★★★★
Joined: 23 Feb 2016
Posts: 91
For count something like
For count something like this:

SELECT COUNT("ClientID") FROM "DatesServed" Where "ReturningClient" = 'TRUE'

Display Grid & Snap to Grid are icons on the ‘Form Design’ toolbar in design mode.

mcMark
Offline
Last seen: 6 days 11 hours ago
Title: ★★
Joined: 15 May 2016
Posts: 16
GRID OPTIONS
Ahh!

Well that was terribly simple. For me to think I would find it up in the top menus under View , Grid and Helplines

…after looking there 1, 2, 3, too may times.

Thank You!

It is better to have and not need than to need and not have… …until you have too much and can’t find what you need
mcMark
Offline
Last seen: 6 days 11 hours ago
Title: ★★
Joined: 15 May 2016
Posts: 16
CHECK BOX COUNT
Using your suggestion in SQL… …and it works!

Attempting this using the GUI:
Does not turn out well

_SO how did you insert the gray box around the SQL text? That seems to be the key to displaying it correctly here.
_
Another further “Thank you”

!{width:90%} http://en.libreofficeforum.org/sites/libreofficeforum.org/files/KeepDate...!

On the first line:
SELECT “DatesServed”.“DateServed”,
How can I keep that date visible?

This was one of my original dilemmas when I first started but became un-important when I couldn’t generate the other data properly.

AttachmentSize
KeepDate.jpg 164.94 KB
It is better to have and not need than to need and not have… …until you have too much and can’t find what you need
Ratskinger
Offline
Last seen: 4 days 18 hours ago
Title: ★★★★
Joined: 23 Feb 2016
Posts: 91
Here is the same count
Here is the same count statement with the date.

SELECT COUNT( "ClientID" ), "DateServed" FROM "DatesServed" WHERE "ReturningClient" = 'TRUE' GROUP BY "DateServed"

After entering the above in a Query, select the ‘Switch Design View On/Off’ on the ‘Query Design’ toolbar. This will switch between modes so you can see both ways.

The gray box is from ‘Insert Block of Code’ icon above the comment box – far right. The are also icons for ‘InsertLink’ and ‘InsertImage’ which you may find useful.

Please create a new post for any further questions as this is somewhat off topic.

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.