"Bad" data in column providing incomplete reports

[rhubarb]

I have a table with 20k records. There is a field in this table called "Device." As the system has been used this is the type of data I am seeing in that field:



Car 1
Car 1,2,3,4
Car 1,2,3

There is now a requirement to pull a report based on this field. I filtered by the required fields using a combo box tied to a query. That query pulls all the user-entered data from my form and filters the device results based on those filters. The problem is if I want to see reports for car 1, and I select car 1 from my drop-down, I will not see the results for Car 1,2,3,4 or Car 1,2,3 as those are unique.

I have a possible solution but am looking for any insight from these forums as to others. My idea is to remove the combo box and provide a text box instead for the user to enter a value, let's say "1" and my query will LIKE that value with the results bound by my previous filters. Appreciate any other insight.

Thanks,

[CJ_London]

where fld like "*1*"

is fine for car 1 but will also find car 10 and car 21.

you can also use the instr function



looks like your data is not normalised and perhaps it is time to resolve that.

[rhubarb]

Good point.

I'm totally willing to put in the extra work to normalize this data but I'm struggling on how. This field was meant for a single item not multiple. I have another empty table that also has a DEVICE field that should match the "dirty" DEVICE field from the original table. I was thinking of normalizing that but how can I pull that into my original table without a lookup. I keep reading lookups are ill advised for tables.

My thought was to clean this data up on a separate table and use a lookup from the original table DEVICE field to this cleaned table. The user entering data will, instead of being presented with a text box to enter the device on their own, will need to select a combo checkbox (as more than one device could be relevant) based either on the cleaned data, instead of the lookup, as the lookup will provide ID's instead of actual DEVICEs and that's not helpful for them.

Does this make sense?


Thanks

[CJ_London]

without knowing your table structure or know what 1,2 3...means I can only provide a suggestion

you need two tables - one to contain 'car 1' plus a unique ID (primary key/PK) and the other to contain 1,2,3.. on separate records plus a reference back to the 'car 1' table uniqueiD (family key/FK)

[CJ_London]

I keep reading lookups are ill advised for tables.

in tables - yes. on forms/reports - no

[rhubarb]

The table device was really meant to hold one device but as some tickets in this table affect multiple devices users entered multiple devices (Car 1,2,3). So Car 1,2,3, really is 3 values that have been made to fit in a single field. This is the main problem.

I have another table (tblElevators) which maps a job address to it's many devices. Even with this I'm not seeing how I can make use of this from my tblTickets which has the multiple devices already in the DEVICE field. So say the PK in the tblElevators matches the FK in tblTickets I see can only match 1 device per ticket instead of say more like 3.

[orange]

We need to know more about the application and what exactly you are trying to do.
Can you give us an overview of the application in simple English? Pretend we know nothing of your set up - because we don't.
Once we know what you're dealing with, I'm sure more focused responses will follow.

[rhubarb]

Okay, no problem.

The application was originally designed to be a ticket entry form to store tickets related to jobs. I have a tblTickets which holds all the data entered by end-users and currently has 20k records. For sake of my issue, this table has two fields: a job address and a device field. The device field is the problem area because some end-users entered multiple values in the single field. For example, the Device field has "Car 1,2,3" which means all three cars were worked on for that single ticket number. My issue is that when I pull a report based on Device "Car 1" I do not see the ticket with Device "Car 1,2,3" because it is a unique value and does not match "Car 1". This has lead me to believe I should normalize this DEVICE field and that is where I'm struggling.

tblTickets
Ticket | JOB ADDRESS | DEVICE
1 | 10 Widget Lane | Car 1,2,3
3 | 10 Widget Lane | Car 1
4 | 10 Widget Lane | Car 3
5 | 10 Widget Lane | Car 1,2,3,Freight



I also have a tblElevators which has no data. I just added a Job Address and mapped to it's corresponding Device so it looks something like this:
ID | JOB ADDRESS | DEVICE
4 | 10 Widget Lane | Car 1
5 | 10 Widget Lane | Car 2
6 | 10 Widget Lane | Car 3
7 | 10 Widget Lane | Freight
...

So I'm using the tblElevators to clean up and map the addresses to devices but I'm still stuck with my issue with the tblTickets.

