Memo or long text field join?

[Brianmullen8]

I have a database running where we are logging issues on 10 machines, this could be up to 3 records per day per machine, sometimes the issues are just over the 255 limit of short text so need to be on long text.

When on short text i can group them by date and have all the information displayed per day but i cannot join Memo or long text which will only show one issue at a time. I have read somewhere you can use where in sql to join memo or long text but cant figure it out. Screenshots of table with data and dates and the report only showing 1 recorded issue where there should be more.


SELECT Cell.aDate, Cell.[175-5], Cell.[550-1], Cell.[200-4], Cell.[275-2], Cell.[200-10], Cell.[240-1], Cell.[240-2], Cell.[300-1], Cell.[500-2], Cell.[420-1]
FROM Cell
WHERE (((Cell.aDate) Between Date() And Date()-"7"))
ORDER BY Cell.aDate DESC;




Any help is very much appreciated

[orange]

Can you tell us about the "business"? The subjects involved, the kind of data, the outputs(reports//forms)?

You may have a table/data structure issue.
You can not use a memo/long text as your "join" field, but I found this via google.

Also, your sample SQL does not show 2 tables with a join??

[Minty]

If you perform any grouping and include a memo field it will fail.
Have a read here http://allenbrowne.com/ser-63.html for why and some workarounds.

[Brianmullen8]

Hi Orange,

This is a single table with 10 machines, date, setter, product, cycle and cavities.

Each machine has an individual and form to enter the data into. I have reports for each individual machine that show all the above. I'm am trying to get a daily report on all machines. But as in the second screen shot each entry creates a new record with a new date.

When on short text I used max on each machine number and group by date for the query to display properly.

The link you supplied is the one I saw but not sure how it should be in sql.

Thanks

[orange]

I think within the link it is saying avoid memo fields where possible.
Minty has provide the reference to Allen Browne's article re the shortcomings of Memo fields.

I still think you may have a table design/data structure problem. But readers don't know your business.

[Brianmullen8]

I think within the link it is saying avoid memo fields where possible.
Minty has provide the reference to Allen Browne's article re the shortcomings of Memo fields.

I still think you may have a table design/data structure problem. But readers don't know your business.

Hi the business is injection moulding and the setters are reporting machines issues

Here is a screenshot when set to short text

[Micron]

Your posted pics are a bit confusing. For 10/02/2018 on your form/report pic there is only one field in the table that contains data for that date?? Don't understand what the issue is. Afraid this isn't clear either

But as in the second screen shot each entry creates a new record with a new date.

I think Orange is correct in post 5 - not clear enough as to what the data looks like and what the desired outcome is.

[orange]

The issue that we are suggesting is the design of the database underlying the Form.
It seems you could have

Machine---->MachineIssuesWithDate<-----MachineIssues

Machine is the info about the machine.
MachineIssuesWithDate is the table to record MachineID, Issue/Problem and the Date
<-----MachineIssues is a list of standard issues that could be selected by their ID. The importance of this table is that it could remove free form text, spelling errors, and/or non standard terminology.

Allowing users/operators to use free form text can lead to a number of downstream issues (inability to group issues; spelling, inconsistent reporting..)

[ssanfu]

From Post #1,

The bottom image and the SQL indicate that you do not have a normalized table design.

<snip>
SELECT Cell.aDate, Cell.[175-5], Cell.[550-1], Cell.[200-4], Cell.[275-2], Cell.[200-10], Cell.[240-1], Cell.[240-2], Cell.[300-1], Cell.[500-2], Cell.[420-1]
FROM Cell
WHERE (((Cell.aDate) Between Date() And Date()-"7"))
ORDER BY Cell.aDate DESC;

It looks like "175-5", "550-1", "200-4", "275-2", "200-10", "240-1", "240-2", "300-1", "500-2" and "420-1" are machine numbers, ie they are DATA and shouldn't be field names. If a machine were renamed or 1 or more machines were added, you would have to modify the tables, the queries, the forms, the reports and any VBA code.
With a correct, normalized design, you would change/add a record - no design changes necessary.
Another thing, the Access gnomes (can) get persnickety when object names begin with a number.

Naming Rules:
-------------
Do not begin an object name with a number.
Object names should be letters and numbers.
NO spaces, punctuation or special characters (exception is the underscore) in object names.



Some light reading......

=======================
Normalization Terms and Concepts http://www.utteraccess.com/wiki/Norm...s_and_Concepts
Normalization http://www.utteraccess.com/wiki//Normalization



What is Normalization?
=======================
What Is Normalization, Part I: Why Normalization? http://rogersaccessblog.blogspot.com...on-part-i.html
What Is Normalization, Part II: Break it up. http://rogersaccessblog.blogspot.com...n-part-ii.html
What Is Normalization: Part III: Putting It Back Together http://rogersaccessblog.blogspot.com...-part-iii.html
What is Normalization: Part IV: More Relationships http://rogersaccessblog.blogspot.com...n-part-iv.html
What Is Normalization: Part V: Many-to-Many Relationships http://rogersaccessblog.blogspot.com...on-part-v.html