'Make Table' Query and Multi-value fields?

[dswift]

Is it possible to incorporate multivalue fields in a 'make table' query?

Essentially, I have a list of clients that are processed in various months. The months they are processed to not always follow a pattern. So, I stored the months they are processed are in a single multivalue cell in Table A. I want to use a 'make table query' to generate a new table displaying only clients processed in a particular month. For example, I would like a new table generated to display only those clients processed in June.

When I set this up I get an error message "Multi-valued fields are not allowed in SELECT INTO statements". Is there any way to get around this? I am also open to suggestions for a better way to set up my original table.

 

[Joe4]

When I set this up I get an error message "Multi-valued fields are not allowed in SELECT INTO statements". Is there any way to get around this?

Yes, don't ever use Multi-Valued fields! They are a horrible idea and should be avoided, IMO! They are not compatible with any other database program (like SQL), violates rule of normalization, and cause all sorts of issues (as you are experiencing).

Thing about redesigning your table instead so that you do not have multi-valued fields.

 

[dswift]

hmm, the reason I am using multivalue fields is because the data would be very cumbersome otherwise. For example, client A might receive Service Type 1 in March, June, Sept, Dec; Service Type 2 in Dec only; and Service Type 3 in months March and Sept. Without using multivalue fields, I think I would need 12 columns (one for each month) for each service offering. Assuming 6 possible service offerings that's 72 columns (6 * 12 mo) before I've stored any other client data!

Lastly I would still want the query to Display all clients & services we provide for the month selected.

I think this business model cannot be that unique, so I'm sure someone else has thought of a better way to store and query this data - any ideas?

 

[Joe4]

Without using multivalue fields, I think I would need 12 columns

No, that would also be a data structure that is not normalized!

What you typically do is have a Month field in this table. So, then instead of having one record with three values in a multi-valued field, or three columns, you should have three different records.
It is a little different way of thinking, but it is typically how it is done. It usually makes things much easier in the end.

For example, let's say that you wanted to return all records for one particular month. If you had 12 different columns (one for each month), you would need to search 12 fields! That is a real pain.
Whereas, if you have just one single value month field, you only need to search that one field.

If you have a situation where you need to return the values across fields instead of down records, you would use a Cross-Tab query to get that.

 

[dswift]

Hmm okay.....Right now my main table is a client list. Each client has a numerical unique ID. If I did it the way you suggest, I would need a new separate table to join clients and services, where there would be a separate row for each service and each month. If service A is provided 6 times throughout the year and Service B provided twice, there would be 8 rows for the client in the new table. Is that right? It would mean a ton more records than I have currently, but it would solve a lot of the other issues I am having for sure.

 

[Joe4]

Yes, you would have separate tables for clients and services.
And you would have 8 records, like you said.
Don't worry about all the extra records. It may seem odd, but that is the best practice.

 

[dswift]

Thanks, this has been stumping me for awhile and now I feel like I have a plan to move forward!

 

[Joe4]

Great! Glad I was able to help!

The other new feature to avoid is the "calculated fields in tables". Keep all the calculations in your queries. Once again, that feature is not compatible with other database programs and does not follow rules or normalization.

 

[dswift]

Ahh..okay. So the new 'make table' query I was going to make was going to include a "date received" column and a "date due" column. The "date due" column was going to calculate based on date received + a window of time which would pull from the client data table. Is that a bad idea? The point of the original "make table" query was to at the beginning of the mont to generate a "to do" list for that month were we could track due dates and our progress (entering dates in columns as we finish pieces of the product).

 

[Micron]

PMFJI
You probably saw my answer to your related thread on monthly reports so I won't repeat the stuff about calculations. As for your original question, if you need to extract the existing multi value records so that you can fix your design (as per the links on normalization provided in the other thread) you can try this:
create a query that returns the fields you will need. In the field(s) that contain the MV's add the word .Value to the end of the field name, as in MyField.Value
IIRC, that will break out the values into multiple rows. You'd then use that query as a source to fix your table data. If the query doesn't do what I expect, let us know. I may not remember the fix correctly since I've never used MVF's.