Help Needed for Tables & Queries

[confusionguy]

I have a table with several fields as stated below:

Table: Orders
Field1: Order Number
Field2: Line Item
Field3: Part Number
Field4: Date
Field5: Status

There are several enquiries I have in my database and hope anyone out there would be able to answer them.

Qns 1:
I want to add in Field6: Aging into the table. The formula for aging is Date(Field)-Date(). Can this Field6 be added into an Access table or do I need to run a query to determine the Aging.

Qns 2:
I want to add in Field7: ID into the table. The formula for ID is given as the concatenation of Field1, Field2 and Field3. This Field7 will act as a primary key as 1 order can have different materials. Can this Field7 be added into an Access table or again, have to run a query to concatenate these fields.

Qns 3:
For Field5: Status, how do I change the data type from Yes/No to Closed/Open? I have a query to determine if the order is closed.

How to I, upon running the closed order query, have the Field5: Status appear as Closed if the order is a result of the closed order query.

Appreciate any help given to my enquiries.

Thanks a lot in advance!

[hermhart]

Qns1 - You are wanting to calculate the age of a person. I would suggest that Field4 contain the person's date of birth. Then Field6 can be Now() minus Field4. In the Control Source property for Field6 enter the following: = Now()-[Date]. Just a suggestion, though. It's best not use Date as a field name. That has code meaning.

Qns2 - Equally you can have Field7's Control Source property be:
= [Order Number]&[Line Number]&[Part Number]. Just make sure you don't have incompatible data types.

Qns3 - I would suggest that you use a check box for this and let your label indicate that Checked means Closed. If you really want the word "Open" and "closed" to be displayed, use a Combobox whose choices are "Open" and "Closed".

I think an Update query would be good for updating status to "Closed". In the query tell the Status field to update to "Closed" and then indicate what Criteria should cause that to happen.

[confusionguy]

Hi Hermhart, thanks for the advice in all my 3 threads. Really appreciate it.

You have mentioned that by running an Update Query, the Status Field will appear (automatically) as closed following a stated criteria right.

My situation is like this. First, I ran a query to find out which of the orders are closed. The query type ran is an Unmatch Query between 2 tables. So with all the results from the Closed Query, I wish to establish a SQL language or some other methods to have the Status Field in my Orders Table updated as Closed.

So when I pull a report to determine the number of orders closed for the day, I can choose those with Status Field Closed and likewise for the new orders.

Btw, you mentioned abt incompatible data types. Do u mean that for concatenation to work, the data types must all be the same?

Thanks once again for your help.

[confusionguy]

Oh yea, where do I find the control source property to configure the fields of my table? I can't seem to find it anywhere in the Design View of a table.

Does Access have a function that allow you to run several queries from a table and export the results from these queries into respective Excel Spreadsheet? I know we can run a single query and export it out but is there a way we can allow a code to run all these queries at once for exporting?

Thanks again for your help.

[hermhart]

1. Your last question first. The control source property is for a control in a form, not a property of a field in a table.

2. Under at least some circumstances you can concatenate fields of unlike datatype. For example, if you have an unbound textbox in a form, you can set its control source property to be the data from two other textboxes concatenated together where one contains a field that is of numerical data type and the other textbox contains a field that is of text datatype. However, you obviously could not update data in a numerical field of a table using data that is of text data type.

3. To export data to Excel, MSAccess has menu driven ways to do that. But I think you're saying you want to automate it. I think the best way would be to use the TransferSpreadsheet action or method in VB code. I have not actually done that, but in the help file it looks pretty straightforward if you're used to doing some coding.

4. Regarding your status being closed, you have two processes to keep clear. The first is how you want to set the Status as closed. Does a user decide that with manual input? Or does some criteria determine it, such as all orders present at the end of the day get set to closed. Secondly, in your report you want to read the closed status in records and act accordingly in your report. Keep these 2 issues clearly separate in your planning.

[confusionguy]

Hermhart, I dun seem to understand the advice.

The control source property. Do u mean I need to create a form first for the field? I always thought that a table must be created first before a query or a form. So if the table field's properties does not allow me to use formula in the fields, how can I create the form.

Unless u r getting at creating a Null Field in the Table first where after designing the required form, the table will show the values I want in the required fields.

Next, the closed orders. As mentioned, the closed orders are determined by running a unmatched query. The results from the query are the closed status. Nope, I do not require manual input to closed the orders.

So from the results of the query (that's the criteria - no match from my Orders Table and my Generated Table), how do I formulate for the Status Field in my Orders Table to be stated as Closed.

Well, to be frank, I'm lousy with Access, least to say coding.

But I really appreciate your great help.

At least I am heading somewhere with my database now. Thanks.

[hermhart]

Yes, I'm more familiar with MSAccess and its accompanying VBA coding. I'm taking it that you are more into SQL. Hopefully, my help has been of some help to you though.