Creating inventory and RMA management forms/building the general database

[Bobwords]

Hello!

I have, so far, about 2 days worth of experience in Access. I have to say, it's an interesting beast. I'm managing a small inventory and RMA process that I am hoping to slightly simplify by moving to access from Excel. I have a team #1 that creates the RMAs, with team #2 people each day shipping them. I need to make a form to insert the right info into access, a second one to add on to that info, then a third that creates a generic shipping form from the data from the first two.

I'll have 3 types of devices I'm tracking, all stored in the same format. I plan on updating the inventory through excel and importing manually, as we use a bar code scanner and it seems like the fastest way to do this.



The fields I'm using are:
Serial/MAC to identify the individual phones
Shipped - just a yes/no if the item has been shipped yet
Date Shipped - The Date the item was shipped
RMA - The RMA number the phone was shipped on

Next, I have the actual sheet we're using to track the RMAs themselves.



ID - The RMA Number
Ticket - our internal ticket number
Store - the location
9 digit - the phone number
Cluster -
Shipping method - how the device will be shipped
Shipping address -
PoC - who the package needs to be addressed to
MAC - the identifier in common on the device inventory page. Every device will have a unique one of these.
Date - the date the RMA was processed

So, for the part that allows the larger team to make the RMAs, I created a basic form to collect everything up to "PoC", as the remainder is filled out by the team #2 processing the RMAs.





What I need to figure out how to do:

Make a form that shows what RMAs need to be processed that day (I.E. ones that haven't had the MAC address field filled in yet), and what devices need to be processed.

Make a form that creates a shipping label that contains the shipping method, the shipping address, the PoC on site, and the store number (with some text added to the front and back of the store number)

Make a query that shows how many phones have been shipped between date x - date x.

And some things I'm sure I haven't figured out I even need yet. I figure quite a bit of this should be pretty simple, but Database work hasn't ever really been my thing before. I'm just looking to cut down on the amount of time we spend working on the paperwork side of the RMAs (and yelling at people for having the excel spreadsheet locked) Any help would be much appreciated.

[ranman256]

Sounds like you need 1 more form...the MENU, so the user can pick from the list of forms.

For each of these forms, build a query that give you the data you need.
for RMAs need to be processed, the query: qsRma2BProcessed (will only pull records to be processed)
The form will use this qry.

Same for the others, (tho some sound like queries rather than forms...Make a query that shows how many phones have been shipped between date x - date x. )

[June7]

One form can be made to work for entering new records as well as editing existing.

Probably should use a report for generating shipping label for printing. Do you have a special printer for labels?

Keep in mind the more 'user friendly', the more code.

If there will be multiple simultaneous users, should be a split design and each user has their own copy of frontend.

[Bobwords]

For each of these forms, build a query that give you the data you need.
for RMAs need to be processed, the query: qsRma2BProcessed (will only pull records to be processed)
The form will use this qry.

For building this query, how do I make it only show blank fields in the MAC address field? Also, how do I get it to pull the content across the entire row, rather than just the column?

[Bobwords]

There will usually just be one person inputting data at a time. We're just having issues with someone locking their computers and going to lunch with the spreadsheet open, locking the spreadsheet for editing.

Is there a good link for how to build good reports? The few I've watched off google haven't exactly been helpful.

For shipping - we have a separate shipping department here that will be dropping stuff on labels. I'm looking to kick the data out onto an 8.5x11 sheet of paper to bring with the boxes getting shipped. So it doesn't have to be super sexy or anything, but clean enough I don't get yelled at by our shipping department.

[June7]

Filter criteria under the [MAC Address] field: Is Null

What do you mean by 'content across the entire row, rather than just the column'?


Any decent tutorial book will have guidance on building objects (tables, queries, forms, reports).

Advise to avoid spaces and special characters/punctuation (underscore is exception) in naming convention.

[pbaldy]

I don't see this having been mentioned. You have these 2 fields:

Shipped - just a yes/no if the item has been shipped yet
Date Shipped - The Date the item was shipped

The first is redundant, as the presence or lack of a date can be used to indicate the shipping status.

[Bobwords]

Ah ha! Thanks for the Is Null function. That makes a lot of sense.

What I mean is this: the return only shows the single column of empty fields.

[Bobwords]

I don't see this having been mentioned. You have these 2 fields:

Shipped - just a yes/no if the item has been shipped yet
Date Shipped - The Date the item was shipped

The first is redundant, as the presence or lack of a date can be used to indicate the shipping status.

I hadn't thought of that when I was originally making the sheet. I'll fix that while I'm moving that. Thanks for the tip!

[Bobwords]

Ah, forget that part with the single column showing, I just realized how to add them.

[June7]

More about Null: http://allenbrowne.com/casu-12.html

[Bobwords]

Ok, next one for you. When I run the query, I want the data to be highlighted in a different color if it already appears in the database (in the case that someone has submitted the same RMA twice). With Excel you can do conditional formatting to highlight cells, is there any easy way to do that in Access?

[June7]

Conditional Formatting of textboxes on form or report.

However, the criteria you want to base conditional formatting on might be a bit difficult. It requires an aggregate function calculation to count records that have same values.