Does this make sense?

[orange]

OK. From your post, which has the Access jargon (forms, tables, fields) which I hoped you would avoid, I see things like:

Tickets
Cars
Jobs
Elevators
Devices

What are these and how do they relate in your "business"?

Storage of data and entry of data are /can be very separate things.
Data is stored in tables.
Users enter data via Forms.
A user may enter a string of comma separated values, but the program/validation routine behind the form
would:
check that a character or string was entered
if more than 1 character, extract values between commas
create new records as necessary using those values and related info

All that to say data entered by users is processed and then rejected with a message or saved based on the rules involved.

[rhubarb]

A ticket is a unique number for a job. A ticket has a single job address. A car is a device and a single job address can have multiple devices.

There is no validation behind the form to stop the user from entering commas.

[orange]

A ticket is associated with 1 Job
A Car is a device
A Job may involve 1 or many Devices

How about Ticket 1 Job can have 1 Ticket or 1 Job may have 1 or Many Tickets?

There is no validation behind the form to stop the user from entering commas.

That isn't my point exactly. If the user enters multiple values, you need code to separate those values. If your tables are structured properly(normalized) then you would see why this code is needed.

Do you have anything in your relationships window? if so, please do a print/screen and capture the imahge , then post it.
Good luck.

[rhubarb]

There really is no job per se in terms of database objects. There is only a ticket. 1 ticket always has 1 job address. The relationship shown shows that 1 customer can have multiple elevators. The Elevator # field is the same as the DEVICE field from the tickets table.

[orange]

You have Customers who have Elevators, and I'm guessing you do service work/repair etc on CustomerElevators.

It sounds (again a guess)
1 may have many
Customer ------>Elevator

-you have a ServiceTicket/work Order for a specific Elevator which is owned/leased/contracted to a Customer.

It seems there may be various options for elevators and that usually means more tables in the database. I see controller type, make, door types, openings, stops.... probably all reference tables in a normalized set up.

From experience I can say that embedded spaces in field and object names can lead to a lot of syntax errors. Similarly, using special characters (# etc) can be problematic and lead to syntax issues. Field names with Access are least trouble when restricted to alphanumerics with no embedded spaces.

[rhubarb]

Yes, 1 customer may have multiple elevators. I can fix the special characters in the field and object names.

Anything I can do about the multiple DEVICES in the single value field? The Tickets table is not shown in the relationships window. Although, I'm sure there are multiple problems with the database currently this one is preventing me from making any progress. I'd like to normalize the data in the DEVICE field but I am not sure how to go about doing it.

So the DEVICE field is a single short text field that holds the type of elevator in the ticket that was worked on. Multiple values in this field were not anticipated so I have users who have entered:

Car 1,2,3
Car 1,2, Freight
Car 1

I know how to clean the field, despite the 20k records already in it, but I'm not sure how the field will be laid out. I could make this field a lookup to the elevators table, elevator # field but the general consensus is against lookups in tables. Maybe I can create a separate table with job address -> device and link it to the tickets DEVICE field but this also sounds like a lookup.

OR be able to pull a report on this field accurately depending on what device I want to pull a ticket on.
My two idea which do not work:

1. Filter the job address and pull possible DEVICE values, results in values that do not capture all tickets. 1 does not equal 1,2,3 or 1,2,3,Freight for example.
2. Make a text box instead, user enters 1 I LIKE results, but as previous poster mentioned I could pull Car 10 or Car 11 instead of all Car 1.

[orange]

Here is a free data model that may be useful to you.

Some interpretation required.

Vehicle === Elevator/Device
Contact=== Could be Owner but depends on who is requesting the Ticket.

I am guessing your Elevators have makes and models.
There may be a suggested Defect or a Found defect and you may want to record that info. In the model you may want to have a reference list of Possible Defects/Issues. This would help with spelling and defect description/names.
Service Bookings === your work Ticket( I think)
Parts and PartOrders === any Part you may have to acquire/use to replace a defective Part.

Order seem outside your scope, but if you have to get parts from some supplier it may be in scope???


Note: We still don't know the details of your business process (business rules).
Good luck.