Yes/No field in Make Table query

[RobTsintas]

Simple question. I have a make-table query that outputs -1 and 0 into fields to represent True/False values.

If you make a table manually, and assign a field format to Yes/No, then when you view the table, you see checkboxes instead of -1 and 0.

I would like the output table of my make-table query to be like this. I can choose a format for the fields in the query, so that it appears as Yes or No in the Datasheet view, but when the table is made, it still appears as -1/0.

I can still use the -1/0 values from the table, but as they're stored as 'Number' they presumably take up more memory.

Any ideas how to force the tables fields to be Yes/No data type?

Thanks

 

[BNPMike]

You can change the property of the field in the Query Designer. It's just a display thing so you can change it back by viewing the new table via another query or by changing the make-table query.

 

[RobTsintas]

Yes, I realised I can change the format in query designer, but that only changes the datasheet view of the query. The field in the resultant table still has Data Type = Number and viewing the table shows -1 and 0 values.

I want the Data Type of the resulting table to be Yes/No so that when I view the table, I see the Yes/No checkboxes.

 

[BNPMike]

Why are you viewing the table? The table is to hold your data. You view and manipulate your data via queries. One of their purposes is to put underlying data in a friendly format.

To put it another way if you base all your forms and reports on your view, you will never ever know what format the data is in the table.

A good practice is to create a query on each table that is just all the fields of the table. This will get you out of the habit of ever directly dealing with tables again.

It is extremely unlikely you would ever have a practical application eg form or report that includes all your table fields in exactly the format they you store the data. If you do, you're probably not normalising your data fully.

 

[RobTsintas]

There are two reasons:

1. I am using this query to replace a table that had previously been made manually, so I wanted to keep the design of the table and its fields the same. I know that it makes no difference in terms of using the data; I just didn't want to cause any confusion for other users.

2. The resulting table has thousands of records and 16 Yes/No fields. If all these fields in all these records contain only 0 or -1, but the Data Type is Number (and Long Integer at that), then surely it's going to take up many times more memory to store the information. I want the fields in the resulting table to default to Yes/No Data Type in order to save space.


I have found that I can manually change the Data Type of the fields afterwards, and could write some VB to automate this, but I was hoping there'd be a simple query setting (or a way of defining the data in the formulae) to ensure the required Data Types of the resulting table.

 

[lespaul]

It appears that you are letting users look directly at the table after you create it and that's why you want the 'Yes/No' check box in the table.

Again, all the Yes/No changes is the display in the table and users should not see the tables, only interfaces that show the data.

The data is still stored as a number with 0 or 1; boolean values are always stored that way.

Leslie

 

[BNPMike]

Don't use a Make-table. Use a fixed table which you empty and then append to. This way the formats are controlled by you. Remember to compact the database regularly - I'm sure you do anyway.

 

[RobTsintas]

Lespaul: This particular database is for data analysis rather than for entering/displaying data with a nice front end. The only people who will use it are other 'experienced' users who need to build their own queries from the data according to whatever information requests we get. The table won't actually be used for entering data, so I don't really care about the checkbox display. I just mentioned that because I wanted to stress that I wanted the table fields to be Yes/No to save space. If, as you say, Long Integers don't actually take up more space than Yes/No (I thought is was 4 bytes vs 2 bytes), then I suppose it doesn't matter on that front either.

BNPMike: That's a good idea. I was using a make-table because the table I'm replacing exists in a different database, and the new table will be copied across to replace the old one. I couldn't just link the table and empty/refill it from the database I'm working in, because a few checks need to be done before the data is replaced. Copying the structure of the table and then filling it seems to be the neatest solution. Thanks.