Hyphenate values from two fields

[iqJafa]

Hi everyone,

I am a beginner in MS Access and trying to create a database for my sales. What I am trying to do but can't is this:

I want to create a table to record my sales, the first field would be the order number; withing each order, there would be one item or more. So in order to overcome the duplicate issue, I thought of adding an item number in another field, then another field to combine both numbers and that would be the order id and primary key.

Example:
Field 1 (order#) : Record1=1840; Record2=1840; Record3=1840
Field 2 (Item#): Record1= 01; Record2=02; Record3=03
Field 3 (Order ID): Record1=1840-01; Record2=1840-02; Record3=1840-03

Is there anyway I can achieve this?

Thank you, in advance

 

[theDBguy]

Hi. Welcome to AWF!

I think that would be the wrong approach. If you don't have it already, I would suggest using separate tables for each order item. If you're not familiar with the term "Normalization," I would recommend reading about it first before you continue with the design of your database.

 

[Gasman]

You would not create the third field, as you can always create it from the other two.?
Most systems would have an order table, and an order items table.
Have a google for the northwind db by microsoft.

 

[iqJafa]

You would not create the third field, as you can always create it from the other two.?
Most systems would have an order table, and an order items table.
Have a google for the northwind db by microsoft.

Thank you, I looked at this db in my access but it was depressing, I couldn't understand anything.

 

[iqJafa]

Hi. Welcome to AWF!

I think that would be the wrong approach. If you don't have it already, I would suggest using separate tables for each order item. If you're not familiar with the term "Normalization," I would recommend reading about it first before you continue with the design of your database.

Thank you. As I understood from the search, that I need to create a seperate table for each order. But wouldn't that make hundreds of tables which is opposite to simplicty.

 

[theDBguy]

Thank you. As I understood from the search, that I need to create a seperate table for each order. But wouldn't that make hundreds of tables which is opposite to simplicty.

Hi. You may have misunderstood it. You'll need one table for the Orders and another table for the Order Items - just two tables.

 

[Gasman]

Thank you. As I understood from the search, that I need to create a seperate table for each order. But wouldn't that make hundreds of tables which is opposite to simplicty.

Here are the relationships from Northwind 2007

 

[Pat Hartman]

MS samples include so many poor practices, I rarely recommend them. I don't think this diagram is intelligible for someone who doesn't understand relationships. I can't see the relationships and I know what they should be.

@iqJafa,
In the picture above, look at Orders (row 1, column 2) and Order Details (row 2, column 4) and Products (row 3, column 1) and Customers. Those are the four tables you need in ANY order application.

Ignore the fact that most of the tables have a PK named ID which makes it impossible to map the PK/FK without seeing the diagram. Also ignore the poor naming standards. Object names should NEVER contain embedded spaces or special characters.

 

[iqJafa]

MS samples include so many poor practices, I rarely recommend them. I don't think this diagram is intelligible for someone who doesn't understand relationships. I can't see the relationships and I know what they should be.

@iqJafa,
In the picture above, look at Orders (row 1, column 2) and Order Details (row 2, column 4) and Products (row 3, column 1) and Customers. Those are the four tables you need in ANY order application.

Ignore the fact that most of the tables have a PK named ID which makes it impossible to map the PK/FK without seeing the diagram. Also ignore the poor naming standards. Object names should NEVER contain embedded spaces or special characters.

Hi, thank you. I have a very basic knowledge of relationships and so far failed to implement any, they didn't do or map anything. I am going to have to study this sample database more and try to create those four tables.

 

[Gasman]

Think of it this way
A customer is a parent of Orders (Customer has none,1 or many orders)
An Order is a parent of OrderItems (Order has 1 or many orderitems)
An order Item is linked to a ProductItem, as it is the Product you are selling.

Start here https://www.google.com/search?q=acc...69i57j69i64.7979j0j4&sourceid=chrome&ie=UTF-8

One of those is https://www.accesstogo.org.uk/Simple_Sales_Orders.html for a low cost?
Well worthwhile considering your lack of experience? It would take quite a while for you to create your own, and unless you are doing it to extend your knowledge, that is the route I would take?

 

[Pat Hartman]

Great find Gasman. Very reasonable pricing also.

 

[Gasman]

Great find Gasman. Very reasonable pricing also.

I have no idea as to how good it might be, but they offer a free trial, so if nothing else, might give some ideas?

 

[mike60smart]

Gasman I downloaded a copy out of interest and seems very easy to navigate. Cost 95 Pounds

 

[Gasman]

Yes, and if I needed such system, I would try it out, and if it was enough for me, I'd pay that price?

 

[Pat Hartman]

I down loaded a free version and was able to view the tables. They were properly normalized but the column names had prefixes which just gets in the way of Intellisense and DS views and would only annoy me if I wanted to expand the app. I don't have a current need for any of them or I would have bought one just to look at the code.